PostgreSQL Antipatterns: вредные JOIN и OR

    Бойтесь операций, buffers приносящих…
    На примере небольшого запроса рассмотрим некоторые универсальные подходы к оптимизации запросов на PostgreSQL. Пользоваться ими или нет — выбирать вам, но знать о них стоит.

    В каких-то последующих версиях PG ситуация может измениться с «поумнением» планировщика, но для 9.4/9.6 она выглядит примерно одинаково, как примеры тут.

    Возьму вполне реальный запрос:
    SELECT
      TRUE
    FROM
      "Документ" d
    INNER JOIN
      "ДокументРасширение" doc_ex
        USING("@Документ")
    INNER JOIN
      "ТипДокумента" t_doc ON
        t_doc."@ТипДокумента" = d."ТипДокумента"
    WHERE
      (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
      d."$Черновик" IS NULL AND
      d."Удален" IS NOT TRUE AND
      doc_ex."Состояние"[1] IS TRUE AND
      t_doc."ТипДокумента" = 'ПланРабот'
    LIMIT 1;

    про имена таблиц и полей
    К «русским» названиям полей и таблиц можно относиться по-разному, но это дело вкуса. Поскольку у нас в «Тензоре» нет разработчиков-иностранцев, а PostgreSQL позволяет нам давать названия хоть иероглифами, если они заключены в кавычки, то мы предпочитаем именовать объекты однозначно-понятно, чтобы не возникало разночтений.

    Посмотрим на получившийся план:

    [посмотреть на explain.tensor.ru]

    144ms и почти 53K buffers — то есть больше 400MB данных! И нам повезет, если все они окажутся в кэше к моменту нашего запроса, иначе он станет в разы дольше при вычитывании с диска.

    Алгоритм важнее всего!


    Чтобы как-то оптимизировать любой запрос, надо сначала понять, что же он вообще должен делать.
    Оставим пока за рамками этой статьи разработку самой структуры БД, и договоримся, что мы можем относительно «дешево» переписать запрос и/или накатить на базу какие-то нужные нам индексы.

    Итак, запрос:
    — проверяет существование хоть какого-то документа
    — в нужном нам состоянии и определенного типа
    — где автором или исполнителем является нужный нам сотрудник

    JOIN + LIMIT 1


    Достаточно часто разработчику проще написать запрос, где сначала делается соединение большого количества таблиц, а потом из всего этого множества остается одна-единственная запись. Но проще для разработчика — не значит эффективнее для БД.
    В нашем случае таблиц было всего 3 — а какой эффект…

    Давайте для начала избавимся от соединения с таблицей «ТипДокумента», а заодно подскажем базе, что у запись типа у нас уникальна (мы-то это знаем, а вот планировщик пока не догадывается):
    WITH T AS (
      SELECT
        "@ТипДокумента"
      FROM
        "ТипДокумента"
      WHERE
        "ТипДокумента" = 'ПланРабот'
      LIMIT 1
    )
    ...
    WHERE
      d."ТипДокумента" = (TABLE T)
    ...

    Да, если таблица/CTE состоит из единственного поля единственной же записи, то в PG можно писать даже так, вместо
    d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)


    «Ленивые» вычисления в запросах PostgreSQL



    BitmapOr vs UNION


    В некоторых случаях Bitmap Heap Scan будет стоить нам очень дорого — например, в нашей ситуации, когда достаточно много записей подпадает под требуемое условие. Получили мы его из-за OR-условия, превратившегося в BitmapOr-операцию в плане.
    Вернемся к исходной задаче — надо найти запись, соответствующую любому из условий — то есть незачем искать все 59K записей по обоим условиям. Есть способ отработать одно условие, а ко второму перейти только когда по первому ничего не нашлось. Нам поможет такая конструкция:
    (
      SELECT
        ...
      LIMIT 1
    )
    UNION ALL
    (
      SELECT
        ...
      LIMIT 1
    )
    LIMIT 1

    «Внешний» LIMIT 1 гарантирует, что поиск завершится при нахождении первой же записи. И если она найдется уже в первом блоке, выполнение второго осуществляться не будет (never executed в плане).

    «Прячем под CASE» сложные условия


    В исходном запросе есть крайне неудобный момент — проверка состояния по связанной таблице «ДокументРасширение». Независимо от истинности остальных условий в выражении (например, d.«Удален» IS NOT TRUE), это соединение выполняется всегда и «стоит ресурсов». Больше или меньше их будет потрачено — зависит от объема этой таблицы.
    Но можно модифицировать запрос так, чтобы поиск связанной записи происходил бы только когда это действительно необходимо:
    SELECT
      ...
    FROM
      "Документ" d
    WHERE
      ... /*index cond*/ AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END

    Раз из связываемой таблицы нам не нужно для результата ни одно из полей, то мы имеем возможность превратить JOIN в условие по подзапросу.
    Оставим индексируемые поля «за скобками» CASE, простые условия от записи вносим в WHEN-блок — и теперь «тяжелый» запрос выполняется только при переходе в THEN.

    Моя фамилия «Итого»


    Собираем результирующий запрос со всеми описанными выше механиками:
    WITH T AS (
      SELECT
        "@ТипДокумента"
      FROM
        "ТипДокумента"
      WHERE
        "ТипДокумента" = 'ПланРабот'
    )
      (
        SELECT
          TRUE
        FROM
          "Документ" d
        WHERE
          ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
          CASE
            WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
              SELECT
                "Состояние"[1] IS TRUE
              FROM
                "ДокументРасширение"
              WHERE
                "@Документ" = d."@Документ"
            )
          END
        LIMIT 1
      )
    UNION ALL
      (
        SELECT
          TRUE
        FROM
          "Документ" d
        WHERE
          ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
          CASE
            WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
              SELECT
                "Состояние"[1] IS TRUE
              FROM
                "ДокументРасширение"
              WHERE
                "@Документ" = d."@Документ"
            )
          END
        LIMIT 1
      )
    LIMIT 1;

    Подгоняем [под] индексы


    Наметанный глаз заметил, что индексируемые условия в подблоках UNION чуть разнятся — это потому, что у нас уже есть подходящие индексы на таблице. А если бы их не было — то стоило бы создать: Документ(Лицо3, ТипДокумента) и Документ(ТипДокумента, Сотрудник).
    о порядке полей в ROW-условиях
    С точки зрения планировщика, конечно, можно написать и (A, B) = (constA, constB), и (B, A) = (constB, constA). Но при записи в порядке следования полей в индексе, такой запрос просто удобнее потом отлаживать.

    Что в плане?

    [посмотреть на explain.tensor.ru]

    К сожалению, нам не повезло, и в первом UNION-блоке ничего не нашлось, поэтому второй все-таки пошел на выполнение. Но даже при этом — всего 0.037ms и 11 buffers!
    Мы ускорили запрос и сократили «прокачку» данных в памяти в несколько тысяч раз, воспользовавшись достаточно простыми методиками — неплохой результат при небольшой копипасте. :)
    Тензор
    Разработчик системы СБИС

    Похожие публикации

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

      0
      интересно как выглядит план выполнения, если cделать только одно изменение — заменить OR на UNION + LIMIT 1 без остальных изменений?
        0
        Поскольку это в нашем примере самое больное место, то и эффект от него максимальный: 0.046ms/14 buffers
        +2

        Вот она, главная боль sql — то, что задумывалось (и старается быть) декларативным описанием желаемого результата, хочешь, не хочешь, а выкидывает иногда такие коленца, что надо забыть об абстракциях, и вместо простого "чо хочу" приходится объяснять ему на пальцах, как не надо. И как хорошо, что подобные кудрявости далеко не на каждом запросе встают — читаемость после такой подкапотной оптимизации резко падает, и через полгода даже чтобы просто прочитать и понять приходится расчехлять бубен.
        Что там Балу говорил Маугли про счастье? "Простые запросы", кажется? Даже жаль иногда, что этот принцип не совместим с реальностью...

          0

          Больше того, иногда очень хочется помочь оптимизатору, хотя бы указав с какой таблицы или индекса начинать. И это тоже боль — чувствовать себя беспомощным не имея хинтов в Постгресе.

            0
            Справедливости ради, хинты — признак беспомощности или недостаточного понимания что происходит в запросе.

            Но такие танцы с бубном действительно что то очень странное)
              +1

              Просто в вашей практике хинты не были нужны. Когда решение тиражируется на десятки и сотни инстансов, а на местах нет грамотных DBA, то хинты зачастую это единственный выход. Бывает что сбор статистики по какой-то причине вообще отключен.
              Кроме того, бывает что система разворачивается в секретном контуре и доступ к ней имеют только федеральные агенты, которые однако ничего не смыслят в IT.
              Ещё вариант — нет доступа к исходникам, а есть запрос, который нужно оптимизнуть. Тогда можно сохранить для него специальный план. Был такой случай, когда в топ AWR на огромном федеральном проекте постоянно вылезал запрос, выполняемый ядром Oracle Application Server, и даже у самих сотрудников Oracle не было возможности это починить иначе чем сохраненным планом.


              Вообще, все дело в наличии актуальной статистики. Иногда, однако, в течение дня характер и распределение данных меняются кардиально. Например, система принимает и обрабатывает миллионы бизнес объектов, и в какой-то момент запускается операция обработки полученных данных (консолидация, аналитика, matching и т.п.), а статистика собиралась 8 часов назад, т.к. на больших БД это довольно длительная и затратная операция. Результат — оптимизатор выбирает неверный алгоритм соединения таблиц или неверный индекс и т.п.
              Поэтому все мои знакомые DBA и senior developers, в том числе сотрудники Oracle, используют хинты не просто иногда, а как обыденный инструмент каждый день.


              Ни один оптимизатор не может сравниться с человеком, имеющим десятки лет опыта, знающим всю подноготную этого алгоритма.

                0
                Ну так вы сами описали как раз то что я и описывал — случаи, когда люди не очень понимают что делать, когда не имеют возможности что то сделать или когда недостаточно настроена база, что без хинтов никак.

                С другой стороны, я говорил в основном о хинтах в запросах, типа подсказок каким джоином джоинить, экспандить или нет и какие индексы брать. =)
                Иногда, однако, в течение дня характер и распределение данных меняются кардиально.
                вот с этим можно и нужно бороться, очевидно.
                Результат — оптимизатор выбирает неверный алгоритм соединения таблиц или неверный индекс и т.п.
                Для особо запущенных случаев, как было замечено, можно и план сохранить. Но всё же лучше решить проблему системно. Хотя бы потому что потом придёт новый разработчик, прогонит хинты на текущем состоянии базы и выбросить нафиг… )

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

                ЗЫ:
                сотрудники Oracle
                В оракле, возможно, всё иначе. Хотя, вроде, у него планировщик получше MSного.

          +2

          Первый антипаттерн — называть поля русским текстом. Читаемость нулевая.

            0
            А хинты не используете потому что прокладка не поддерживает хинты?
            0

            После беглого прочтения, кажется запрос очень странным. Ищем первый попавшийся план работ в нужном состоянии от заданного сотрудника. Почему первый попавшийся?


            Допускаю, что большая часть документов создана этим сотрудником и поэтому индексы по «Сотрудник» и «Лицо3» не используются или их просто нет. Но в этом случае, искать «первый попавшийся» совсем странно.


            Если же упомянутые индексы достаточно селективны, то PG будет использовать их. Буквально — пройтись по ветке индекса для заданного сотрудника и найти первый попавшийся документ подпадающий под условия, затем проверить его тип в другой таблице и завершить запрос если тип подошёл.


            Типов документов может быть много, тогда индекс по (Сотрудник, Тип Документа) решит все проблемы.


            В сухом остатке — я не вижу причин переписывать исходный запрос.

              0
              Почему первый попавшийся?
              Потому что хотим проверить просто наличие такой записи. Например, если сотрудник еще ни разу не оформлял себе или кому-то отпуска — давайте покажем ему в интерфейсе подсказку, как сделать это правильно.
              Если же упомянутые индексы достаточно селективны, то PG будет использовать их.… В сухом остатке — я не вижу причин переписывать исходный запрос.
              Все планы сняты в одном и том же состоянии БД, то есть со всеми необходимыми индексами. И тем не менее — вот такое отличие в производительности.
                0
                Долгий Bitmap heap scan говорит о том, что документов у этого сотрудника много, причем большая часть из них удалена или черновик или состояние у документа не то. Можно предположить, что если включить в индекс одно из этих полей, то запрос начнет выполняться намного быстрее.

                Изначальный план запроса, отрабатывает 144 ms, хотя нужна всего одна запись. Если мои предположения о характере данных верны, то возможно во всей базе и есть только одна подходящая запись и поэтому запрос такой долгий. В вашем финальном варианте довольно тяжело разобраться. Нет гарантий, что он быстрее только потому, что запросу везет и он встречает эту запись, начиная сканировать таблицу «где нужно». Во всяком случае я не вижу, почему финальный вариант быстрее.
                  0

                  Если пристально посмотреть на исходный план, то видно, что по всем дополнительным условиям в Bitmap Heap Scan отфильтровалось всего 1.5K записей из 53K. А до проверки состояния по связанной таблице дело вовсе не дошло (never executed), потому что не нашлось ни одной записи после соединения с ТипДокумента.
                  То есть всех документов по сотруднику — много, а нужного типа — нет.
                  Но планировщик хоть и понимает, что идентификатор типа у нас уникален (rows=1), пробросить в индекс его через InitPlan не пытается. Вот и стоит ему помочь.

                    0
                    Похоже у сотрудника очень много документов, но лишь малая часть из них это нужные нам планы. Почему бы не сделать частичный индекс по (сотрудник/лицо, тип документа) где тип документа == «план»?
                      0
                      Потому что список типов заранее не определен и расширяется самим пользователем. Так что под каждый отдельный индекс не придумаешь заранее.
              0

              LIMIT без ORDER BY? Выглядит не очень, чтобы очень.

                0
                А зачем нужен ORDER, если нам все равно какая запись?
                  0

                  А вы посмотрите план с ORDER BY и без. Мне, например, очень интересно, что будет.

                    0
                    Какой из вариантов запроса и по какому полю посортировать?
                      0

                      Вариант — любой, поле — индексированное.

                        0
                        Посортировал исходный по ТипДокумента — то же самое. Хотя есть индекс Документ(ТипДокумента, Сотрудник).

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

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