Регистрация

[PostgreSQL, Программирование, SQL, Администрирование баз данных] PostgreSQL Antipatterns: «Должен остаться только один!»

Ответить на тему
Автор Сообщение
news_bot ®

Стаж: 6 лет 2 месяца
Сообщений: 27286

Создавать темы news_bot ® написал(а)
04-Авг-2020 18:30
Цитировать

На SQL вы описываете «что» хотите получить, а не «как» это должно исполняться. Поэтому проблема разработки SQL-запросов в стиле «как слышится, так и пишется» занимает свое почетное место, наряду с особенностями вычисления условий в SQL.
Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования GROUP/DISTINCT и LIMIT вместе с ними.
Вот если вы написали в запросе «сначала соедини эти таблички, а потом выкинь все дубли, должен остаться только один экземпляр по каждому ключу» — именно так и будет работать, даже если соединение вовсе не было нужно.
И иногда везет и это «просто работает», иногда — неприятно сказывается на производительности, а иногда дает абсолютно неожидаемые с точки зрения разработчика эффекты.

Ну, может, не настолько зрелищные, но…
«Сладкая парочка»: JOIN + DISTINCT
SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Как бы понятно, что хотели отобрать такие записи X, для которых в Y есть связанные с выполняющимся условием. Написали запрос через JOIN — получили какие-то значения pk по несколько раз (ровно сколько подходящих записей в Y оказалось). Как убрать? Конечно DISTINCT!
Особенно «радует», когда для каждой X-записи находится по несколько сотен связанных Y-записей, а потом героически убираются дубли…

Как исправить? Для начала осознать, что задачу можно модифицировать до «отобрать такие записи X, для которых в Y есть ХОТЯ БЫ ОДНА связанная с выполняющимся условием» — ведь из самой Y-записи нам ничего не нужно.
Вложенный EXISTS
SELECT
  *
FROM
  X
WHERE
  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
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;
Этот же вариант позволяет при необходимости заодно сразу вернуть какие-то данные из нашедшейся связанной Y-записи. Похожий вариант рассмотрен в статье «PostgreSQL Antipatterns: редкая запись долетит до середины JOIN».

«Зачем платить больше»: DISTINCT [ON] + LIMIT 1
Дополнительным преимуществом подобных преобразований запроса является возможность легко ограничить перебор записей, если нужно только одна/несколько из них, как в следующем случае:
SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Теперь читаем запрос и пытаемся понять, что предлагается сделать СУБД:
  • соединяем таблички
  • уникализируем по X.pk
  • из оставшихся записей выбираем какую-то одну

То есть получили что? «Какую-то одну запись» из уникализованных — а если брать эту одну из неуникализованных результат разве как-то изменится?.. «А если нет разницы, зачем платить больше?»
SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 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
  pk
FROM
  X
LIMIT $1

Наивный разработчик может искренне полагать, что выполнение запроса остановится, как только мы найдем $1 первых попавшихся разных значений.
Когда-то в будущем это может так и будет работать благодаря новому узлу Index Skip Scan, реализация которого сейчас прорабатывается, но пока — нет.
Пока что сначала будут извлечены все-все записи, уникализированы, и только уже из них вернется сколько запрошено. Особенно грустно бывает, если мы хотели что-то вроде $1 = 4, а записей в таблице — сотни тысяч…
Чтобы не грустить попусту, воспользуемся рекурсивным запросом «DISTINCT для бедных» из PostgreSQL Wiki:

 ===========
 Источник:
habr.com
===========
Похожие новости: Теги для поиска: #_postgresql, #_programmirovanie (Программирование), #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_sql, #_dba, #_sql_tips_and_tricks, #_group, #_distinct, #_lateral, #_exists, #_blog_kompanii_tenzor (
Блог компании Тензор
), #_postgresql, #_programmirovanie (
Программирование
), #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Профиль  ЛС 
Показать сообщения:     
Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 27-Апр 19:12
Часовой пояс: UTC + 5