Re: Query plan for NOT IN

On Mon, 5 Oct 2009, Grzegorz Jaśkiewicz wrote:
> On Mon, Oct 5, 2009 at 2:52 PM, Matthew Wakeling <matthew(at)flymine(dot)org> wrote:
> Table "public.intermineobject"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  object | text    |
>  id     | integer | not null
>  class  | text    |
> Indexes:
>    "intermineobject_pkey" UNIQUE, btree (id)
>
> mnw21-modmine-r13features-copy=# explain select * from project where id NOT
> IN (SELECT id FROM intermineobject);
>  
> try using join instead of 'not in'..
>
> select p.* from project p left join intermineobject i on i.id=p.id where i.id is null;

Yes, that does work, but only because id is NOT NULL. I thought Postgres
8.4 had had a load of these join types unified to make it less important
how the query is written?

Matthew

--
I'm always interested when [cold callers] try to flog conservatories.
Anyone who can actually attach a conservatory to a fourth floor flat
stands a marginally better than average chance of winning my custom.
(Seen on Usenet)