Karl Denninger <karl@denninger.net> writes:
> Explain Analyze on the alternative CLAIMS the same query planner time
> (within a few milliseconds) with explain analyze. But if I replace the
> executing code with one that has the alternative ("not exists") syntax
> in it, the system load goes to crap instantly and the execution times
> "in the wild" go bananas.
Could we see the actual explain analyze output, and not some handwaving?
What I would expect 8.4 to do with the NOT EXISTS version is to convert
it to an antijoin --- probably a hash antijoin given that the subtable
is apparently small. That should be a significant win compared to
repeated seqscans as you have now. The only way I could see for it to
be a loss is that that join would probably be performed after the other
subplan tests instead of before. However, the rowcounts for your
original query suggest that all the subplans get executed the same
number of times; so at least on the test values you used here, all
those conditions succeed. Maybe your test values were not
representative of "in the wild" cases, and in the real usage it's
important to make this test before the others.
If that's what it is, you might see what happens when all of the
sub-selects are converted to exists/not exists style, instead of
having a mishmash...
regards, tom lane