From: | Florian Weimer <fweimer(at)bfk(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Deleting rows from a table not contained in another table |
Date: | 2010-03-04 10:51:17 |
Message-ID: | 82eik04axm.fsf@mid.bfk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I want to reimplement
DELETE FROM foo;
INSERT INTO foo SELECT * FROM bar;
in a way which does not touch rows which are not modified (mainly to
avoid locking issues). I've come up with this:
DELETE FROM foo WHERE NOT EXISTS
(SELECT * FROM bar WHERE foo.* IS NOT DISTINCT FROM bar.*);
INSERT INTO foo SELECT * FROM bar EXCEPT SELECT * FROM foo;
The problem is that the plan for the DELETE doesn't look pretty at all:
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop Anti Join (cost=313.36..181568.96 rows=1 width=6)
Join Filter: (NOT (foo.* IS DISTINCT FROM bar.*))
-> Seq Scan on foo (cost=0.00..293.05 rows=20305 width=38)
-> Materialize (cost=313.36..516.40 rows=20305 width=32)
-> Seq Scan on bar (cost=0.00..293.05 rows=20305 width=32)
(5 rows)
Is there some way to turn this into a merge join, short of introducing
primary keys and using them to guide the join operation?
--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2010-03-04 20:09:14 | an aggregate to return max() - 1 value? |
Previous Message | Andreas Kretschmer | 2010-03-03 17:50:01 | Re: Triggers on system tables |