Comments 31
Интересно, что бы победило, если бы в конце не надо было делать group by, а посчитать агрегат без ключа группировки?
Т. к. в этом случае, если иметь данные в RAM, то аналог join (flatMap) побеждает практически всегда. Это проверял для котлиновских Sequence, джавовых stream, итераторов в rust, LINQ-запросов в C#: лучше сначала умножить две коллекции через flatMap и строить агрегат, чем агрегировать по субколлекциям, а затем считать общий total. Хотя, казалось бы, данные в субколлекциях лежат близко в памяти, и должны лучше попадать в cache-line, плюс векторизация должна отработать.
Интересно, что бы победило, если бы в конце не надо было делать group by, а посчитать агрегат без ключа группировки?
Какая-то не очень определённая фраза. Ведь в исследуемом запросе в выходном наборе есть как неагрегированное, так и агрегированное поле, и просто выбросить оттуда GROUP BY не получится, а использование оконной функции даст разные выходные наборы.
Не могли бы вы привести точный текст SQL-запроса, о котором говорите?
В терминах ваших запросов будет так, первый вариант:
SELECT COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.region = ?;Второй вариант:
SELECT SUM(t.orders_count) AS orders_count
FROM (
SELECT (
SELECT COUNT(o.order_id) AS cnt
FROM orders o
WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c
WHERE c.region = ?
) AS t;
Это побеждает в основном in vitro, так же, как и exists в предикате. Чуть посложнее основной запрос и/или корррелированный - и нет никакой гарантии, что вся конструкция не свалится в O(N^2).
Вывод - если вы можете решить задачу без подзапроса/outer apply (что собственно практически одно и то же) - делайте надежно через inner/left join с подзапросом. Если не можете (лень, или селективность предиката основного запроса выше, чем селективность подзапроса) - делайте коррелированным - но тогда уж лучше - outer apply. В нем хоть можно получить несколько полей за один раз, да и протянуть из него значение в основной предикат (последнее нужно юзать с пониманием).
Вопросы.
А как поведёт этот запрос не при 25 клиентах и 100 записях, а при 25000 клиентах и 100000 записях? А если ещё устроить фрагментацию таблиц/индексов?
Почему в сравнении нет Microsoft SQL Server?
Тоже всегда интересовало почему в синтетических тестах берут ничтожно малое количество записей и сравнивают микросекунды. Точность измерения и затраты на другие действия соизмеримы с основными затратами и не понятно как влияют.
Да в общем-то никто не запрещает попробовать то же, но с другим количеством записей. Вот, например, полностью настраиваемый код для MySQL.
Отличный комментарий. Я буду рад если вы проведете такие тесты и опубликуете результаты
Вот тоже такие цифры хотел. А так 1000 записей по 16 байт на строку, всего 16кб, несколько страниц памяти всего. Помимо количества строк бы ещё количество полей больше и строки разной длины...
Вопросы и ремарки к "3. PostgreSQL 16"
"В этом тесте PostgreSQL 16 быстрее выполнил вариант с JOIN + GROUP BY: ~0.415 ms против ~0.803 ms для коррелированного подзапроса."
Вопросы :
Какой размер тестовой выборки? Сколько итераций было выполнено ?
Как менялась нагрузка ?
"Не гадайте. Тестируйте."
В самую точку. Осталось уточнить - как тестировать .
"Ваш лучший друг — это не "золотые правила", а команда EXPLAIN. "
Очень важный вопрос - вы точно уверены , и почему, в том, что стоимость запроса определяет производительность ?
Проще говоря - никогда не сталкивались с ситуацией - стоимость запроса стала меньше , а производительность в ходе нагрузочного тестирования уменьшилась.
Если теоретически - является ли снижение стоимости запроса необходимым и достаточным условием роста производительности СУБД ?
В любом случае , спасибо за статью и тему ближайшего эксперимента. Поставил в план работ. По итогам - будет статья и анализ производительности в ходе нагрузочного тестирования .
Автор, разрешите поинтересоваться в целях повышения образованности, что за шифровки на КДПВ?
MYSSL, PostsGEQUL, SQUITE.
N+1 это другая проблема. Это когда клиент сначала получает данные по всем клиентам, а потом по каждому клиенту делает запросы заказов и считает их. Эта проблема часто в ORM встречается при кривом использовании. В этом случае выполняется 1 + N запрос к БД
В вашем кейсе всё зависит от планировщика. Причём в комментариях правильно заметили, что результат сильно будет зависеть от кол-ва данных. Планировщик БД может просто не использовать индекс например если данных мало, дешевле например fullscan будет сделать. Поэтому план будет отличаться в зависимости от объёма данных.
Скажу за Оракл (для других СУБД, думаю, тоже корректно).
Во-первых, запрос с джойном может быть написан по-другому, чтобы группировка делалась раньше, до объединения:
SELECT
customers.customer_id,
orders_count
FROM customers
LEFT JOIN (select customer_id, COUNT(order_id) AS orders_count from orders GROUP BY customer_id) orders
ON customers.customer_id = orders.customer_id
;
и в этом случае время выполнения такое же, как в варианте с коррелированным подзапросом.
Во-вторых, в выводах по Ораклу Вы пишете про Nested Loop, но в рассматриваемом примере его нет (естественно, ведь нет и индекса по orders(customer_id), при создании foreign key в Оракле индекс не создается автоматически), а есть тот факт, что умный Оракл преобразует Ваш коррелированный подзапрос примерно к тому варианту запроса с джойном, который я написал в пункте выше.
Почитал еще...
PostgreSQL: Оптимизатор PostgreSQL, вероятно, самый умный из всех. Он посмотрел на оба запроса и понял, что для такой маленькой таблицы
customersпланNested Loopбудет эффективным в обоих случаях.EXPLAIN ANALYZEпоказывает, что он выбрал планNested Loopдля обоих запросов.
ГДЕ?? Для варианта с джойном Вы так же, как и с подзапросом в Оракле, увидели несуществующий Nested Loop.
Главный вывод из статьи: автор не умеет читать планы и притягивает за уши выводы. Эпик фейл.
Вообще то "теоретически" SQL декларативный язык, он говорит ЧТО делать но не говорит КАК. Наличие subquery не говорит о том, что надо делать под запросы на каждую запись.
Главный урок: Не гадайте. Тестируйте.
Ваш лучший друг — это не "золотые правила", а команда
EXPLAIN. Всегда проверяйте, какой план выполнения строит ваша СУБД для ваших конкретных данных.
Дополню - "Всегда проверяйте нагрузочным тестированием производительность СУБД после оптимизации отдельного запроса. Результат может быть неожиданным"
Ваш лучший друг - инструмент статистического анализа результатов нагрузочного тестирования.
https://dzen.ru/a/aRXfgpKD9TZMqRAY
Если , кому интересно, в качестве продолжения - ситуация с JOIN vs Коррелированный подзапрос, оказалась очень интересной.
Вывод - использовать нейросети для анализа производительности СУБД PostgreSQL - нельзя.
Для данной виртуальной машины , данной версии СУБД , в ходе данного плана нагрузочного тестирования:
Производительность СУБД при использовании коррелированного подзапроса кардинально выше, чем при использовании JOIN.
Использование JOIN существенно повышает пользовательскую нагрузку на CPU.
С ростом нагрузки - при использовании коррелированного подзапроса количество ожиданий SpinDelay снижается, при использовании JOIN - возрастает.
С ростом нагрузки - при использовании коррелированного подзапроса количество прерываний снижается, при использовании JOIN - возрастает.
https://dzen.ru/a/aRclLgywITzKmFWX
@rozhnev - спасибо за тему для экспериментов
Самый оптимальный вариант - вообще без join
select o.Customer_id, count(*) as orders_count from Orders o group by o.Customer_id
или, если надо получить в том числе клиентов без заказов,
select c.Customer_id, coalesce(t.orders_count, 0) as orders_count
from Customers c
left join (select o.Customer_id, count(*) as orders_count from Orders o group by o.Customer_id) t on t.Customer_id=dd.Customer_id
или чтобы материализовать посчитанных клиентов, не давая оптимизатору много раз проходить по индексу,
select c.Customer_id, coalesce(t.orders_count, 0) as orders_count
from Customers c
left HASH join (select o.Customer_id, count(*) as orders_count from Orders o group by o.Customer_id) t on t.Customer_id=dd.Customer_id
Извините, а в чем смысл этого "теста"? У вас накладные расходы на работу базы больше, чем обращение к данным в памяти. Это как сравнивать грузовики, перевозя банку пива на сто метров.
Интерес представляют OLTP-случаи, когда у нас данных много, но влезают в память, и OLAP, когда данных в разы больше, чем памяти.
Началось с того что я увидел а Линкдин пример где используют подзапрос для подсчета заказов (мой второй пример) и поспешно заявил что это плохой пример запроса и следует исрользовать join.
На что получил комментарий утверждавший что я не прав. В резудььате я захотел разобраться в теме и как результат написал эту статью.
JOIN vs. Коррелированный подзапрос: Разрушаем миф о «N+1» на 4 СУБД