Groups > comp.databases.postgresql > #333

Re: Joining two tables using two columns (instead of one)

Path	csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!news.musoftware.de!wum.musoftware.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From	Robert Klemme <shortcutter@googlemail.com>
Newsgroups	comp.databases.postgresql
Subject	Re: Joining two tables using two columns (instead of one)
Date	Thu, 05 Apr 2012 23:16:27 +0200
Lines	25
Message-ID	<9u6gdcF6d7U1@mid.individual.net> (permalink)
References	<4f7d9c45$0$1745$426a34cc@news.free.fr> <jlkaa5$9ii$1@dont-email.me> <4f7dc1f1$0$21932$426a74cc@news.free.fr>
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding	7bit
X-Trace	individual.net dAN/hQ7PHKy0sw8YTMPfvgVElM07d023sdmo7x6wAFmuX0hbg=
Cancel-Lock	sha1:keDZOBWqV6hGhFgrI2i5R9gVprI=
User-Agent	Mozilla/5.0 (X11; Linux i686; rv:11.0) Gecko/20120310 Thunderbird/11.0
In-Reply-To	<4f7dc1f1$0$21932$426a74cc@news.free.fr>
Xref	csiph.com comp.databases.postgresql:333

Show key headers only | View raw

On 04/05/2012 06:01 PM, Mateusz wrote:

Btw, the subject does not match your problem description well: I would 
consider a join on two columns as a join where there are criteria on two 
columns.  You are doing two joins.

> I see that you replaced my "LEFT OUTER JOIN" by short "JOIN" - I tested that, but it doesn't change (in any noticeable way at least) the behavior of the database.. I still have to
> wait several minutes, to end up with a "out of memory" message.
>
> If I perform such request:
> SELECT subnet.id, probes.value
>     FROM subnet
>     LEFT OUTER JOIN probes ON probes.id=subnet.probe1;
>
> I get the result after less than 1 second. So doing it on 2 columns should (I guess) still be well under 2s... I don't understand why adding a second column is so much different from
> doing the request two times?
>
> best regards,
> Mateusz

Did you look at the execution plan?  What does it look like?

Kind regards

	robert

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-05 15:21 +0200
  Re: Joining two tables using *two* columns (instead of one) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-04-05 16:30 +0200
    Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-05 18:01 +0200
      Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-05 23:16 +0200
        Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-06 01:00 +0000
          Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-06 04:12 +0000
            Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-06 04:44 +0000
              Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-06 14:17 +0200
                Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-08 19:01 +0000
    Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-05 21:20 +0000
      Re: Joining two tables using *two* columns (instead of one) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-04-06 09:30 +0200
        Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-06 23:42 +0000
        Re: Joining two tables using *two* columns (instead of one) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-04-08 20:39 +0200
  Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-05 19:48 +0000
    Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-05 19:50 +0000
    Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-06 09:13 +0200
  Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-05 21:28 +0000
    Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-06 09:00 +0200
      Re: Joining two tables using *two* columns (instead of one) Jasen Betts <jasen@xnet.co.nz> - 2012-04-06 23:38 +0000
  Re: Joining two tables using *two* columns (instead of one) Mateusz <no@spam.please> - 2012-04-06 08:48 +0200
    Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-06 14:33 +0200
      Re: Joining two tables using *two* columns (instead of one) Mladen Gogala <gogala.mladen@gmail.com> - 2012-04-11 03:21 +0000
    Re: Joining two tables using *two* columns (instead of one) Torsten Kirschner <torsten.kirschner@gmail.com> - 2012-04-10 05:58 +0200
      Re: Joining two tables using *two* columns (instead of one) Fredrik Jonson <fredrik@jonson.org> - 2012-04-10 08:53 +0000
        Re: Joining two tables using *two* columns (instead of one) Torsten Kirschner <torsten.kirschner@gmail.com> - 2012-04-11 00:06 +0200
          Re: Joining two tables using *two* columns (instead of one) Robert Klemme <shortcutter@googlemail.com> - 2012-04-12 20:38 +0200

csiph-web