Skip site navigation (1) Skip section navigation (2)

Re: Query plan for NOT IN

From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query plan for NOT IN
Date: 2009-10-05 13:56:05
Message-ID: 2f4958ff0910050656w1028016dib925f38a7a45d96e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Oct 5, 2009 at 2:52 PM, Matthew Wakeling <matthew(at)flymine(dot)org>wrote:

>
> mnw21-modmine-r13features-copy=# select count(*) from project;
>  count
> -------
>    10
> (1 row)
>
> mnw21-modmine-r13features-copy=# select count(*) from intermineobject;
>  count
> ----------
>  26344616
> (1 row)
>
> mnw21-modmine-r13features-copy=# \d intermineobject;
> 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);
>                                     QUERY PLAN
>
> ------------------------------------------------------------------------------------
>  Seq Scan on project  (cost=1476573.93..1476575.05 rows=5 width=183)
>   Filter: (NOT (hashed SubPlan 1))
>   SubPlan 1
>     ->  Seq Scan on intermineobject  (cost=0.00..1410720.74 rows=26341274
> width=4)
> (4 rows)
>
> This query plan seems to me to be a little slow. Surely it could iterate
> through the ten project rows and perform ten index lookups in the big table?
>
>
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;


-- 
GJ

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-10-05 13:59:59
Subject: Re: Query plan for NOT IN
Previous:From: Matthew WakelingDate: 2009-10-05 13:52:13
Subject: Query plan for NOT IN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group