| From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
|---|---|
| To: | "John D(dot) Burger" <john(at)mitre(dot)org> |
| Cc: | PostgreSQL-general general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: How would I write this query... |
| Date: | 2006-05-02 09:15:01 |
| Message-ID: | 20060502091501.GA7820@svana.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Mon, May 01, 2006 at 05:23:41PM -0400, John D. Burger wrote:
> In my experience, queries like the OUTER LEFT JOIN version posted
> earlier are usually much more efficient than NOT IN queries like the
> above. The planner seems to be pretty smart about turning (positive)
> IN queries into joins, but NOT IN queries usually turn into nested
> table scans, in my experience.
That's because they're not equivalent. IN/NOT IN have special semantics
w.r.t. NULLs that make them a bit more difficult to optimise. OUTER
JOINs on the other hand is easier since in a join condition anything =
NULL evaluates to NULL -> FALSE.
I think there's been some discussion about teaching the planner about
columns that cannot be NULL (like primary keys) thus allowing it to
perform this transformation safely. I don't know if anyone has done it
though...
Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Csaba Nagy | 2006-05-02 09:45:10 | Re: Alternative for vacuuming queue-like tables |
| Previous Message | Alban Hertroys | 2006-05-02 09:09:40 | Re: How would I write this query... |