Re: "not in" clause too slow?

Ottavio Campana wrote:
> 2) how can I speed it up? by using indexes? or by changing the query?

Do you have indices on mytable.id and copy_mytable.id?
Does using NOT EXISTS get you any better results?

> db=# EXPLAIN ANALYZE select * from mytable where id not in (select id
> from copy_mytable);
>                                                       QUERY PLAN
>
>
----------------------------------------------------------------------------------------------------------------------
>  Seq Scan on mytable  (cost=53.21..148.34 rows=925 width=96) (actual
> time=9.813..9.813 rows=0 loops=1)
>    Filter: (NOT (hashed subplan))
>    SubPlan
>      ->  Seq Scan on copy_mytable  (cost=0.00..48.57 rows=1857 width=4)
> (actual time=0.031..3.132 rows=1857 loops=1)
>  Total runtime: 10.291 ms
>


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //