Comments 11
Проверка вхождения через
joinсебя не оправдала.
Попробуйте неопровержимо доказать, что в случаях использования WHERE IN и WHERE EXISTS операция удаления дубликатов значений, возвращаемых подзапросом (DISTINCT в подзапросе), обязана НЕ присутствовать, тогда как в случае JOIN, наоборот, такая процедура обязательно должна выполняться. До тех пор, пока это не будет однозначно и безальтернативно доказано, весь ваш вывод нумер 2 не стОит и выеденного яйца.
А в качестве бонуса можете попробовать доказать, что в случаях WHERE IN и JOIN подзапрос вообще, в принципе, нужен...
Для проверки вхождения нет разницы, какую конструкцию использовать -
existsилиin, потому что в большинстве случаев оптимизатор перепишет их в одинаковый план выполнения.
За кадром я пытался найти условия, при которых
joinбудет работать быстрее других вариантов. Добавлял индексы, менял размеры таблиц и ожидаемую селективность, но каждый раз результат был либо такой же, либо хуже.
Метод Научного Тыка - в полный рост. Условия надо не искать, а планировать, и обеспечивать при этом полное покрытие пространства возможных значений (либо чётко определять область применения).
Ну и результаты - соответствующие.
Операция дистинкта в случае WHERE IN и WHERE EXISTS не "обязана НЕ присутствовать", а "НЕ обязана присутствовать явно", потому что планировщик будет либо использовать полусоединение, которое подразумевает удаление дубликатов, либо самостоятельно вставит его в план при выборе других инструментов фильтрации (того же join, например). Как мы видим из плана выполнения со скрина, без использования явного дистинкта количество вернувшихся строк совпадает с ожиданиями (800 тыс.)
"тогда как в случае JOIN, наоборот, такая процедура обязательно должна выполняться" - разумеется должна, у нас выполняет соединение один ко многим, фильтровать дубликаты необходимо либо в подзапросе, либо на результирующем сете данных. Если этого не сделать, то на каждую запись из таблицы transactions будет своя запись в результирующем сете. Таким образом, вместо ожидаемых 800 тыс. строк мы получим все (или почти все) 100 млн. Это базовый функционал join.
Вы прекрасно доказали, что проверяете вовсе не относительную производительность указанной операции, то есть сравниваете не чистые указанные три альтернативы, а их же, но с дополнительными операциями, причём для каждого варианта своими. Как итог - неадекватные результаты. Ну и незаслуженно, как я считаю, пострадавший JOIN. А WHERE IN так и вовсе выжил исключительно благодаря интеллекту планировщика сервера.
Приведите иллюстрирующий пример. Исходя из понимания процессов его не так сложно написать)
Навскидку - замените COUNT на функцию, нечувствительную к join multiplying, например, MAX. Этого, скорее всего, будет достаточно. Или смените направление связи, и ставьте задачу по поиску/подсчёту на стороне "много".
Мне кажется вы не поняли общее направленность статьи. Я рассмотрел исключительно две типичные ситуации, которые я считаю самыми распространенными. Я не исключаю того, что есть запросы, для которых вышесказанное будет не актуально. Если вы приведете реальный подобный пример для нас всех это будет очень полезный опыт.
Мне кажется вы не поняли общее направленность статьи.
Хм... возможно. Я полагаю, что уж если проводить сравнение, заявляя его как общее, и тем более делать из его результатов какие-то выводы без точного указания условий непосредственно в этих выводах, то начальные условия обязаны накрывать всё поле возможных значений и типов обработки. Агрегация на стороне "один" и на стороне "много", различные соотношения количества соответствующих записей на стороне "много" каждой записи на стороне "один", различный процент записей на стороне "один", не имеющих соответствия на стороне "много", наличие/отсутствие подходящего индекса... даже на вашей модели - просто проверьте, будет ли поведение сервера одним и тем же, если в среднем одной записи на стороне "один" соответствует 2-3 записи на стороне "много", и то же при в среднем 100 соответствующих записях? теоретически планы должны различаться.
А почему не включили в сравнение боле очевидный и подходящий для такой задачи инструмент, как intersect/except?
Все VIP'ы:
select customer_id from customers
intersect
select customer_id from vip_customers;
Все не VIP'ы:
select customer_id from customers
except
select customer_id from vip_customers;
Кто хоть раз что-то купил:
select customer_id from customers
intersect
select customer_id from transactions;
Кто ничего не покупал:
select customer_id from customers
except
select customer_id from transactions;
И ещё - по логике, customer_id в таблице VIP'ов уникален (и вообще, должен быть FK и PK). А раз так, то никакой подзапрос, тем более с distinct'ом, для join'а не нужен.
Согласен с моментом - "customer_id в таблице VIP'ов уникален (и вообще, должен быть FK и PK)". И действительно, тогда дистинкт не нужен в подзапросе для join. Для невхождения в данном примере это не будет играть роли, так как запрос всё равно переписывается в антисоединение. Для вхождения в случае с наличием PK на все запросы опять же таки переписываются в одинаковый Nested Loop и не имеют никакой разницы между друг другом. Так что хоть вы и правы в данном моменте, но по сути разницы это не составляет никакой.
Касательно конструкций intersect/except - действительно важное замечание. Я не рассматривал эти примеры лишь потому, что ни разу в своей практике не видел использования этих конструкций в реальном коде. На самом деле это устаревшие конструкции с заведомо проигрышными планами выполнения. Никто это не использует - и правильно делают. Даже на моих примерах:
Нахождение VIP`ов:
20-40 ms у других вариантов против 400+ ms у intersect
Скрытый текст
Нахождение "Кто хоть раз что-то купил":
20-30k ms у других вариантов против 100k+ у intersect:
Скрытый текст
Данные конструкции просто не выдерживают никакой критики и не рекомендуются к использованию никогда и никому.
Плюс Вам, за сравнение производительности.
А можно пруфы на устаревшие и не рекомендуются к использованию?
@Akina справедливо отметил, что на мелких датасетах вариант имеет право на жизнь.
И на выборках порядка 10^3 разница в скорости будет незаметна, зато, запрос выглядит лаконичнее.
UNION DISTINCT / EXCEPT / INTERSECT всегда требуют выполнения сортировки для последующего удаления дубликатов, потому их имеет смысл применять только на очень компактных наборах данных.
К тому же (не скажу, правда, за самые последние версии) все эти операторы в обязательном порядке материализуют промежуточные наборы данных.
Проверка на (не)вхождение строк через разные инструменты в PostgreSQL