Re: "not in" clause too slow?

Alban Hertroys ha scritto:
> 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?

mytable.id is primary key.

I create copy_mytable with
create table copy_mytable as (select * from mytable);

the planer behavior does not change no matter if I create and index on
copy_mytable.id or not.

>> 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
>>
>
>