Тюнинг производительности запросов в PostgreSQL

Автор оригинала: Brady Holt
Настройка производительности базы данных — разработчики обычно либо любят это, либо ненавидят. Я получаю удовольствие от этого и хочу поделиться некоторыми методами, которые я использовал в последнее время для настройки плохо выполняющихся запросов в PostgreSQL. Мои методы не является исчерпывающими, скорее учебником для тех, кто просто тащится от тюнинга.

Поиск медленных запросов


Первый очевидный способ начать тюнинг — это найти конкретные операторы, которые работают плохо.

pg_stats_statements


Модуль pg_stats_statements — отличное место для начала. Он просто отслеживает статистику выполнения операторов SQL и может быть простым способом поиска неэффективных запросов.

Как только вы установили этот модуль, системное представление с именем pg_stat_statements будет доступно со всеми своими свойствами. Как только у него будет возможность собрать достаточный объем данных, ищите запросы, которые имеют относительно высокое значение total_time. Сначала сфокусируйтесь на этих операторах.

SELECT *
FROM
  pg_stat_statements
ORDER BY
  total_time DESC;

user_id dbid queryid query calls total_time
16384 16385 2948 SELECT address_1 FROM addresses a INNER JOIN people p ON a.person_id = p.id WHERE a.state = @state_abbrev; 39483 15224.670
16384 16385 924 SELECT person_id FROM people WHERE name = name; 26483 12225.670
16384 16385 395 SELECT _ FROM orders WHERE EXISTS (select _ from products where is_featured = true) 18583 224.67


auto_explain


Модуль auto_explain также полезен для поиска медленных запросов, но имеет 2 явных преимущества: он регистрирует фактический план выполнения и поддерживает запись вложенных операторов с помощью опции log_nested_statements. Вложенные операторы — это те операторы, которые выполняются внутри функции. Если ваше приложение использует много функций, auto_explain неоценим для получения подробных планов выполнения.

Опция log_min_duration контролирует, какие планы выполнения запросов регистрируются, основываясь на том, как долго они выполняются. Например, если вы установите значение 1000, все записи, которые выполняются дольше 1 секунды, будут зарегистрированы.

Тюнинг индексов


Другой важной стратегией настройки является обеспечение правильного использования индексов. В качестве предварительного условия нам нужно включить Cборщик Cтатистики (Statistics Collector).

Postgres Statistics Collector — это подсистема первого класса, которая собирает все виды полезной статистики производительности.

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

Отсутствующие индексы


Отсутствующие индексы может быть одним из самых простых решений для повышения производительности запросов. Однако они не являются серебряной пулей и должны использоваться правильно (подробнее об этом позже). Если у вас включен сборщик статистики, вы можете выполнить следующий запрос (источник).

SELECT
  relname,
  seq_scan - idx_scan AS too_much_seq,
  CASE
    WHEN
      seq_scan - coalesce(idx_scan, 0) > 0
    THEN
      'Missing Index?'
    ELSE
      'OK'
  END,
  pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan
FROM
  pg_stat_all_tables
WHERE
  schemaname = 'public'
  AND pg_relation_size(relname::regclass) > 80000
ORDER BY
  too_much_seq DESC;

Запрос находит таблицы, в которых было больше последовательных сканирований (Sequential Scans), чем индексных сканирований (Index Scans) — явный признак того, что индекс поможет. Это не скажет вам, по каким столбцам создать индекс, так что потребуется немного больше работы. Однако, знание, какие таблицы нуждаются в них, это хороший первый шаг.

Неиспользуемые индексы


Индексируйте все сущности, правильно? Знаете ли вы, что неиспользуемые индексы могут негативно повлиять на производительность записи? Причина в том, что при создании индекса Postgres обременен задачей обновления этого индекса после операций записи (INSERT / UPDATE / DELETE). Таким образом, добавление индекса является уравновешивающим действием, поскольку оно может ускорить чтение данных (если оно создано правильно), но замедлит операции записи. Чтобы найти неиспользуемые индексы, вы можете выполнить следующий запрос.

SELECT
  indexrelid::regclass as index,
  relid::regclass as table,
  'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement
FROM
  pg_stat_user_indexes
  JOIN
    pg_index USING (indexrelid)
WHERE
  idx_scan = 0
  AND indisunique is false;

Примечание о статистике сред разработки


Полагаться на статистику, полученную из локальной базы данных разработки, может быть проблематично. В идеале вы можете получить приведенную выше статистику с вашей рабочей машины или сгенерировать ее из восстановленной рабочей резервной копии. Зачем? Факторы окружения могут изменить работу оптимизатора запросов Postgres. Два примера:

  • когда у машины меньше памяти, PostgreSQL может быть не в состоянии выполнить Hash Join, в противном случае он сможет и сделает это быстрее.
  • если в таблице не так много строк (как в базе данных разработки), PostgresSQL может предпочесть выполнять последовательное сканирование таблицы, а не использовать доступный индекс. Когда размеры таблиц невелики, Seq Scan может быть быстрее. (Примечание: вы можете запустить
    SET enable_seqscan = OFF
    в сеансе, чтобы оптимизатор предпочел использовать индексы, даже если последовательное сканирование может быть быстрее. Это полезно при работе с базами данных разработки, в которых нет большого количества данных)

Понимание планов выполнения


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

EXPLAIN


Команда EXPLAIN, безусловно, обязательна при настройке запросов. Он говорит вам, что на самом деле происходит. Чтобы использовать его, просто добавьте к запросу EXPLAIN и запустите его. PostgreSQL покажет вам план выполнения, который он использовал.

При использовании EXPLAIN для настройки, я рекомендую всегда использовать опцию ANALYZE (EXPLAIN ANALYZE), поскольку она дает вам более точные результаты. Опция ANALYZE фактически выполняет оператор (а не просто оценивает его), а затем объясняет его.

Давайте окунемся и начнем понимать вывод EXPLAIN. Вот пример:



Узлы


Первое, что нужно понять, это то, что каждый блок с отступом с предшествующим «->» (вместе с верхней строкой) называется узлом. Узел — это логическая единица работы («шаг», если хотите) со связанной стоимостью и временем выполнения. Стоимость и время, представленные на каждом узле, являются совокупными и сводят все дочерние узлы. Это означает, что самая верхняя строка (узел) показывает совокупную стоимость и фактическое время для всего оператора. Это важно, потому что вы можете легко детализировать для определения, какие узлы являются узким местом.

Стоимость


cost=146.63..148.65

Первое число — это начальные затраты (затраты на получение первой записи), а второе число — это затраты на обработку всего узла (общие затраты от начала до конца).

Фактически, это стоимость, которую, по оценкам PostgreSQL, придется выполнить для выполнения оператора. Это число не означает сколько времени потребуется для выполения запроса, хотя обычно существует прямая зависимость, необходимого для выполнения. Стоимость — это комбинация из 5 рабочих компонентов, используемых для оценки требуемой работы: последовательная выборка, непоследовательная (случайная) выборка, обработка строки, оператор (функция) обработки и запись индекса обработки. Стоимость представляет собой операции ввода-вывода и загрузки процессора, и здесь важно знать, что относительно высокая стоимость означает, что PostgresSQL считает, что ему придется выполнять больше работы. Оптимизатор принимает решение о том, какой план выполнения использовать, исходя из стоимости. Оптимизатор предпочитает более низкие затраты.

Фактическое время


actual time=55.009..55.012

В миллисекундах первое число — это время запуска (время для извлечения первой записи), а второе число — это время, необходимое для обработки всего узла (общее время от начала до конца). Легко понять, верно?

В приведенном выше примере потребовалось 55,009 мс для получения первой записи и 55,012 мс для завершения всего узла.

Узнать больше о планах выполнения


Есть несколько действительно хороших статей для понимания результатов EXPLAIN. Вместо того, чтобы пытаться пересказать их здесь, я рекомендую потратить время на то, чтобы по-настоящему понять их, перейдя к этим 2 замечательным ресурсам:


Настройка запросов


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

Заметка о кеше данных и издержках


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

Вещи, которые я узнал, могут помочь улучшить планы выполнения:

  • Индексы
    • Исключите последовательное сканирование (Seq Scan), добавив индексы (если размер таблицы не мал)
    • При использовании многоколоночного индекса убедитесь, что вы обращаете внимание на порядок, в котором вы определяете включенные столбцы — Дополнительная информация
    • Попробуйте использовать индексы, которые очень избирательны к часто используемым данным. Это сделает их использование более эффективным.
  • Условие ГДЕ

    • Избегайте LIKE
    • Избегайте вызовов функций в условии WHERE
    • Избегайте больших условий IN()
  • JOINы

    • При объединении таблиц попробуйте использовать простое выражение равенства в предложении ON (т.е. a.id = b.person_id). Это позволяет использовать более эффективные методы объединения (т. Е. Hash Join, а не Nested Loop Join)
    • Преобразуйте подзапросы в операторы JOIN, когда это возможно, поскольку это обычно позволяет оптимизатору понять цель и, возможно, выбрать лучший план.
    • Правильно используйте СОЕДИНЕНИЯ: используете ли вы GROUP BY или DISTINCT только потому, что получаете дублирующиеся результаты? Это обычно указывает на неправильное использование JOIN и может привести к более высоким затратам
    • Если план выполнения использует Hash Join, он может быть очень медленным, если оценки размера таблицы неверны. Поэтому убедитесь, что статистика вашей таблицы точна, пересмотрев стратегию очистки (vacuuming strategy )
    • По возможности избегайте коррелированных подзапросов; они могут значительно увеличить стоимость запроса
    • Используйте EXISTS при проверке существования строк на основе критерия, поскольку он подобен короткому замыканию (останавливает обработку, когда находит хотя бы одно совпадение)
  • Общие рекомендации

    • Делайте больше с меньшими затратами; Процессор быстрее чем операции ввода/вывода (I/O)
    • Используйте Common Table Expressions и временные таблицы, когда вам нужно выполнить цепочечные запросы.
    • Избегайте операторов LOOP и предпочитайте операции SET
    • Избегайте COUNT (*), поскольку PostgresSQL для этого выполняет сканирование таблиц (только для версий <= 9.1)
    • По возможности избегайте ORDER BY, DISTINCT, GROUP BY, UNION, поскольку это приводит к высоким начальным затратам
    • Ищите большую разницу между оценочными и фактическими строками в выражении EXPLAIN. Если счетчик сильно отличается, статистика таблицы может быть устаревшей, а PostgreSQL оценивает стоимость с использованием неточной статистики. Например:
      Limit (cost=282.37..302.01 rows=93 width=22) (actual time=34.35..49.59 rows=2203 loops=1)
      Расчетное количество строк составило 93, а фактическое — 2203. Поэтому, скорее всего, это плохое решение плана. Вы должны пересмотреть свою стратегию очистки (vacuuming strategy) и убедиться, что ANALYZE выполняется достаточно часто.

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

    +1

    Для тех, кто тоже не сразу врубился в перевод:


    Стоимость — это комбинация из 5 рабочих компонентов, используемых для оценки требуемой работы: последовательная выборка, непоследовательная (случайная) выборка, обработка строки, оператор (функция) обработки и запись индекса обработки.

    Тут речь о том, что есть пять параметров конфигурации, которые определяют стоимость отдельных операций: последовательной выборки (seq_page_cost), случайной выборки (random_page_cost), обработки табличной строки (cpu_tuple_cost), выполнения операции (cpu_operator_cost) и обработки индексной строки (cpu_index_tuple_cost). Итоговая стоимость любого узла плана оценивается как комбинация из энного количества этих примитивных операций.

      0
      Спасибо за разъяснение!
      0
      Спасибо за отсутствующие индексы, взял на вооружение.

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

      Мой вариант запроса
      with forein_key_indexes as (
        select i.indexrelid
          from pg_constraint c
          join lateral unnest(c.conkey) with ordinality as u(attnum, attposition) on true
          join pg_index i on i.indrelid = c.conrelid and (c.conkey::int[] <@ indkey::int[])
          where c.contype = 'f'
      )
      select
        psui.relname as table_name,
        psui.indexrelname as index_name,
        pg_relation_size(i.indexrelid) as index_size,
        pg_size_pretty(pg_relation_size(i.indexrelid)) as index_size_pretty,
        psui.idx_scan as index_scans
      from pg_stat_user_indexes psui
        join pg_index i on psui.indexrelid = i.indexrelid
      where
            psui.schemaname = 'public'::text and
            not i.indisunique and
            i.indexrelid not in (select * from forein_key_indexes) and -- retain indexes on foreign keys
            psui.idx_scan < 50 and
            pg_relation_size(psui.relid) >= 5 * 8192 -- skip small tables
      	  and pg_relation_size(psui.indexrelid) >= 5 * 8192 -- skip small indexes
      order by psui.relname, pg_relation_size(i.indexrelid) desc
      

        0
        Используйте Common Table Expressions и временные таблицы, когда вам нужно выполнить цепочечные запросы.

        Только не забывайте, что до версии PG 12, CTE материализуются.

          0
          Оптимизирую запросы при помощи профилировщика, входящего в состав dbForge Studio for PostgreSQL и не знаю забот. Во-первых, оптимизация происходит в визуальном режиме, все что требуется от меня — жмакнуть кнопку, после чего получаю исчерпывающую статистику по запросу, включая детальный план волнения с процентным соотношением траты ресуров каждым компонентом запроса. Запрос можно также модифицировать прямо в профилировщике и сравнивать результаты. Рекомендую тулзу.
            0

            Эта "студия" только под винду, нет исходников, и конечно же хочет денег.

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

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