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