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=1width=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=20305width=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@bfk.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
