"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?  ("Rodrigo De León" <rdeleonp@gmail.com>)
Re: "not in" clause too slow?  (Alban Hertroys <a.hertroys@magproductions.nl>)
Re: "not in" clause too slow?  (Martijn van Oosterhout <kleptog@svana.org>)
Список 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 по дате отправления:

Предыдущее
От: Ottavio Campana
Дата:
Сообщение: Re: queston about locking
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: queston about locking