DELETE, которые не имеют соответствия в другой таблице

Ответ 1

Я сравнивал четыре типичных запроса с разными настройками для {work_mem, effective_cache_size, random_page_cost}, эти настройки оказывают наибольшее влияние на выбранный план. Сначала я выполнил "запуск" с настройками по умолчанию, чтобы согреть кеш. Примечание: тестовый набор достаточно мал, чтобы все необходимые страницы присутствовали в кеше.

Тест-тест

SET search_path=tmp;

/************************/
DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE one
        ( id SERIAL NOT NULL PRIMARY KEY
        , payload varchar
        );

CREATE TABLE two
        ( id SERIAL NOT NULL PRIMARY KEY
        , one_id INTEGER REFERENCES one
        , payload varchar
        );

INSERT INTO one (payload) SELECT 'Text_' || gs::text FROM generate_series(1,30000) gs;
INSERT INTO two (payload) SELECT 'Text_' || gs::text FROM generate_series(1,30000) gs;


UPDATE two t
SET one_id = o.id
FROM one o
WHERE o.id = t.id
AND random() < 0.1;

INSERT INTO two (one_id,payload) SELECT one_id,payload FROM two;
INSERT INTO two (one_id,payload) SELECT one_id,payload FROM two;
INSERT INTO two (one_id,payload) SELECT one_id,payload FROM two;

VACUUM ANALYZE one;
VACUUM ANALYZE two;
/***************/

Запросы:

\echo NOT EXISTS()
EXPLAIN ANALYZE
DELETE FROM one o
WHERE NOT EXISTS ( SELECT * FROM two t
        WHERE t.one_id = o.id
        );

\echo NOT IN()
EXPLAIN ANALYZE 
DELETE FROM one o
WHERE o.id NOT IN ( SELECT one_id FROM two t)
        ;

\echo USING (subquery self LEFT JOIN two where NULL)
EXPLAIN ANALYZE
DELETE FROM one o
USING (
        SELECT o2.id
        FROM one o2
        LEFT JOIN two t ON t.one_id = o2.id
        WHERE t.one_id IS NULL
        ) sq
WHERE sq.id = o.id
        ;

\echo USING (subquery self WHERE NOT EXISTS(two)))
EXPLAIN ANALYZE
DELETE FROM one o
USING (
        SELECT o2.id
        FROM one o2
        WHERE NOT EXISTS ( SELECT *
                FROM two t WHERE t.one_id = o2.id
                )
        ) sq
WHERE sq.id = o.id
        ;

Результат (суммированный)

                        NOT EXISTS()    NOT IN()        USING(LEFT JOIN NULL)   USING(NOT EXISTS)
1) rpc=4.0.csz=1M wmm=64        80.358  14389.026       77.620                  72.917
2) rpc=4.0.csz=1M wmm=64000     60.527  69.104          51.851                  51.004
3) rpc=1.5.csz=1M wmm=64        69.804  10758.480       80.402                  77.356
4) rpc=1.5.csz=1M wmm=64000     50.872  69.366          50.763                  53.339
5) rpc=4.0.csz=1G wmm=64        84.117  7625.792        69.790                  69.627
6) rpc=4.0.csz=1G wmm=64000     49.964  67.018          49.968                  49.380
7) rpc=1.5.csz=1G wmm=64        68.567  3650.008        70.283                  69.933
8) rpc=1.5.csz=1G wmm=64000     49.800  67.298          50.116                  50.345

legend: 
rpc := "random_page_cost"
csz := "effective_cache_size"
wmm := "work_mem"

Как вы можете видеть, вариант NOT IN() очень чувствителен к нехватке work_mem. Согласен, установка 64 (КБ) очень низкая, но это "больше или меньше" соответствует большим наборам данных, которые также не будут помещаться в хэш-таблицы.

EXTRA: во время фазы "вживую" запрос NOT EXISTS() подвергся экстремальному конфликту FK-триггера. Это связано с конфликтом с вакуумным деамоном, который по-прежнему активен после настройки таблицы.:

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
NOT EXISTS()
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Delete on one o  (cost=6736.00..7623.94 rows=27962 width=12) (actual time=80.596..80.596 rows=0 loops=1)
   ->  Hash Anti Join  (cost=6736.00..7623.94 rows=27962 width=12) (actual time=49.174..61.327 rows=27050 loops=1)
         Hash Cond: (o.id = t.one_id)
         ->  Seq Scan on one o  (cost=0.00..463.00 rows=30000 width=10) (actual time=0.003..5.156 rows=30000 loops=1)
         ->  Hash  (cost=3736.00..3736.00 rows=240000 width=10) (actual time=49.121..49.121 rows=23600 loops=1)
               Buckets: 32768  Batches: 1  Memory Usage: 1015kB
               ->  Seq Scan on two t  (cost=0.00..3736.00 rows=240000 width=10) (actual time=0.006..33.790 rows=240000 loops=1)
 Trigger for constraint two_one_id_fkey: time=467720.117 calls=27050
 Total runtime: 467824.652 ms
(9 rows)

Ответ 2

Во-первых: в вашем тексте говорится:

Я хотел бы удалить те сиротские записи из item_tbl.

Но ваш код говорит:

DELETE FROM link_tbl lnk ...

Update: При перечитывании Q я считаю, что вы хотите удалить оси сирот в link_tbl. Точка отсчета строк в этом направлении. @Lucas) будет правильным в этом случае. Но я боюсь, NOT EXISTS на самом деле медленнее, чем NOT IN в этом случае.

Чтобы проверить, что я запускал тестовый пример, который удаленно, как ваша настройка. Невозможно сделать это намного больше, или SQLfiddle будет работать с таймаутом.

→ SQLfiddle.

NOT EXISTS будет быстрее для обратного случая. (Я тоже это испытал.) EXISTS лучше подходит для тестирования "многих" - сторонних. И вообще, с EXISTS есть больше, чем с NOT EXISTS - эта форма должна все равно проверять всю таблицу. Намного сложнее доказать, что чего-то не существует, чем доказать, что что-то существует. Эта универсальная истина также применяется к базам данных.

Разделить и победить

Эта операция подходит для разделения. Особенно, если у вас есть параллельные транзакции (но даже без), я бы рассмотрел разделение DELETE на несколько фрагментов, чтобы транзакция могла COMMIT после приличного количества времени.

Что-то вроде:

DELETE FROM link_tbl l
WHERE  l.item_id < 1000000
AND    l.item_id NOT IN (SELECT i.id FROM item_tbl i)

Затем l.item_id BETWEEN 100001 AND 200000 и т.д.

Вы не можете автоматизировать это с помощью функции. Это превратило бы все в транзакцию и помешало бы этой цели. Таким образом, вы должны будете script от любого клиента.
Или вы можете использовать..

dblink

Этот дополнительный модуль позволяет запускать отдельные транзакции в любой базе данных, включая ту, в которой он работает. И это может быть выполнено через постоянное соединение, которое должно удалить большую часть издержек подключения. Инструкции по установке:
Как использовать (установить) dblink в PostgreSQL?

DO выполнит задание (PostgreSQL 9.0 или новее). Выполнение 100 DELETE команд для 50000 item_id за раз:

DO
$$
DECLARE
   _sql text;
BEGIN

PERFORM dblink_connect('port=5432 dbname=mydb');  -- your connection parameters

FOR i IN 0 .. 100
LOOP
   _sql := format('
   DELETE FROM link_tbl l
   WHERE  l.item_id BETWEEN %s AND %s
   AND    l.item_id NOT IN (SELECT i.id FROM item_tbl i)'
   , (50000 * i)::text
   , (50000 * (i+1))::text);

   PERFORM  dblink_exec(_sql);
END LOOP;

PERFORM dblink_disconnect();

END
$$

Если script должно быть прервано: dblink_connect записывает в журнал БД то, что он выполнил, поэтому вы видите, что уже сделано.

Ответ 3

Возможно, это:

DELETE FROM link_tbl lnk
WHERE NOT EXISTS
  ( SELECT 1 FROM item_tbl item WHERE item.id = lnk.item_id );

При работе с большим количеством записей гораздо эффективнее создать временную таблицу, выполнить INSERT INTO SELECT * FROM ..., затем удалить исходную таблицу, переименовать временную таблицу, а затем добавить индексы обратно...