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 по дате отправления:

Предыдущее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Triggers on system tables
Следующее
От: Louis-David Mitterrand
Дата:
Сообщение: an aggregate to return max() - 1 value?