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

Позволять'скажем, у меня есть набор предметов:

Элемент1Элемент2Item3Item4Item5

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

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'
Что является более эффективным и почему?В какой момент один становится более эффективным, чем другой? Другими словами, что если бы было 500 предметов?

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

 a_horse_with_no_name11 июн. 2013 г., 08:16
Маленькая придира: стандартный оператор SQL дляне равняется является хотя все (?) СУБД вроде бы поддерживают нестандартные!= точно также.
 grantwparks02 авг. 2018 г., 22:01
Эффективный может относиться ко времени выполнения и использованию ресурсов. Почти две вещи.
 Andy Lester12 июн. 2013 г., 06:23
Когда ты сказал "более эффективным", ты имеешь ввиду "Быстрее"? "Efficient» может относиться ко многим вещам, кроме скорости выполнения.

Ответы на вопрос(2)

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

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

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

Проще говоря

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

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

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

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

 a_horse_with_no_name11 июн. 2013 г., 16:42
Возможно, вы захотите изменить свое первое предложение сейчас, когда принятый ответ был изменен;)
 Craig Ringer11 июн. 2013 г., 10:10
Полностью согласен;NOT EXISTS или анти-объединение в списке данных - вменяемый способ. PostgreSQL превращаетсяnot exists в любом случае против присоединения.
 gbn11 июн. 2013 г., 10:09
Моя вторая ссылка говорит, что можетт в отличие от других СУБД. Во всяком случае, ябуду использовать НЕ СУЩЕСТВУЕТ
 Craig Ringer11 июн. 2013 г., 10:07
Ни один из них не совсем подходит для PostgreSQL; иногда он может использовать индекс для где статистические данные таблицы подтверждают теорию о том, что исключенное значение встречается в подавляющем большинстве случаев, и AFAIK это можетиспользовать индекс дляNOT IN список, в который он внутренне переводит.id ALL
 gbn11 июн. 2013 г., 10:13
@CraigRinger: обновлено в любом случае для "список констант "
Решение Вопроса

довольно небольшая разница при разумной длине списка, хотя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.596AND ... список:80173.823VALUES основанJOIN исключение:20,727VALUES на основе исключения подзапроса: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 список занял почти совсем не время, выполняя то же самое илинемного быстрее чем Подход таблицы в большинстве тестов.

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

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

этот удобный пост в блоге Магнус Хагандер написал по теме
 Burhan Khalid11 июн. 2013 г., 09:09
Черт ...* Минут * запланирование?!
 Burhan Khalid11 июн. 2013 г., 08:58
Для postgresql нет особых ограничений, но в некоторых базах данных есть ограничение на размерIN оператор может получить, что дает +1 кAND ... ... построить.
 Craig Ringer11 июн. 2013 г., 08:58
@BurhanKhalid Использование цепочекAND ... ... также осложняет жизнь анализатору и планировщику. Были недавние отчеты по списку рассылки о планировании запросовминут для запросов с десятками тысяч таких предложений, сгенерированных некоторыми ужасными ORM.
 Craig Ringer11 июн. 2013 г., 10:00
@BurhanKhalid Планировщик запросов должен объединить огромныеAND списки неравенства вNOT IN список, чтобы получить слегка вменяемый результат, который сам по себе займет время, и замедлить планирование для подавляющего большинства запросов, которые не являются безумными. Это один из тех "дон»не делай этого " вещи ... если тысгенерировать запрос с50000 простыхOR статьи как тот, которого я видел недавно, тыределать неправильно, Минуты планирования времени ужасны, но поэтому общее дело намного медленнее, чтобы справляться с причудливыми угловыми случаями.

Ваш ответ на вопрос