"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> (I'm not sure how optimized UNION inside an IN/NOT IN is.)
NOT IN is pretty nonoptimal, period. It'd help a lot to boost work_mem
to the point where the planner figures it can use a hashtable (look for
EXPLAIN to say "hashed subplan" rather than just "subplan"). Of course,
if there's enough stuff in the UNION that that drives you into swapping,
it's gonna be painful anyway.
Using UNION ALL instead of UNION might save a few cycles too.
If you're willing to rewrite the query wholesale, you could try the old
trick of a LEFT JOIN where you discard rows for which there's a match,
ie, the righthand join value isn't NULL.
regards, tom lane