"not in" clause too slow?
| От | Ottavio Campana |
|---|---|
| Тема | "not in" clause too slow? |
| Дата | |
| Msg-id | 46F3986E.9040306@campana.vi.it обсуждение исходный текст |
| Ответы |
Re: "not in" clause too slow?
Re: "not in" clause too slow? Re: "not in" clause too slow? |
| Список | pgsql-general |
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
Вложения
В списке pgsql-general по дате отправления: