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(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.

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.com
PostgreSQL Training, Services and Support