From: | Ottavio Campana <ottavio(at)campana(dot)vi(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | "not in" clause too slow? |
Date: | 2007-09-21 10:09:50 |
Message-ID: | 46F3986E.9040306@campana.vi.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2007-09-21 10:16:46 | Re: queston about locking |
Previous Message | Ottavio Campana | 2007-09-21 09:53:54 | Re: queston about locking |