Re: Optimizing NOT IN plans / verify rewrite
От
Dave Crooke
Тема
Re: Optimizing NOT IN plans / verify rewrite
Дата
Msg-id
AANLkTinfjRkXJoKD4p8UTEBYtF4AP5+vPuGNcxp=-J+U@mail.gmail.com
Ответ на
Re: Optimizing NOT IN plans / verify rewrite (Kevin Grittner)
Список
Дерево обсуждения
Optimizing NOT IN plans / verify rewrite Maciek Sakrejda <msakrejda@truviso.com>
Re: Optimizing NOT IN plans / verify rewrite Andres Freund <andres@anarazel.de>
Re: Optimizing NOT IN plans / verify rewrite Maciek Sakrejda <msakrejda@truviso.com>
Re: Optimizing NOT IN plans / verify rewrite Andres Freund <andres@anarazel.de>
Re: Optimizing NOT IN plans / verify rewrite Maciek Sakrejda <msakrejda@truviso.com>
Re: Optimizing NOT IN plans / verify rewrite Andres Freund <andres@anarazel.de>
Re: Optimizing NOT IN plans / verify rewrite "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Optimizing NOT IN plans / verify rewrite Maciek Sakrejda <msakrejda@truviso.com>
Re: Optimizing NOT IN plans / verify rewrite "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Optimizing NOT IN plans / verify rewrite Dave Crooke <dcrooke@gmail.com>
Re: Optimizing NOT IN plans / verify rewrite "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Re: Optimizing NOT IN plans / verify rewrite "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
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
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.Grittner@wicourts.gov> wrote:
> No NULLs. It looks like it's a good deal slower than the LOJOn 8.4 and later the NOT EXISTS I suggested is a bit faster than
> version, but a good deal faster than the original.
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
В списке pgsql-performance по дате отправления
