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

On Thu, 2008-08-14 at 10:04 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.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.

OK, that sounds good. Are you also working on transforming NOT IN into
different form? Or is that the same thing as (1)?

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support