Deleting rows from a table not contained in another table
От | Florian Weimer |
---|---|
Тема | Deleting rows from a table not contained in another table |
Дата | |
Msg-id | 82eik04axm.fsf@mid.bfk.de обсуждение исходный текст |
Список | 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=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
В списке pgsql-sql по дате отправления: