Рецепты для хворающих SQL-запросов

    Несколько месяцев назад мы анонсировали explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.

    За прошедшее время вы уже воспользовались им более 6000 раз, но одна из удобных функций могла остаться незамеченной — это структурные подсказки, которые выглядят примерно так:



    Прислушивайтесь к ним, и ваши запросы «станут гладкими и шелковистыми». :)

    А если серьезно, то многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

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



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

    Для лучшего погружения в тему сначала можно послушать соответствующий блок из моего доклада на PGConf.Russia 2020, а уже потом перейти к детальному разбору каждого примера:

    1. индексная «недосортировка»
    2. пересечение индексов (BitmapAnd)
    3. объединение индексов (BitmapOr)
    4. читаем много лишнего
    5. разреженная таблица
    6. чтение с «середины» индекса
    7. CTE × CTE
    8. swap на диск (temp written)
    9. неактуальная статистика
    10. «что-то пошло не так»



    #1: индексная «недосортировка»


    Когда возникает


    Показать последний счет по клиенту «ООО Колокольчик».

    Как опознать


    -> Limit
       -> Sort
          -> Index [Only] Scan [Backward] | Bitmap Heap Scan
    

    Рекомендации


    Используемый индекс расширить полями сортировки.

    Пример:

    CREATE TABLE tbl AS
    SELECT
      generate_series(1, 100000) pk  -- 100K "фактов"
    , (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
    
    CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
    
    SELECT
      *
    FROM
      tbl
    WHERE
      fk_cli = 1 -- отбор по конкретной связи
    ORDER BY
      pk DESC -- хотим всего одну "последнюю" запись
    LIMIT 1;


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

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

    Исправляем:

    DROP INDEX tbl_fk_cli_idx;
    CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
    


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

    Даже на такой примитивной выборке — в 8.5 раз быстрее и в 33 раза меньше чтений. Эффект будет тем нагляднее, чем больше у вас «фактов» по каждому значению fk.

    Замечу, что такой индекс будет работать как «префиксный» не хуже прежнего и по другим запросам с fk, где сортировки по pk не было и нет (подробнее про это можно прочитать в моей статье про поиск неэффективных индексов). В том числе, он обеспечит и нормальную поддержку явного foreign key по этому полю.

    #2: пересечение индексов (BitmapAnd)


    Когда возникает


    Показать все договоры по клиенту «ООО Колокольчик», заключенные от имени «НАО Лютик».

    Как опознать


    -> BitmapAnd
       -> Bitmap Index Scan
       -> Bitmap Index Scan

    Рекомендации


    Создать составной индекс по полям из обоих исходных или расширить один из существующих полями из второго.

    Пример:

    CREATE TABLE tbl AS
    SELECT
      generate_series(1, 100000) pk      -- 100K "фактов"
    , (random() *  100)::integer fk_org  -- 100 разных внешних ключей
    , (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
    
    CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
    CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
    
    SELECT
      *
    FROM
      tbl
    WHERE
      (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре


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

    Исправляем:

    DROP INDEX tbl_fk_org_idx;
    CREATE INDEX ON tbl(fk_org, fk_cli);
    


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

    Тут выигрыш меньше, поскольку Bitmap Heap Scan достаточно эффективен сам по себе. Но все-таки в 7 раз быстрее и в 2.5 раза меньше чтений.

    #3: объединение индексов (BitmapOr)


    Когда возникает


    Показать первые 20 самых старых «своих» или неназначенных заявок для обработки, причем свои в приоритете.

    Как опознать


    -> BitmapOr
       -> Bitmap Index Scan
       -> Bitmap Index Scan

    Рекомендации


    Использовать UNION [ALL] для объединения подзапросов по каждому из OR-блоков условий.

    Пример:

    CREATE TABLE tbl AS
    SELECT
      generate_series(1, 100000) pk  -- 100K "фактов"
    , CASE
        WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
        ELSE (random() * 100)::integer -- 100 разных внешних ключей
      END fk_own;
    
    CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
    
    SELECT
      *
    FROM
      tbl
    WHERE
      fk_own = 1 OR -- свои
      fk_own IS NULL -- ... или "ничьи"
    ORDER BY
      pk
    , (fk_own = 1) DESC -- сначала "свои"
    LIMIT 20;
    


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

    Исправляем:

    (
      SELECT
        *
      FROM
        tbl
      WHERE
        fk_own = 1 -- сначала "свои" 20
      ORDER BY
        pk
      LIMIT 20
    )
    UNION ALL
    (
      SELECT
        *
      FROM
        tbl
      WHERE
        fk_own IS NULL -- потом "ничьи" 20
      ORDER BY
        pk
      LIMIT 20
    )
    LIMIT 20; -- но всего - 20, больше и не надо


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

    Мы воспользовались тем, что все 20 нужных записей были сразу получены уже в первом блоке, поэтому второй, с более «дорогим» Bitmap Heap Scan, даже не выполнялся — в итоге в 22 раза быстрее, в 44 раза меньше чтений!

    Более детальный рассказ о данном способе оптимизации на конкретных примерах можно прочитать в статьях PostgreSQL Antipatterns: вредные JOIN и OR и PostgreSQL Antipatterns: сказ об итеративной доработке поиска по названию, или «Оптимизация туда и обратно».

    Обобщенный вариант упорядоченного отбора по нескольким ключам (а не только по паре const/NULL) рассмотрен в статье SQL HowTo: пишем while-цикл прямо в запросе, или «Элементарная трехходовка».

    #4: читаем много лишнего


    Когда возникает


    Как правило, возникает при желании «прикрутить еще один фильтр» к уже существующему запросу.
    «А у вас нет такого же, но с перламутровыми пуговицамих/ф «Бриллиантовая рука»

    Например, модифицируя задачу выше, показать первые 20 самых старых «критичных» заявок для обработки, независимо от их назначенности.

    Как опознать


    -> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
       && 5 × rows < RRbF -- отфильтровано >80% прочитанного
       && loops × RRbF > 100 -- и при этом больше 100 записей суммарно
    

    Рекомендации


    Создать [более] специализированный индекс с WHERE-условием или включить в индекс дополнительные поля.
    Если условие фильтрации является «статичным» для ваших задач — то есть не предполагает расширения перечня значений в будущем — лучше использовать WHERE-индекс. В эту категорию хорошо укладываются разные boolean/enum-статусы.

    Если же условие фильтрации может принимать разные значения, то лучше расширить индекс этими полями — как в ситуации с BitmapAnd выше.

    Пример:

    CREATE TABLE tbl AS
    SELECT
      generate_series(1, 100000) pk -- 100K "фактов"
    , CASE
        WHEN random() < 1::real/16 THEN NULL
        ELSE (random() * 100)::integer -- 100 разных внешних ключей
      END fk_own
    , (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
    
    CREATE INDEX ON tbl(pk);
    CREATE INDEX ON tbl(fk_own, pk);
    
    SELECT
      *
    FROM
      tbl
    WHERE
      critical
    ORDER BY
      pk
    LIMIT 20;


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

    Исправляем:

    CREATE INDEX ON tbl(pk)
      WHERE critical; -- добавили "статичное" условие фильтрации
    


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

    Как видим, фильтрация из плана полностью ушла, а запрос стал в 5 раз быстрее.

    #5: разреженная таблица


    Когда возникает


    Разнообразные попытки сделать собственную очередь обработки задач, когда большое количество обновлений/удалений записей на таблице приводят к ситуации большого количества «мертвых» записей.

    Как опознать


    -> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
       && loops × (rows + RRbF) < (shared hit + shared read) × 8
          -- прочитано больше 1KB на каждую запись
       && shared hit + shared read > 64
    

    Рекомендации


    Регулярно вручную проводить VACUUM [FULL] или добиться адекватно частой отработки autovacuum с помощью тонкой настройки его параметров, в том числе для конкретной таблицы.
    В большинстве случаев подобные проблемы оказываются вызваны плохой компоновкой запросов при вызовах с бизнес-логики вроде тех, которые были рассмотрены в PostgreSQL Antipatterns: сражаемся с ордами «мертвецов».

    Но надо понимать, что даже VACUUM FULL может помочь не всегда. Для таких случаев стоит ознакомиться с алгоритмом из статьи DBA: когда пасует VACUUM — чистим таблицу вручную.

    #6: чтение с «середины» индекса


    Когда возникает


    Вроде и прочитали немного, и все по индексу, и никого лишнего не фильтровали — а все равно прочитано существенно больше страниц, чем хотелось бы.

    Как опознать


    -> Index [Only] Scan [Backward]
       && loops × (rows + RRbF) < (shared hit + shared read) × 8
          -- прочитано больше 1KB на каждую запись
       && shared hit + shared read > 64
    

    Рекомендации


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

    Пример:

    CREATE TABLE tbl AS
    SELECT
      generate_series(1, 100000) pk      -- 100K "фактов"
    , (random() *  100)::integer fk_org  -- 100 разных внешних ключей
    , (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
    
    CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
    -- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
    
    SELECT
      *
    FROM
      tbl
    WHERE
      fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
    LIMIT 20;


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

    Вроде бы все хорошо, даже по индексу, но как-то подозрительно — на каждую из 20 прочитанных записей пришлось вычитать по 4 страницы данных, 32KB на запись — не жирно ли? Да и имя индекса tbl_fk_org_fk_cli_idx наводит на размышления.

    Исправляем:

    CREATE INDEX ON tbl(fk_cli);


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

    Внезапно — в 10 раз быстрее, и в 4 раза меньше читать!
    Другие примеры ситуаций неэффективного использования индексов можно увидеть в статье DBA: находим бесполезные индексы.

    #7: CTE × CTE


    Когда возникает


    В запросе набрали «жирных» CTE из разных таблиц, а потом решили сделать между ними JOIN.

    Кейс актуален для версий ниже v12 или запросов с WITH MATERIALIZED.

    Как опознать


    -> CTE Scan
       && loops > 10
       && loops × (rows + RRbF) > 10000
          -- слишком большое декартово произведение CTE
    

    Рекомендации


    Внимательно проанализировать запрос — а нужны ли тут вообще CTE? Если все-таки да, то применить «ословаривание» в hstore/json по модели, описанной в PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN.

    #8: swap на диск (temp written)


    Когда возникает


    Разовая обработка (сортировка или уникализация) большого количества записей не влезает в выделенную для этого память.

    Как опознать


    -> *
       && temp written > 0

    Рекомендации


    Если использованное операцией количество памяти не сильно превышает установленное значение параметра work_mem, стоит его скорректировать. Можно сразу в конфиге для всех, а можно через SET [LOCAL] для конкретного запроса/транзакции.

    Пример:

    SHOW work_mem;
    -- "16MB"
    
    SELECT
      random()
    FROM
      generate_series(1, 1000000)
    ORDER BY
      1;


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

    Исправляем:

    SET work_mem = '128MB'; -- перед выполнением запроса


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

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

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

    #9: неактуальная статистика


    Когда возникает


    В базу влили сразу много, но не успели прогнать ANALYZE.

    Как опознать


    -> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
       && ratio >> 10

    Рекомендации


    Провести-таки ANALYZE.
    Подробнее данная ситуация расписана в PostgreSQL Antipatterns: статистика всему голова.

    #10: «что-то пошло не так»


    Когда возникает


    Случилось ожидание блокировки, наложенной конкурирующим запросом, или не хватило аппаратных ресурсов CPU/гипервизора.

    Как опознать


    -> *
       && (shared hit / 8K) + (shared read / 1K) < time / 1000
          -- RAM hit = 64MB/s, HDD read = 8MB/s
       && time > 100ms -- читали мало, но слишком долго
    

    Рекомендации


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


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

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

      0

      А вот ментата надо было харконненовского брать, он колоритнее.

        0
        Он как-то слишком уж довольно ухмыляется и потирает лапки — как бы чего недоброго не натворил…

        0
        А эта штука может быть расширена на другие СУБД? Хотя бы в принципе.
          0
          Алгоритмически — да, для любой СУБД, позволяющей получить план.
          Просто нужен абсолютно другой парсер плана и другой набор подсказок == много-много экспертизы.
            0
            Ну да, я догадываюсь, что это нетривиально чисто технически. Т.е. парсим план во что-то типа дерева, потом ищем там паттерны, и даем на основе этого советы.
              0
              Ну постгрес умеет план в JSON возвращать, а оракл если я помню в XML…
          0
          Да, думаю что может. Одно время я занимался оптимизацией и улучшением производительности запросов в MS SQL (2008 — 2012 версий). В том числе через донастройку индексов. Описанное в статье выглядит очень знакомым и, в общем смысле, применимо к MS SQL. Вплоть до ситуации с WHERE… OR и ее решением через UNION — частый случай у нас был, хотя и в более изощренной форме.
          0
          Понимать, что такое план запроса — это очень важно, большая «О» и понимание, что там проиходит внутри базы — вообще бесценно.

          Но, увы, далеко не все вникают так глубоко в делали.

          У меня огромная просьба, когда вы решаете проблему медленного SELECT созданием очередного индекса — пишите там же, как это изменение повлиет на операции INSERT\UPDATE\DELETE.
          Спасибо
            0
            У меня огромная просьба, когда вы решаете проблему медленного SELECT созданием очередного индекса — пишите там же, как это изменение повлиет на операции INSERT\UPDATE\DELETE.
            Если в вашей базе сильно доминирует чтение, скажем 1000:1 (а это почти всегда так, за исключением очень редких случаев типа мониторинга), то вы +1 индекс заметите только с точки зрения объема БД, на пишущие операции оно повлияет микроскопически.
              0
              Если в вашей базе сильно доминирует чтение, скажем 1000:1 (а это почти всегда так, за исключением очень редких случаев

              не согласен, у меня случаи "нужно писать постоянный поток" сплошь и рядом

            0
            CREATE INDEX ON tbl(fk_cli, pk DESC);

            зачем тут desc в индексе? он нужен достаточно редко (в живых, а не учебных базах сходу и не вспоминается такой случай)

              0
              Это исключительно привычка — иметь индекс соответствующий прикладным запросам, чтобы не возникало в плане Index Scan Backward.
                0
                чтобы не возникало в плане Index Scan Backward

                оно разве чем-то плохо?
                ЕМНИП такой индекс нужен разве что для запросов вроде where fk_cli beween 1000 and 2000 order by fk_cli, pk desc (отбор по индексу + сортировка по нескольким полям индекса)

                  0
                  Для такого условия индекс будет работать плохо. И DESC на это не повлияет все равно.
                    0

                    с чего бы это для условия A between 1000 and 2000 индекс (A, B) будет работать плохо?!?

                      0
                      Для этого условия он будет работать нормально. А вот необходимость 1000 раз заглянуть на уровень ниже и взять «последний» pk резко добавляет нагрузки.
                      Вот если бы order by fk_cli, pk desc limit 1000 без условия на fk_cli… Но это уже совсем другой запрос.
                        0
                        заглянуть на уровень ниже и взять «последний» pk

                        откуда взялся последний pk?

                          0
                          Из order by же. Как иначе мы сможем получить сортировку по паре ключей?
                            0

                            я вас не понимаю.
                            вот смотрите, пусть у нас есть миллион fk_cli, каждому соответсвует 10 pk.
                            нам надо выбрать примерно 10к записей, где fk_cli в отсортированы в прямом порядке, pk — в обратном (или наоборот, не важно).


                            в этом случае индекс (fk_cli, pk desc) будет работать идеально, ничего более оптимального нет (индекс даст и нужный диапазон, и нужную сортировку)


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

                              0
                              Посмотрите мою статью по ссылке выше. При BETWEEN-условии с сортировкой получается ровно вот этот эффект:

                                0

                                хорошо, каким способом можно выполнить этот (или экививалентный) запрос быстрее?

                                  0
                                  Иногда можно эффективно применить связку unnest + подзапрос/ARRAY.
                                  Но это сильно зависит от условий — например, сколько реальных значений ожидается найти в BETWEEN-диапазоне (насколько он разрежен).
                                    0

                                    иногда можно. но не в этом случае. ничего быстрее фильтра «в лоб» по индексу тут не будет, можете проверить

                                      0
                                      Так я и не утверждал, что «всегда есть способ сделать лучше» — не всегда. А вот «тут индекс будет работать плохо» — правда, он и в тысяче других случаев может плохо работать. :)
                                        0

                                        да с чего вы взяли, что он будет работать плохо?
                                        поиск по дереву, потом последовательный перебор страниц — именно то, для чего btree лучше всего подходит

                                          0
                                          Возьмем генератор датасета отсюда и модель индекса отсюда (разве что он по 3 полям, а не по 2):

                                          -- задачи с указанным распределением
                                          CREATE TABLE task AS
                                          WITH aid AS (
                                            SELECT
                                              id
                                            , array_agg((random() * 999)::integer + 1) aids
                                            FROM
                                              generate_series(1, 1000) id
                                            , generate_series(1, 20)
                                            GROUP BY
                                              1
                                          )
                                          SELECT
                                            *
                                          FROM
                                            (
                                              SELECT
                                                id
                                              , '2020-01-01'::date - (random() * 1e3)::integer task_date
                                              , (random() * 999)::integer + 1 owner_id
                                              FROM
                                                generate_series(1, 100000) id
                                            ) T
                                          , LATERAL(
                                              SELECT
                                                aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
                                              FROM
                                                aid
                                              WHERE
                                                id = T.owner_id
                                              LIMIT 1
                                            ) a;
                                          
                                          CREATE INDEX ON task(owner_id, task_date, id);
                                          
                                          explain (analyze, buffers, costs off)
                                          SELECT
                                            *
                                          FROM
                                            task
                                          WHERE
                                            owner_id = ANY('{1,2,4,8,16,32,64,128,256,512}'::integer[])
                                          ORDER BY
                                            owner_id, task_date, id;

                                          "Index Scan using task_owner_id_task_date_id_idx on task (actual time=0.016..0.808 rows=946 loops=1)"
                                          "  Index Cond: (owner_id = ANY ('{1,2,4,8,16,32,64,128,256,512}'::integer[]))"
                                          "  Buffers: shared hit=974"
                                          

                                          946 полученных записей в результате чтения 974 страниц данных. Это я и имею в виду, когда говорю, что индекс работает «нехорошо» — если для получения одной записи требуется читать 8KB+ данных.

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

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