Skip site navigation (1) Skip section navigation (2)

Re: BUG #4949: NOT IN is prohibitive slower than the rewrite for medium to large sets

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Ole Tange <ole(at)tange(dot)dk>
Cc: Tom Molesworth <tom(at)audioboundary(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4949: NOT IN is prohibitive slower than the rewrite for medium to large sets
Date: 2009-07-29 12:28:21
Message-ID: 407d949e0907290528p706ab2edg7ce4e95904428900@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, Jul 29, 2009 at 11:34 AM, Ole Tange<ole(at)tange(dot)dk> wrote:
> EXPLAIN tells me the LEFT JOIN does table scan as well. It seems the
> primary reason why your LEFT JOIN is faster than my 3 line alternative
> is because I do DELETEs, which is slow. So thank you for the LEFT JOIN
> idea.


If you don't want the NULL behaviour then you should also be able to
translate "WHERE foo NOT IN (select f from bar)" into  "WHERE NOT
EXISTS (select 1 from bar where foo=f)"

You should also test 8.4 to see if the performance of all three of
these variants haven't changed. 8.4 understands how to plan these
kinds of joins much better now. It still doesn't know how to prove a
column cannot be NULL though so I don't know that it would actually
help in your particular case.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-07-29 15:16:51
Subject: Re: BUG #4952: commit_delay ignored because CountActiveBackends always returns zero
Previous:From: Robert HaasDate: 2009-07-29 11:54:21
Subject: Re: BUG #4950: Problem in Job scheduling in Advanced postgre sql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group