With Oracle, I've found an anti-union (MINUS in Oracle, EXCEPT in PGSQL) to be often a bit better than an anti-join, which is in turn faster than NOT IN. Depends of course on row distribution and index layouts, and a bunch of other details.
Depending on what you're returning, it can pay to make sure this computation is done with the shortest possible rows, if necessary using a subquery. Cheers Dave On Mon, Aug 2, 2010 at 2:49 PM, Kevin Grittner <kevin.gritt...@wicourts.gov>wrote: > Maciek Sakrejda <msakre...@truviso.com> wrote: > > > No NULLs. It looks like it's a good deal slower than the LOJ > > version, but a good deal faster than the original. > > On 8.4 and later the NOT EXISTS I suggested is a bit faster than > your fast version, since Tom did some very nice work in this area, > implementing semi join and anti join. If you've got much load with > this kind of query, it might be worth upgrading. > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
