Skip site navigation (1) Skip section navigation (2)

Re: Sequential Scans On Complex Query With UNION - see why this fails

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: Steve Tucknott <steve(at)retsol(dot)co(dot)uk>, PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Sequential Scans On Complex Query With UNION - see why this fails
Date: 2006-01-12 03:21:40
Message-ID: 453.1137036100@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Michael Glaesemann <grzm(at)myrealbox(dot)com> writes:
> When I first saw your FROM clause, I didn't even know that moving the  
> ON clause away from the JOIN it was related to would even work. I was  
> surprised to find that it does! I find it quite counterintuitive to  
> separate a join and its condition. As I understand it,

I think what's happening is that the parser implicitly parenthesizes
like this:

	from ((a join b on a.x=b.y) join c on b.y=c.z)

versus this:

	from (a join (b join c on b.y=c.z) on a.x=b.y)

These are equivalent formulations for inner joins, but they are
decidedly not equivalent if one or both joins is OUTER.  I'd tend
to agree with Michael's advice to keep the join condition close
to the pair of tables you think it's joining.  Even better, use
parentheses to make sure the parser reads it the same as you do.

> I believe both join conditions and  
> restrictions are rewritten as they'd appear in the WHERE clause, so  
> you *could* put them all in the WHERE clause.

Again, this is true for inner joins but very much not the case if
any outer joins are involved.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2006-01-12 03:48:27
Subject: Re: Sequential Scans On Complex Query With UNION - see why this fails
Previous:From: Brendan DuddridgeDate: 2006-01-11 23:39:25
Subject: Re: Intel Macs and PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group