PostgreSQL Antipatterns: редкая запись долетит до середины JOIN

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

    Такие запросы любят «кушать» процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот — чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN…


    Само по себе соединение таблиц не вредно и не полезно — это просто инструмент, но и пользоваться им надо уметь.

    Группировка по недосмотру


    Сначала возьмем совсем простой пример.

    Есть «словарик» на 100 записей (например, это регионы РФ):

    CREATE TABLE tbl_dict AS
    SELECT
      generate_series(0, 100) k;
    ALTER TABLE tbl_dict ADD PRIMARY KEY(k);
    

    … и к нему прилагается таблица связанных «фактов» на 100K записей:

    CREATE TABLE tbl_fact AS
    SELECT
      (random() * 100)::integer k
    , (random() * 1000)::integer v
    FROM
      generate_series(1, 100000);
    CREATE INDEX ON tbl_fact(k);
    

    Теперь попытаемся подсчитать сумму значений по каждому «региону».

    Как слышится, так и пишется


    SELECT
      d.k
    , sum(f.v)
    FROM
      tbl_fact f
    NATURAL JOIN
      tbl_dict d
    GROUP BY
      1;
    

    Само чтение данных заняло только 18% времени, остальное — обработка:


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

    А все потому, что Hash Join и Hash Aggregate пришлось обрабатывать по 100K записей из-за нашего желания группировать по полю связанной таблицы.

    Применяем смекалку


    Но ведь значение этого поля равно значению поля в агрегируемой таблице! То есть нам никто не мешает сначала сгруппировать «факты», а уже потом делать соединение:

    SELECT
      d.k
    , f.sum
    FROM
      (
        SELECT
          k
        , sum(v)
        FROM
          tbl_fact
        GROUP BY
          1
      ) f
    NATURAL JOIN
      tbl_dict d;
    


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

    Безусловно, метод не универсален, но для нашего случая «обычного JOIN» выигрыш по времени в 2 раза с минимальной модификацией запроса — просто за счет «обнулившегося» Hash Join, которому на вход вместо 100K записей пришло только 100.

    Неравные условия


    Теперь усложним задачу: у нас есть 3 таблицы, связанные одним идентификатором — основная и две вспомогательные с некими прикладными данными, по которым мы будем фильтровать.

    Маленькое, но очень важное замечание: пусть на основе «прикладных» знаний целевой задачи нам уже заведомо известно, что условия будут выполняться на первой таблице — почти всегда (для определенности — 3:4), а на второй — очень редко (1:8).

    Мы хотим отобрать из основной и первой вспомогательной таблицы 100 первых по id записей с четными значениями идентификатора, для которых выполняются условия на всех таблицах. Всего записей в таблицах у нас пусть будет снова по 100K.

    Скрипт-генератор
    CREATE TABLE base(
      id
        integer
          PRIMARY KEY
    , val
        integer
    );
    
    INSERT INTO base
    SELECT
      id
    , (random() * 1000)::integer
    FROM
      generate_series(1, 100000) id;
    
    CREATE TABLE ext1(
      id
        integer
          PRIMARY KEY
    , conda
        boolean
    );
    
    INSERT INTO ext1
    SELECT
      id
    , (random() * 4)::integer <> 0 -- 3:4
    FROM
      generate_series(1, 100000) id;
    
    CREATE TABLE ext2(
      id
        integer
          PRIMARY KEY
    , condb
        boolean
    );
    
    INSERT INTO ext2
    SELECT
      id
    , (random() * 8)::integer = 0 -- 1:8
    FROM
      generate_series(1, 100000) id;
    

    Как слышится, так и пишется


    SELECT
      base.*
    , ext1.*
    FROM
      base
    NATURAL JOIN
      ext1
    NATURAL JOIN
      ext2
    WHERE
      id % 2 = 0 AND
      conda AND
      condb
    ORDER BY
      base.id
    LIMIT 100;
    


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

    Отрицательные времена в плане
    По некоторым узлам прошло настолько много циклов, что погрешности округления кое-кого даже загнали в минуса. Как раз про подобные артефакты в планах я буду рассказывать на PGConf.Russia.

    200мс и больше 2GB данных прокачано — не очень хорошо для 100 записей!

    Применяем смекалку


    Используем следующие подходы, чтобы добиться ускорения:

    1. Для начала поймем, что все условия по связанным таблицам нам вообще имеет смысл проверять только при выполнении условия по основной таблице (для четных id).
    2. Данные на выходе должны быть отсортированы по base.id, и для этого нам отлично подойдет первичный ключ этой таблицы!
    3. Данные из ext2 нам вообще не нужны, и используются только для проверки условия. Значит, всю работу с этой таблицей можно смело вынести из JOIN в WHERE-часть. И использовать для проверки EXISTS, а то вдруг такой записи там вообще нет?
    4. Извлекать хоть какие-то данные из ext1 нам надо только в случае успешного прохождения остальных проверок по base и ext2. То есть соединение с ext1 должно идти после всех действий с base/ext2, чего можно добиться с помощью LATERAL.
    5. Чтобы планировщик запроса не пытался вложенную проверку по ext2 превратить в JOIN, подзапрос «спрячем под CASE».

    SELECT
      base.*
    , ext1.*
    FROM
      base
    , LATERAL( -- подзапрос делается заведомо после отбора по base
        SELECT
          *
        FROM
          ext1
        WHERE
          id = base.id AND
          conda -- частое условие
        LIMIT 1
      ) ext1
    WHERE
      CASE
        WHEN base.id % 2 = 0 THEN
          EXISTS( -- подзапрос делается только при прохождении первичного условия
            SELECT
              NULL
            FROM
              ext2
            WHERE
              id = base.id AND
              condb -- редкое условие
            LIMIT 1
          )
      END
    ORDER BY
      base.id -- сортировка пойдет строго по PK, потому что больше не по чему
    LIMIT 100;
    


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

    Запрос, конечно, стал посложнее, но выигрыш в 13 раз по времени и в 350 по «прожорливости» стоит того!

    Снова напомню, что использовать стоит не все способы и не всегда, но знать — лишним не будет.

    Также будет интересно:

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

    Comments 25

      +1
      Подскажите, плз, а это только с PostgreSQL такие проблемы или для других БД аналогично?
      В наименовании статьи PostgreSQL Antipatterns, а дальше ни слова о PostgreSQL.
        +2
        В каждой СУБД планировщик запроса работает по своим принципам. Конкретно для PG приведенные выше примеры и способы справедливы, для других — надо проверять.

        Но, скажем так, БД никогда не сможет знать столько же, сколько сам разработчик. Хотя попытки прикрутить machine learning конкретно к планировщику PostgreSQL уже есть.
          0
          В MSSQL планы почти идентичны. Между «оптимизированым» и «не очень» из данной статьи.
          По моим ощущениям у мелкомягки планировщик немного умнее в этом плане. (с поправкой на особенности принципов работы бд).
          0

          По-хорошему надо проверять в каждой отдельной субд (и даже на разных версиях одной и той же порой можно словить интересные варианты).


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

            0
            Ну, вот, в MSSQL значительного выигрыша нет… потому что планировщик умеет раскладывать подзапросы. =)
            Почти уверен, что оракл тоже должен нормально обработать такой случай.
              0

              Попробовал на оракле — планы разные, но время выполнения отличается порядка 10% максимум. Но мне кажется, для того железа, что у меня на работе, надо каждую таблицу сделать в 1000 раз больше, чтобы задача представляла хоть какую-то сложность ;)

          0
          Такое чувство, что во втором примере вы просто пытаетесь сделать то что оптимизатор почему-то не сделал… подсказываете ему последовательность filter/join
            0
            Это правильное чувство — любой разработчик только тем и занимается, что «подсказывает» машине делать то, что до него кто-то не сделал. В этом случае — разработчики планировщика PG (не в упрек им), видимо.
            И раз уж мы все равно уже работаем при таких вводных, стоит знать, как именно можно этим управлять.
              +3
              Э-э… нет. Если планировщик достаточно мощный — количество необходимых вмешательств становится очень невелико.
              Это легко описать простой зависимостью — чем проще задачи, в которых планировщику требуется подсказка — тем фиговей он. И наоборот — чем сложнее задачи, начинающие требовать явные подсказки — тем лучше.
              Если планировщик плох — код будет выглядеть как нагромаждение нечитаемых костылей, призванных обойти проблемы планировщика…

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

                  Насчёт плохого/хорошего планировщика, это часто зависит от того насколько ваши данные и ваши сервера похожи на те, которые подразумевались разработчиками как наиболее частые в использовании пользователями.
                  Для больших хранилищ данных планировщик иногда мешает, иногда у него просто не может быть достаточно информации для подбора оптимального плана выполнения.

                  0
                  Неужели в PG нельзя подругому это сделать? Прямо вот нет другого способа подсказать планировщику в этом случае последовательность фильтрации?
                  Статистика не?
                    0
                    Если условия во втором примере представлены не статичными полями в ext1/ext2, а динамическими выражениями (от пользовательских фильтров), то статистика особо и не поможет.
                      0

                      мне вот очень не хватает хинтов в синтаксис.
                      вроде есть расширение но я его не пользовал.


                      постгрис, иногда у него случается, выбирает не те индексы.

                  –1

                  а почему не используете WITH, а вложенные запросы?


                  еще хороший подход при "проявляем смекалку" мигрировать от JOIN к LEFT JOIN.


                  Когда выполняется обычный джоин это "окно" движущееся по полной выборке из обоих таблиц


                  а когда выполняется левый джоин — это условия по первой таблице — а затем окно с результатами по второй.


                  то есть то что Вы уносите во вложенный запрос


                  и новомодные словечки вроде NATURAL вводят меня в апсет :)

                    +1

                    Применение LEFT JOIN не гарантирует порядка сканирования, а только описывает целевой результат — можно легко получить вообще Merge Join при выполнении.
                    А CTE — просто не всегда быстро:
                    https://habr.com/ru/post/479298/


                    NATURAL JOIN появился в SQL-92, плохо тянет на новомодность. :)

                      0

                      цель — делать левый джоин с условиями только по левой таблице.


                      тогда порядок сканирования всегда правильный

                        +1
                        Не совсем понял, что имеется в виду. Накладываемые условия ведь зависят не от нас.
                        Можно пример запроса?
                          0

                          в смысле если приведём к


                          SELECT
                            *
                          FROM
                            t1
                          LEFT JOIN t2 USING(id)
                          WHERE
                             -- тут только условия с T1

                          То порядок сканирования будет тот который мы ожидаем.


                          Накладываемые условия ведь зависят не от нас.

                          но запрос мы пишем


                          Вы же в примере написали "организуем так чтобы опросить СПЕРВА то, а ПОТОМ поджойнить" (цитата не из вас, а по памяти)


                          вот так и организуем


                          СПЕРВА-ПОТОМ — это в последовательные блоки WITH идёт
                          а джоины стараемся приводить к левым (с тем чтоб по правым не было фильтрации)


                          и получаются красивые планы запроса, а главное не меняющиеся во времени от роста таблиц

                            0
                            Теорию-то я понял — я не понял, как именно на практике под эту модель переписать запрос для 2й задачи.
                    0

                    Ещё один конкретный пример того, что оптимизаторы SQL-запросов в СУБД промышленного уровня всё равно тупые. Сколько я намучался в своё время с оптимизатором Oracle, который очень своевольничал, — это не перечесть.
                    Иными словами: если вы не понимаете структуру ваших данных, то и работа с ними будет далеко неоптимальной.

                      0
                      Поздновато, но вдруг.
                      А как приведенные примеры из статьи уживаются с различными ORM?
                        0
                        В смысле «как получить такой запрос с помощью ORM»?
                          0
                          Да.
                            0

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

                      Only users with full accounts can post comments. Log in, please.