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

    На 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:

    Тензор
    Разработчик системы СБИС

    Комментарии 19

      0
      Доброго времени суток. Подскажите а у вас в приложении действительно такие сложные сценарии/запросы используются? В первый раз вижу такие сложные оптимизации запросов (если в целом брать ваши статьи).
        0
        Да, это реальные кейсы. Иногда разница в нагрузке между наивной и оптимизированной версией бывает кратной, а «железо» все-таки не резиновое.
          0
          Спасибо. Действительно, очень интересно всегда Вас читать.
          Пишите еще))
          +1
          это все довольно простые запросы. Сложные — это запросы с полусотней джоинов, и подзапросами в 3-4 уровня. Причем на многомилионных таблицах.
            0
            Что-то типа такого? :)

            Многие со мной не согласятся, но я считаю, что «полусотня джойнов» — это все-таки антипаттерн, если нет задачи «написать, чтобы просто хоть один раз отработал».

            Ну и проблемы в больших и сложных запросах обычно появляются как следствие проблем в их маленьких кусочках — вроде таких вариантов с DISTINCT.
              +1
              Ну для витрин данных — просто разбивать 50 джоинов на 5 подвитрин по 10 — смысла не много, если они не переиспользуются. А полста джоинов — это еще мало если просто справочников на факте висит пару дюжин (а бывает для нормального отчета надо и 100 справочников). Конечно большинство из них прямые и простые как валенок хэш джоины, без сюрпризов, но обязательно попадется и справочник без pk, и scd2 и фактов не один а штук 10. И вот уже джоины с подзапросами и дедубликакцией и оконных функций полно т.д. в итоге и получается 5 страниц кода в одном запросе.
              Хотя работает при этом все один проход и быстро.

          0
          А разве планировщик/оптимизатор не соптимизирует такие запросы, даже если написать откровенную чушь?
            0
            Если подать откровенную чушь на вход, результат будет соответствующий. И странно ожидать иного.
            0
            SELECT
              *
            FROM
              X
            WHERE
              EXISTS(
                SELECT
                  NULL
                FROM

            NULL тут лишний.
              0
              А как же без него?
                0
                Так:

                SELECT
                  *
                FROM
                  X
                WHERE
                  EXISTS(
                    SELECT
                    FROM
                  0
                  select без полей — это как-то совсем некрасиво. А парсер от какой версии такое понимает?
                    0
                    Испокон веков.
                      0
                      У меня вот 9.3, пустой SELECT вызывает ошибку синтаксиса
                        0
                        Экая древность. Он разве ещё поддерживается?
                          0
                          Я так понимаю, у вас «испокон веков» понятие плавающее?
                          Если что, то вполне себе используется, и до кучи заявленное поведение начало работать только с версии 9.4
                            0
                            Для меня шесть лет — вполне себе срок, чтобы так говорить. Скоро уже 13-я версия Постгреса выйдет.
                            Если что, то вполне себе используется…
                            Я про поддержку говорю, использование — на вашей совести.
              0
              Можете объяснить, почему во втором примере («Зачем платить больше»: DISTINCT [ON] + LIMIT 1) запросы эквивалентны в общем случае?

              Если рассматривать join, а не left join, то возможна же ситуация, когда этот
              SELECT
                *
              FROM
                (
                  SELECT
                    *
                  FROM
                    X
                  -- сюда можно подсунуть подходящих условий
                  LIMIT 1 -- +1 Limit
                ) X
              JOIN
                Y
                  ON Y.fk = X.pk

              джоин даст в результате 0 записей. Хотя если убрать LIMIT 1, то записи найдутся.
                0
                В общем случае они НЕ эквивалентны, но мы рассматриваем именно ситуацию, когда разницы нет — например, если по прикладным причинам всегда имеет место хотя бы одна связанная запись.

                Или, если поменять X и Y местами, пример станет нагляднее, когда Y.fk действительно foreign key.

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

              Самое читаемое