Re: WIP: patch to create explicit support for semi and anti joins

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> On Wed, 2008-08-13 at 23:12 -0400, Tom Lane wrote:
>> We're just trying to provide better performance for certain common SQL
>> idioms.

> Sounds good, but can you explain how this will help?

1. Allowing optimization of EXISTS/NOT EXISTS as general-purpose joins.
Up to now, the best plan you could hope for was the equivalent of a
nestloop with inner indexscan, with the EXISTS subquery on the inside.
While that's not necessarily bad, it could be pretty bad for a large
outer table. Now we'll have the option to consider merge and hash
joins too.

2. Allowing the planner to get better estimates of the result size of
these special join types. In the past we've had to kluge that, and
the results weren't always good. Part of what I'm doing (the unfinished
part ;-)) is to make more information about join context available to
selectivity estimation functions, which is something we've known we
needed for awhile. I can't yet *prove* that I can get better estimates
with the added info, but if not, that just means I need to rethink what
to pass down exactly.

regards, tom lane