"not in" clause too slow?

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

Responses

Browse pgsql-general by date

  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