Re: Performance of subselects

On Thu, Mar 5, 2009 at 11:25 PM, Christian Schröder <cs(at)deriva(dot)de> wrote:
> Hi list,
> if I want to find all records from a table that don't have a matching record
> in another table there are at least two ways to do it: Using a left outer
> join or using a subselect. I always thought that the planner would create
> identical plans for both approaches, but actually they are quite different
> which leads to a bad performance in one case.
> I tried the following test case:

SNIP

> All tests have been performed on a PostgreSQL 8.2.9 server:

Have you looked at the release notes for 8.2.12? Especially this line:

Fix planner misestimation of selectivity when transitive equality is
applied to an outer-join clause (Tom)

This could result in bad plans for queries like ... from a left join b
on a.a1 = b.b1 where a.a1 = 42 ...