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@flymine.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)