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

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