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 >> > >
signature.asc
Description: OpenPGP digital signature
