SQL: Когда дело доходит до НЕ IN и НЕ РАВНО, что является более эффективным и почему?

Допустим, у меня есть набор предметов:

  • Элемент1
  • Элемент2
  • Item3
  • Item4
  • Item5

Запрос может быть построен двумя способами. Во-первых:

SELECT * 
FROM TABLE 
WHERE ITEM NOT IN ('item1', 'item2', 'item3', 'item4','item5')

Или это можно записать как:

SELECT * 
FROM TABLE 
WHERE ITEM != 'item1' 
  AND ITEM != 'item2' 
  AND ITEM != 'item3' 
  AND ITEM != 'item4' 
  AND ITEM != 'item5'
  1. Что является более эффективным и почему?
  2. В какой момент один становится более эффективным, чем другой? Другими словами, что если бы было 500 предметов?

Мой вопрос касается конкретно PostgreSQL.

Источник

2 ответа

Решение

В PostgreSQL обычно довольно небольшая разница при разумной длине списка, хотя IN концептуально намного чище. Очень длинный AND ... <> ... списки и очень длинные NOT IN списки работают ужасно, с AND намного хуже чем NOT IN,

В обоих случаях, если они достаточно длинные для того, чтобы вы даже задавали вопрос, вам следует вместо этого провести тест на исключение из объединения или подзапроса над списком значений.

WITH excluded(item) AS (
    VALUES('item1'), ('item2'), ('item3'), ('item4'),('item5')
)
SELECT * 
FROM thetable t
WHERE NOT EXISTS(SELECT 1 FROM excluded e WHERE t.item = e.item);

или же:

WITH excluded(item) AS (
    VALUES('item1'), ('item2'), ('item3'), ('item4'),('item5')
)
SELECT * 
FROM thetable t
LEFT OUTER JOIN excluded e ON (t.item = e.item)
WHERE e.item IS NULL;

(В современных версиях Pg обе программы в любом случае выдают один и тот же план запросов).

Если список значений достаточно длинный (много десятков тысяч элементов), тогда синтаксический анализ запроса может начать иметь значительные затраты. На данный момент вы должны рассмотреть возможность создания TEMPORARY Таблица, COPY добавление данных для исключения, возможно создание индекса для них, затем использование одного из вышеуказанных подходов к временной таблице вместо CTE.

Демо-версия:

CREATE UNLOGGED TABLE exclude_test(id integer primary key);
INSERT INTO exclude_test(id) SELECT generate_series(1,50000);
CREATE TABLE exclude AS SELECT x AS item FROM generate_series(1,40000,4) x;

где exclude список значений, которые нужно пропустить.

Затем я сравниваю следующие подходы на тех же данных со всеми результатами в миллисекундах:

  • NOT IN список: 3424.596
  • AND ... список: 80173.823
  • VALUES основан JOIN исключение: 20,727
  • VALUES исключение подзапроса: 20.495
  • Таблица на основе JOIN, без экс-списка: 25.183
  • На основе таблицы подзапросов, без индекса в экс-списке: 23,985

... делая подход на основе CTE более чем в три тысячи раз быстрее, чем AND список и в 130 раз быстрее, чем NOT IN список.

Код здесь: https://gist.github.com/ringerc/5755247 (защитите глаза, те, кто переходит по этой ссылке).

Для этого размера набора данных добавление индекса в список исключений не имеет значения.

Заметки:

  • IN список, созданный с SELECT 'IN (' || string_agg(item::text, ',' ORDER BY item) || ')' from exclude;
  • AND список, созданный с SELECT string_agg(item::text, ' AND item <> ') from exclude;)
  • Исключение подзапросов и таблиц на основе соединений во многих повторных прогонах было практически одинаковым.
  • Изучение плана показывает, что Pg переводит NOT IN в <> ALL

Итак... вы можете видеть, что между обоими IN а также AND списки против правильного соединения. Что меня удивило, так это то, как быстро это можно сделать с помощью CTE, используя VALUES список был... разбор VALUES list почти не занимал много времени, выполняя тот же или чуть более быстрый, чем табличный подход, в большинстве тестов.

Было бы хорошо, если бы PostgreSQL мог автоматически распознать нелепо длинный IN пункт или цепочка аналогичных AND условия и переключиться на более разумный подход, такой как выполнение хэшированного соединения или неявное превращение его в узел CTE. Прямо сейчас он не знает, как это сделать.

Смотрите также:

Источник

Я не согласен с оригинальным принятым ответом @Jayram.

Не в последнюю очередь, ссылка для SQL Server и противоречит многим другим статьям и ответам. Также в таблице примеров нет индексов.

Обычно для подзапросов SQL-конструкций

  • <> (или же !=) это скалярное сравнение
  • NOT IN реляционный оператор с левым анти-полусоединением

Проще говоря

  • NOT IN становится формой JOIN, которая может использовать индекс (кроме PostgreSQL!)
  • != часто не SARGable и индекс не может быть использован

Это обсуждалось на dba.se: "Использование логики НЕ по отношению к индексам". Для PostgreSQL в этой расширенной статье объясняется внутреннее больше (но, к сожалению, нет списка констант с NOT IN).

В любом случае, для списка констант, я бы использовал NOT IN до <> как правило, потому что это легче читать и из-за того, что объяснил @CraigRinger.

Для подзапроса NOT EXISTS это путь

Источник
Другие вопросы по тегам