Русский
Русский
English
Статистика
Реклама

PostgreSQL Antipatterns Должен остаться только один!

На SQL вы описываете что хотите получить, а не как это должно исполняться. Поэтому проблема разработки SQL-запросов в стиле как слышится, так и пишется занимает свое почетное место, наряду с особенностями вычисления условий в SQL.

Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования 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:

Источник: habr.com
К списку статей
Опубликовано: 04.08.2020 16:15:07
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Программирование

Sql

Администрирование баз данных

Dba

Sql tips and tricks

Group

Distinct

Lateral

Exists

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru