From: | Dave Crooke <dcrooke(at)gmail(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | Maciek Sakrejda <msakrejda(at)truviso(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimizing NOT IN plans / verify rewrite |
Date: | 2010-08-02 20:14:56 |
Message-ID: | AANLkTinfjRkXJoKD4p8UTEBYtF4AP5+vPuGNcxp=-J+U@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)Grittner(at)wicourts(dot)gov>wrote:
> Maciek Sakrejda <msakrejda(at)truviso(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2010-08-02 20:21:51 | Re: Optimizing NOT IN plans / verify rewrite |
Previous Message | Maciek Sakrejda | 2010-08-02 20:06:00 | Re: Optimizing NOT IN plans / verify rewrite |