Re: NOT IN Optmization

David, Stephan,

> Maybe something of the form
> AND NOT EXISTS (SELECT 1 FROM job__resource
> WHERE job__id = 1027 and r.id = resource__id)
> will work better for the older systems (it sometimes does, it sometimes
> doesn't)

IME, NOT EXISTS works better than NOT IN on all 7.1 --> 7.3 for any subquery
result which is consistently 20 values or more.

HOWEVER, the subquery estimate for EXISTS clauses up to 7.4 is a hack ...
pretty much the planner assumes that there will be 50% correlation, which can
often result in an unnecessary seq scan (mind you, NOT IN is no better for
large lists). So it pays to "overdetermine" your criteria in the main query;
that is, add WHERE clauses to the main query even where they are reduntant
with the subquery to give the planner the right idea.

--
Josh Berkus
Aglio Database Solutions
San Francisco