Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования
GROUP/DISTINCT и
LIMIT вместе с ними.Вот если вы написали в запросе сначала соедини эти таблички, а потом выкинь все дубли, должен остаться только один экземпляр по каждому ключу именно так и будет работать, даже если соединение вовсе не было нужно.
И иногда везет и это просто работает, иногда неприятно сказывается на производительности, а иногда дает абсолютно неожидаемые с точки зрения разработчика эффекты.
Ну, может, не настолько зрелищные, но
Сладкая парочка: JOIN + DISTINCT
SELECT DISTINCT X.*FROM XJOIN Y ON Y.fk = X.pkWHERE Y.bool_condition;
Как бы понятно, что хотели отобрать такие записи X, для которых в Y есть связанные с выполняющимся условием. Написали запрос через
JOIN получили какие-то значения pk по несколько
раз (ровно сколько подходящих записей в Y оказалось). Как убрать?
Конечно DISTINCT!Особенно радует, когда для каждой X-записи находится по несколько сотен связанных Y-записей, а потом героически убираются дубли
Как исправить? Для начала осознать, что задачу можно модифицировать до отобрать такие записи X, для которых в Y есть ХОТЯ Б ОДНА связанная с выполняющимся условием ведь из самой Y-записи нам ничего не нужно.
Вложенный EXISTS
SELECT *FROM XWHERE EXISTS( SELECT NULL FROM Y WHERE fk = X.pk AND bool_condition LIMIT 1 );
Некоторые версии PostgreSQL понимают, что в EXISTS достаточно найти первую попавшуюся запись, более старые нет. Поэтому я предпочитаю всегда указывать
LIMIT 1 внутри
EXISTS.LATERAL JOIN
SELECT X.*FROM X, LATERAL ( SELECT Y.* FROM Y WHERE fk = X.pk AND bool_condition LIMIT 1 ) YWHERE Y IS DISTINCT FROM NULL;
Этот же вариант позволяет при необходимости заодно сразу вернуть какие-то данные из нашедшейся связанной Y-записи. Похожий вариант рассмотрен в статье PostgreSQL Antipatterns: редкая запись долетит до середины JOIN.
Зачем платить больше: DISTINCT [ON] + LIMIT 1
Дополнительным преимуществом подобных преобразований запроса является возможность легко ограничить перебор записей, если нужно только одна/несколько из них, как в следующем случае:
SELECT DISTINCT ON(X.pk) *FROM XJOIN Y ON Y.fk = X.pkLIMIT 1;
Теперь читаем запрос и пытаемся понять, что предлагается сделать СУБД:
- соединяем таблички
- уникализируем по X.pk
- из оставшихся записей выбираем какую-то одну
То есть получили что? Какую-то одну запись из уникализованных а если брать эту одну из неуникализованных результат разве как-то изменится?.. А если нет разницы, зачем платить больше?
SELECT *FROM ( SELECT * FROM X -- сюда можно подсунуть подходящих условий LIMIT 1 -- +1 Limit ) XJOIN Y ON Y.fk = X.pkLIMIT 1;
И точно такая же тема с
GROUP BY + LIMIT 1.Мне только спросить: неявный GROUP + LIMIT
Подобные вещи встречаются при разных проверках непустоты таблички или CTE по ходу выполнения запроса:
...CASE WHEN ( SELECT count(*) FROM X LIMIT 1 ) = 0 THEN ...
Агрегатные функции (
count/min/max/sum/...) успешно
выполняются на всем наборе, даже без явного указания GROUP
BY. Только вот с LIMIT они дружат не очень.Разработчик может думать вот если там записи есть, то мне надо не больше LIMIT. Но не надо так! Потому что для базы это:
- посчитай, что хотят по всем записям
- отдай столько строк, сколько просят
В зависимости от целевых условий тут уместно совершить одну из замен:
-
(count + LIMIT 1) = 0наNOT EXISTS(LIMIT 1) -
(count + LIMIT 1) > 0наEXISTS(LIMIT 1) -
count >= Nна(SELECT count(*) FROM (... LIMIT N))
Сколько вешать в граммах: DISTINCT + LIMIT
SELECT DISTINCT pkFROM XLIMIT $1
Наивный разработчик может искренне полагать, что выполнение запроса остановится, как только мы найдем $1 первых попавшихся разных значений.
Когда-то в будущем это может так и будет работать благодаря новому узлу Index Skip Scan, реализация которого сейчас прорабатывается, но пока нет.
Пока что сначала будут извлечены все-все записи, уникализированы, и только уже из них вернется сколько запрошено. Особенно грустно бывает, если мы хотели что-то вроде $1 = 4, а записей в таблице сотни тысяч
Чтобы не грустить попусту, воспользуемся рекурсивным запросом DISTINCT для бедных из PostgreSQL Wiki:
