From: | "Guy Rouillier" <guyr(at)masergy(dot)com> |
---|---|
To: | "PostgreSQL-general general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How would I write this query... |
Date: | 2006-05-01 22:37:13 |
Message-ID: | D4D1632DC736E74AB95FE78CD6090079012616@mtxexch01.add0.masergy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
John D. Burger wrote:
>> select *
>> from people
>> where id not in
>> (
>> select id
>> from class_registration
>> )
>
> 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.
Interesting, I am aware that each DBMS query optimizer does better with
some expressions, and worse with others. When I was a DB2 DBA, DB2 would
change from release to release the expressions it most preferred.
I imagine the above formulation is what many people would try initially,
until they encounter experiences such as yours. I checked the TO-DO
list and I don't see anything pending to address this. Bruce and/or
Tom, are there any far-off intentions to do anything to improve "not in"
execution? Or perhaps to rewrite it to an equivalent expression that
already works well?
--
Guy Rouillier
From | Date | Subject | |
---|---|---|---|
Next Message | Karen Hill | 2006-05-02 00:23:22 | insert into a view? |
Previous Message | brsaweda | 2006-05-01 22:15:28 | Re: For vim users: Syntax highlighting for PostgreSQL |