Karl DeBisschop <kdebisschop@h00a0cc3b7988.ne.mediaone.net> writes:
> For example, if you look through the archive you will see quite a few
> discussions about slow returns from "SELECT * FROM foo WHERE x in (...)"
> -- this is a fact about the current implementation of PostgreSQL and the
> (only?) solution is to rewrite using "WHERE EXISTS ...." Though I forget
> the exact reason, it turns out that it is not a trivial matter to have the
> optimizer rewrite the first query into the second (and of course the first
> is slow because it cannot use inidces). Your question could be a similar
> case, or it could be a repairable shortcoming in the planner/optimizer.
In fact, IN (subselect), INTERSECT, and EXCEPT are all pretty much the
same thing, and they're all pretty slow in the current code :-(, because
they all work by rescanning the inner query for each outer tuple --- in
other words, they're all implemented like plain nestloop joins. EXISTS
is marginally better because the planner can figure out how to use an
index on the inner table, if there is one.
The right way to fix this is to promote these operations into
full-fledged join types so that the optimizer can consider alternatives
like mergejoin (which is basically the method Dragos is talking about),
hashjoin, index-driven nestloop, etc. That's not a small task. I'm
hoping to see it happen as part of the querytree redesign scheduled for
7.2, which will also give us outer joins. If you think about it, all
of these are variants on the theme of outer join...
regards, tom lane