From: | "John D(dot) Burger" <john(at)mitre(dot)org> |
---|---|
To: | PostgreSQL-general general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How would I write this query... |
Date: | 2006-05-02 14:15:02 |
Message-ID: | a115999fa9479251bed1581c6e3ed478@mitre.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Martijn van Oosterhout wrote:
> 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.
Which is why Hash IN Joins were added, presumably. But there's nothing
analogous for NOT IN, I guess, perhaps there can't be.
> 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...
Yeah, I've noticed cases where I've thought "Ah, the planner doesn't
know that column can't be null". Similarly, it has seemed to me that
knowing that a column was UNIQUE could have made for a better plan,
although I can't think of any examples off-hand. Maybe where I saw it
using a Hash aggregate on a unique column, and I thought it could just
use the index, although that may not make sense either.
- John D. Burger
MITRE
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-05-02 14:53:10 | Re: Using a pointer as statetype for an aggregate |
Previous Message | David Brain | 2006-05-02 13:58:56 | number of page slots needed exceeds max_fsm_pages |