Медленные запросы PostgreSQL: поиск через pg_stat_statements

pg_stat_statements - расширение PostgreSQL, которое хранит одну строку накопленных итогов для каждого уникального запроса, выполненного в вашей базе: сколько раз он запускался, сколько времени занял в сумме, сколько строк вернул и сколько буферов затронул. Вы сортируете это представление по общему времени, берёте пять первых строк - и получаете список запросов, которым стоит уделить внимание. Это самое полезное представление для работы над производительностью, потому что оно отвечает на вопрос, на который не ответит угадывание: какие запросы на самом деле обходятся этому серверу дороже всего. Чтобы его включить, нужны библиотека, загружаемая при старте сервера, один CREATE EXTENSION и около мегабайта общей памяти. В этом руководстве разобраны настройка, все столбцы, которые стоит читать, запросы для запуска и то, как передать найденное в EXPLAIN.

Что записывает pg_stat_statements, а что нет#

Каждый раз, когда инструкция завершается, PostgreSQL убирает из неё литеральные значения, хеширует остаток в queryid и добавляет данные о времени и вводе-выводе к этой записи. Два запроса, отличающиеся только параметрами, попадают в одну строку:

SELECT * FROM orders WHERE customer_id = 42;SELECT * FROM orders WHERE customer_id = 9001;

Оба становятся одной записью, хранящейся как SELECT * FROM orders WHERE customer_id = $1. Это нормализация запросов, и именно поэтому представление полезно: тысячи вариаций из приложения сворачиваются в немногие формы запросов, которые реально есть в вашем коде.

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

Прежде чем на него полагаться, стоит знать три ограничения:

• Параметры не сохраняются. Вы видите $1, но не то значение, на котором запрос был медленным. Для конкретного значения используйте журнал (log_min_duration_statement) или auto_explain.
• Считается время выполнения, а не ожидание в вашем приложении. Запрос, который выполняется 3 мс, но стоит в очереди пула соединений 400 мс, здесь выглядит быстрым. Об этой стороне читайте в статье connection pools and limits.
• Число записей ограничено. По умолчанию отслеживается 5000 уникальных инструкций; когда таблица заполнена, наименее используемые записи вытесняются. Большинство небольших приложений до этого не доходят, но приложение, которое собирает SQL с меняющейся формой, может дойти.

Проверьте, не установлено ли оно уже#

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

SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name = 'pg_stat_statements';SELECT extname, extversion FROM pg_extension;SHOW shared_preload_libraries;

Читайте три результата вместе:

Последняя строка - та, что удивляет людей. CREATE EXTENSION может пройти успешно и без загруженной библиотеки, а потом выборка из представления падает с ошибкой pg_stat_statements must be loaded via "shared_preload_libraries". Расширение создаётся для каждой базы отдельно, но как только оно собирает данные, представление в любой базе показывает инструкции из всех баз экземпляра, а столбец dbid помогает их различать.

Можно ли менять shared_preload_libraries, целиком зависит от того, где работает ваша база. На сервере, которым вы управляете сами, можно. В хостинговой базе параметр может быть задан за вас, может меняться только провайдером или вообще не быть доступным. Нигде не заявлено, что тарифы PostgreSQL в RE:NODE это позволяют, поэтому надёжный путь - проверка выше: если расширения нет, откройте тикет из панели и спросите поддержку, можно ли включить его на вашем экземпляре. Не предполагайте ни один из ответов. Всё, что идёт после раздела о настройке, работает на любом хостинге, где представление существует.

Включение на сервере, которым вы управляете#

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

Сначала добавьте её в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max = 5000pg_stat_statements.track = topcompute_query_id = autotrack_io_timing = on

Если в строке уже перечислены другие библиотеки, оставьте их и разделяйте запятыми. Перезапись существующего shared_preload_libraries - классический способ отключить что-то ещё, что вам было нужно. Эквивалент без ручной правки файла - ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';, он записывает значение в postgresql.auto.conf.

Затем перезапустите сервер. Для этого параметра перезагрузки конфигурации (SELECT pg_reload_conf();) недостаточно. Планируйте это на спокойное время: перезапуск обрывает все соединения, а в статье PostgreSQL tuning for small servers перечислено, какие ещё параметры требуют того же, так что их можно собрать в один перезапуск.

Наконец, создайте расширение в той базе, из которой будете делать запросы:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Для этого нужна роль, которой разрешено создавать расширения; на самостоятельно управляемом сервере это суперпользователь. Убедитесь, что всё работает:

SELECT count(*) FROM pg_stat_statements;

Число, даже небольшое, означает, что сбор идёт. Если вы получили ошибку «must be loaded», значит, перезапуск не подхватил параметр; выполните SHOW shared_preload_libraries;, чтобы увидеть, что сервер загрузил на самом деле.

После обновления до новой основной версии выполните ALTER EXTENSION pg_stat_statements UPDATE; в каждой базе, где оно есть. Столбцы представления меняются между версиями, и старое определение сохраняет старые столбцы, пока вы этого не сделаете.

Важные параметры#

Расширением управляют четыре параметра. Перезапуск нужен только для max.

top подходит почти всем. Переключайтесь на all только тогда, когда под подозрением медленная хранимая функция и вы хотите увидеть, какая инструкция внутри неё виновата; цена - больше записей и больше шума. Есть также pg_stat_statements.track_planning, по умолчанию он off, потому что измерение времени планирования добавляет накладные расходы на загруженных серверах. Включите его ненадолго, если подозреваете, что проблема во времени планирования, например при очень широких секционированных таблицах.

Два параметра вне расширения определяют, насколько полезны цифры:

• `compute_query_id` (PostgreSQL 14 и новее) по умолчанию равен auto, что включает идентификаторы запросов, когда загружен pg_stat_statements. Оставьте так. Тот же идентификатор появляется в pg_stat_activity.query_id и в EXPLAIN (VERBOSE), что позволяет связать выполняющийся запрос с его историей.
• `track_io_timing` по умолчанию off. Пока он выключен, столбцы времени чтения и записи блоков остаются нулевыми, и вы не можете понять, медленный ли запрос из-за диска. На современном оборудовании накладные расходы малы; pg_test_timing измерит их для вашей машины. Включите, если можете.

И последнее замечание о памяти: таблица небольшая. Пять тысяч записей - это самое большее несколько мегабайт, а тексты запросов лежат в файле на диске. На сервере с 1 GB вы этого не заметите.

Столбцы и ловушка версий#

Выполните \d pg_stat_statements в psql, чтобы увидеть точный список для вашей версии. Те, которыми вы будете пользоваться каждый день:

И столбцы ввода-вывода, которые показывают, ушло ли время на память или на диск:

Несколько столбцов требуют пояснения. shared_blks_read - не обязательно диск: это блоки, которых не оказалось в собственных буферах PostgreSQL, а кэш операционной системы мог их всё же отдать. Тем не менее большое значение означает, что запрос затрагивает больше данных, чем помещается в shared_buffers, и это сигнал посмотреть на план. temp_blks_written больше нуля означает, что сортировке или хешированию не хватило work_mem и они писали на диск, а это лечится иначе, чем отсутствующий индекс. В PostgreSQL 17 и новее столбцы времени ввода-вывода получили префикс shared_, например shared_blk_read_time; та же проверка через \d покажет, какой вариант у вас.

Три запроса «топ N»#

Всё сказанное выше нужно ради этих запросов. Одно и то же представление, отсортированное тремя способами, отвечает на три разных вопроса.

По общему времени: на что сервер тратит свой день

SELECT queryid,       calls,       round(total_exec_time::numeric, 1)                      AS total_ms,       round(mean_exec_time::numeric, 2)                       AS mean_ms,       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,       rows,       left(query, 80)                                         AS queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

Этот запрос стоит запускать первым и чаще всего. Столбец pct показывает долю каждой инструкции во всём времени выполнения; в типичном приложении три-четыре записи составляют большую его часть. Запрос, который занимает 4 мс, но выполняется два миллиона раз в день, то есть 8000 секунд в сумме, обходится дороже девятисекундного отчёта, который запускается раз в ночь. Его часто и исправить проще, потому что обычно нужен один индекс или на одно обращение меньше.

По среднему времени: отдельные медленные запросы

SELECT queryid,       calls,       round(mean_exec_time::numeric, 2)   AS mean_ms,       round(stddev_exec_time::numeric, 2) AS stddev_ms,       left(query, 80)                     AS queryFROM pg_stat_statementsWHERE calls >= 20ORDER BY mean_exec_time DESCLIMIT 10;

Фильтр calls >= 20 важен. Без него разовый CREATE INDEX или ручной VACUUM оказывается на вершине списка и ничему вас не учит. Такая сортировка находит запросы, которые чувствует пользователь: страницу отчёта, экспорт, поиск с подстановкой в начале. Смотрите на stddev_ms рядом со средним. Среднее 50 мс при стандартном отклонении 400 мс - это не медленный запрос, а запрос с двумя режимами работы, часто потому, что планировщик выбирает разные планы в зависимости от параметра. Этим лучше заниматься через auto_explain, о нём ниже.

По числу вызовов: «разговорчивые» запросы

SELECT queryid,       calls,       round(mean_exec_time::numeric, 3) AS mean_ms,       rows / calls                      AS rows_per_call,       left(query, 80)                   AS queryFROM pg_stat_statementsORDER BY calls DESCLIMIT 10;

Запрос с миллионами вызовов и крошечным средним временем - характерный признак шаблона N+1: приложение получает список, а потом выполняет по одному запросу на каждый элемент. Никакой индекс это не исправит; поможет соединение (join) или пакетный WHERE id = ANY($1). Столбец rows_per_call помогает: инструкция, которая возвращает ровно одну строку и вызывается миллион раз, ищет данные по одному.

По промахам кэша и временным файлам

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

SELECT queryid,       shared_blks_hit,       shared_blks_read,       round(100.0 * shared_blks_hit             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,       left(query, 70) AS queryFROM pg_stat_statementsORDER BY shared_blks_read DESCLIMIT 10;

Вторая, ORDER BY temp_blks_written DESC, перечисляет запросы, которые сбрасывают сортировки на диск. Ни одна из них сама по себе не срочна. Процент попаданий в высоких девяностых - норма; часто вызываемая инструкция с 70 процентами гоняет ваш кэш и вытесняет данные, нужные другим запросам.

Измерение за окно времени и сброс#

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

SELECT pg_stat_statements_reset();

В PostgreSQL 12 и новее функция также принимает аргументы userid, dbid и queryid, так что можно очистить одну инструкцию после исправления и посмотреть, как она себя ведёт. По умолчанию вызвать её может только суперпользователь, и действие необратимо, поэтому сначала запишите то, что вам нужно. Дешёвый способ сохранять историю без дополнительных инструментов - таблица срезов:

CREATE TABLE stmt_snapshot ASSELECT now() AS taken_at, queryid, calls, total_exec_time, rows, queryFROM pg_stat_statements;-- later: INSERT INTO stmt_snapshot SELECT now(), queryid, calls, ... FROM pg_stat_statements;

Вычтите calls и total_exec_time одного среза из следующего - получите стоимость между ними. Запускайте это по расписанию, например из cron или pg_cron каждый час, и у вас будет неделя данных о динамике. Не давайте таблице разрастаться, удаляя старые строки, а текст запроса достаточно хранить один раз, если держать его в отдельной таблице по queryid.

Если сервер упал или процесс был убит, pg_stat_statements.save не поможет: счётчики потеряны, потому что записываются только при штатном завершении. Относитесь к представлению как к окну недавней активности, а не как к постоянной записи.

От queryid к исправлению: EXPLAIN (ANALYZE, BUFFERS)#

Представление говорит, на какой запрос смотреть. Почему он медленный, оно сказать не может. Скопируйте нормализованный текст, подставьте реалистичное значение вместо каждого $1 и выполните его под EXPLAIN с параметрами, которые показывают ввод-вывод:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT * FROM ordersWHERE customer_id = 42 AND status = 'open'ORDER BY created_at DESCLIMIT 20;

ANALYZE выполняет инструкцию по-настоящему, поэтому всё, что пишет данные, оборачивайте в транзакцию, которую откатите: BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;. BUFFERS добавляет строки Buffers: shared hit=... read=..., которые можно сравнить со столбцами shared_blks_hit и shared_blks_read из представления. Если представление говорит, что этот запрос читает 40 000 блоков за вызов, в плане должен найтись узел, который это объясняет, обычно последовательное сканирование или сортировка. Полный словарь есть в статье EXPLAIN ANALYZE: reading Postgres query plans; эта же статья лишь приводит вас к нужному запросу.

Используйте значения, соответствующие боевым. План для customer_id = 1 может отличаться от плана для клиента с двумя миллионами строк, потому что планировщик, опираясь на статистику, выберет по-разному. Обычные исправления следуют из того, что показывает план:

• Последовательное сканирование большой таблицы с избирательным фильтром указывает на отсутствующий индекс; порядок столбцов и частичные индексы разобраны в статье PostgreSQL indexes: types, order and cost.
• Очень большое значение Rows Removed by Filter значит, что индекс есть, но не подходит под фильтр.
• Оценка, сильно отличающаяся от фактического числа строк, означает устаревшую статистику: выполните ANALYZE tablename;.
• Таблица, выросшая во много раз сверх нужного размера при отстающем vacuum, замедляет каждое сканирование; см. Postgres vacuum, bloat and autovacuum.

После изменения сбросьте этот один queryid (или сначала сделайте срез), дайте трафику поработать и сравните mean_exec_time. Исправление, которое вы потом не измерили в pg_stat_statements, - это догадка.

Живые запросы: pg_stat_activity#

pg_stat_statements - это история. Для того, что происходит прямо сейчас, например зависшей страницы или скачка числа соединений, используйте pg_stat_activity:

SELECT pid,       usename,       state,       wait_event_type,       wait_event,       now() - query_start AS runtime,       left(query, 80)     AS queryFROM pg_stat_activityWHERE state <> 'idle'  AND pid <> pg_backend_pid()ORDER BY runtime DESC;

Первым делом читайте столбец state. active - выполняется сейчас; idle in transaction означает, что клиент открыл транзакцию и ничего не делает, при этом держит блокировки и не даёт vacuum очищать данные. Длинный список сеансов idle in transaction - это ошибка приложения, а не базы. wait_event_type со значением Lock означает, что запрос стоит в очереди за другим, а pg_blocking_pids(pid) возвращает, кто его держит.

Чтобы остановить зациклившийся запрос, SELECT pg_cancel_backend(pid); отменяет текущий запрос и сохраняет соединение; pg_terminate_backend(pid) завершает весь сеанс. Сначала пробуйте первый вариант. Начиная с PostgreSQL 14, pg_stat_activity.query_id совпадает с pg_stat_statements.queryid, так что можно увидеть, как выполняющийся сейчас запрос вёл себя раньше:

SELECT a.pid, now() - a.query_start AS runtime, s.calls, s.mean_exec_timeFROM pg_stat_activity aJOIN pg_stat_statements s ON s.queryid = a.query_idWHERE a.state = 'active';

Учтите, что pg_stat_activity.query обрезается по track_activity_query_size, по умолчанию 1024 байта. Сгенерированный запрос с огромным списком в IN будет обрезан именно там, а чтобы увеличить значение, нужен перезапуск.

Дополнения: log_min_duration_statement и auto_explain#

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

`log_min_duration_statement` записывает в журнал сервера каждую инструкцию медленнее порога, включая реальные значения параметров:

ALTER SYSTEM SET log_min_duration_statement = '500ms';SELECT pg_reload_conf();

Здесь достаточно перезагрузки конфигурации, перезапуск не нужен. Это ничего не стоит, пока нет медленных запросов, и отвечает на вопрос, на который представление не отвечает: какое именно значение сделало запрос медленным и в какое время. Используйте 0 только ненадолго, поскольку в журнал попадёт всё. Если журнал доступен вам только через консоль на хостинге, умеренный порог вроде 500 мс или 1 с сохранит его читаемым.

`auto_explain` идёт на шаг дальше и записывает в журнал сам план медленных инструкций, что ловит план, который портится лишь изредка. Он загружается так же, как pg_stat_statements, через shared_preload_libraries (или session_preload_libraries), и настраивается так:

shared_preload_libraries = 'pg_stat_statements,auto_explain'auto_explain.log_min_duration = '1s'auto_explain.log_analyze = offauto_explain.log_buffers = offauto_explain.log_nested_statements = off

Держите log_analyze выключенным, пока вам не нужны фактические замеры времени: включение инструментирует каждую инструкцию, в том числе быстрые, и может заметно замедлить загруженную базу. Суперпользователь может также выполнить LOAD 'auto_explain'; и задать параметры для одного сеанса, чтобы проверить без перезапуска. Правило выбора такое: pg_stat_statements говорит, на что смотреть, log_min_duration_statement говорит, когда и с какими значениями, auto_explain говорит, какой план был выбран. Пользователи MySQL узнают средний вариант по журналу медленных запросов MySQL.

Замечание о том, как поддерживать порядок в качестве привычки: раз в неделю берите пять первых по общему времени и пять первых по среднему времени при calls >= 20. Если через месяц в списке те же имена, исправление не сработало. Статья Monitoring that tells you something о том, как сделать такие цифры тем, на что вы действительно смотрите.

Привилегии: кто что видит#

По умолчанию роль видит полный текст и статистику только своих инструкций. Для строк других пользователей pg_stat_statements показывает статистику, но заменяет текст на <insufficient privilege> и скрывает столбец queryid. Суперпользователи видят всё. Всем остальным чистое решение даёт встроенная роль pg_read_all_stats, доступная начиная с PostgreSQL 10:

GRANT pg_read_all_stats TO monitoring_user;

Она даёт доступ на чтение к статистическим представлениям, включая pg_stat_statements и запросы других сеансов в pg_stat_activity, не давая доступа к данным, так что это правильная выдача прав для дашборда или инженера, которому не нужно читать содержимое таблиц. Сброс - отдельный вопрос: pg_stat_statements_reset() может выполнить только суперпользователь, если вы не сделали GRANT EXECUTE на неё, а CREATE EXTENSION тоже требует достаточных прав. Как правильно построить такую роль, рассказано в статье PostgreSQL roles and permissions.

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

Использование в хостинговой базе#

В управляемой или хостинговой службе PostgreSQL раздел о настройке может к вам не относиться. Обычно встречаются три ситуации: расширение уже включено, и вам нужны только запросы выше; расширение доступно и ему нужен только CREATE EXTENSION, который обычному владельцу базы могут разрешить или не разрешить; либо конфигурация на уровне сервера принадлежит провайдеру, и вы не можете её трогать.

Для линейки PostgreSQL в RE:NODE, которая начинается от $6 в месяц, документально подтверждено, что учётные данные создаются для каждого сервера, а подключаться нужно к хосту и порту вашего тарифа. Нигде не сказано, можно ли редактировать shared_preload_libraries и какие расширения доступны. Поэтому практический порядок такой:

1. Подключитесь через psql и выполните три проверки из раздела выше.
2. Если pg_stat_statements установлено и представление отвечает, используйте его, как описано.
3. Если нет, обратитесь в поддержку тикетом из панели, назвав расширение и то, зачем оно вам. Можно ли включить его на вашем экземпляре, решать им.
4. Тем временем pg_stat_activity и накопительные представления вроде pg_stat_user_tables работают без всякой настройки, а EXPLAIN (ANALYZE, BUFFERS) не требует ничего вообще.

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

FAQ#

Замедляет ли pg_stat_statements базу данных?

Незначительно. Цена - небольшие фиксированные накладные расходы на каждую инструкцию на хеширование и обновление общих счётчиков, которые трудно измерить на большинстве нагрузок. Более тяжёлые параметры - track_planning и auto_explain.log_analyze, поэтому держите их выключенными, пока они не понадобятся. Постоянная работа pg_stat_statements в боевой среде - обычная практика.

Почему в тексте запроса вместо значения стоит $1?

Потому что расширение нормализует инструкции, чтобы вариации одного запроса попадали в одну строку. Значения намеренно не сохраняются. Чтобы найти значение, на котором запрос был медленным, задайте log_min_duration_statement или используйте auto_explain. Приложения, которые подставляют значения прямо в текст, а не передают их как параметры привязки, всё равно нормализуются; удаление происходит внутри сервера.

Почему представление пусто или выдаёт ошибку после CREATE EXTENSION?

Либо библиотеки нет в shared_preload_libraries, либо сервер не перезапускали после её добавления. SHOW shared_preload_libraries; показывает, что загружено сейчас. Сообщение об ошибке pg_stat_statements must be loaded via "shared_preload_libraries" означает именно это. Пустое представление при загруженном расширении просто значит, что после последнего сброса ничего не выполнялось.

Сортировать по общему времени или по среднему?

Начните с общего времени: оно показывает, что обходится серверу дороже всего в целом и где исправление окупится. Среднее время, с фильтром calls >= 20, используйте, когда жалоба в том, что медленно работает конкретная страница. Они отвечают на разные вопросы, и смотреть вы будете на оба.

Работает ли это в MySQL, MongoDB или SQL Server?

Нет, только в PostgreSQL. В MySQL есть журнал медленных запросов и таблицы дайджестов в performance_schema; в SQL Server есть Query Store, о нём рассказано в статье SQL Server Query Store and slow queries. Идея везде одна: агрегировать по нормализованной инструкции, сортировать по общей стоимости, исправлять верх списка.

Как узнать, какой queryid относится к какой таблице?

Поищите по тексту: WHERE query ILIKE '%orders%'. queryid - хеш разобранной инструкции и сам по себе не несёт сведений о таблицах. Чтобы получить все инструкции к одной таблице за период, отфильтруйте по тексту, просуммируйте total_exec_time и сравните со счётчиками сканирований таблицы в pg_stat_user_tables.