On Aug 13, 2008, at 20:12, Tom Lane wrote:
Wow. That sound awesome, Tom. Stupid question: Do these join types
have some sort of correspondence to the SQL standard?
Semi and anti joins are pretty standard concepts in relational theory,
but they have no direct mapping in the SQL join syntax. You can write
them with certain well-known locutions, though:
IN and EXISTS, with certain restrictions, represent semi join
NOT EXISTS, with certain restrictions, represents anti join
LEFT JOIN with an "incompatible" higher IS NULL test represents
anti join
Basically what this patch is about is teaching the planner that these
constructs are best understood via the relational-theory concepts.
We'd been doing it in a pretty ad-hoc way before, and run into a lot
of problems that we've had to kluge around. I think that this
approach
provides a structure that will actually work well.
Great. Thanks for the explanation, Tom, as always.
Or is this just something that's under the
hood an not actually a change to the syntax of SQL joins?
Right, there's no "user visible" feature or syntax change here. We're
just trying to provide better performance for certain common SQL
idioms.
Good, it makes a lot of sense.
What's not done:
nodeMergejoin.c doesn't yet handle JOIN_ANTI. (This is just a SMOP,
I guess that means you plan to do it once there has been significant
testing with nestloop and hash and when the selectivity stuff is
done?
Actually, I got it done an hour or so ago --- it turned out to be
easier
than I thought. It just didn't seem like part of the critical path
for
the patch, so I'd been willing to let it go till later.
I love it when things work that way. :-)
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
