| 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 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2009-07-29 15:16:51 | Re: BUG #4952: commit_delay ignored because CountActiveBackends always returns zero |
| Previous Message | Robert Haas | 2009-07-29 11:54:21 | Re: BUG #4950: Problem in Job scheduling in Advanced postgre sql |