mytable has 1857 rows, copy_mytable is a copy of mytable and I want to
know which new rows have been entered. I used the where id not in, and
the query works. My problem is that if I run the same command on another
table with 378415 rows, it is terribly slow. I ran explain analyze on
the first table, just to see how is works, and I have two questions:
1) from explain analyze output, does the planner scan mytable and for
each element runs a sec sqn on copy_mytable?
2) how can I speed it up? by using indexes? or by changing the query?
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
