To: pgsql-sql@postgresql.org
Subject: Deleting rows from a table not contained in another table
From: Florian Weimer <fweimer@bfk.de>
Date: Thu, 04 Mar 2010 10:51:17 +0000
Message-ID: <82eik04axm.fsf@mid.bfk.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

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=3D313.36..181568.96 rows=3D1 width=3D6)
   Join Filter: (NOT (foo.* IS DISTINCT FROM bar.*))
   ->  Seq Scan on foo  (cost=3D0.00..293.05 rows=3D20305 width=3D38)
   ->  Materialize  (cost=3D313.36..516.40 rows=3D20305 width=3D32)
         ->  Seq Scan on bar  (cost=3D0.00..293.05 rows=3D20305 width=3D32)
(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?

--=20
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstra=DFe 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99