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(at)magproductions(dot)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 //