Поиск самых затратных SQL-операторов с помощью pg_stat_statements

Цель

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

Предварительные условия

Модуль в shared_preload_libraries (согласно документации, для его добавления или удаления нужен перезапуск), compute_query_id со значением auto или on и выполненная команда CREATE EXTENSION pg_stat_statements в базе, из которой читается представление. Для просмотра текста запросов других пользователей нужна роль pg_read_all_stats или права суперпользователя.

Шаги

1. Определите окно наблюдения: SELECT stats_reset FROM pg_stat_statements_info. Счётчики накапливаются с этого момента; если окно неизвестно или охватывает развёртывание, выполните SELECT pg_stat_statements_reset() и подождите репрезентативный период.
2. Ранжируйте по суммарному времени: SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20. Быстрый оператор, вызываемый миллионы раз, часто оказывается выше медленного отчёта, на который все жалуются.
3. Отдельно ранжируйте по calls. Очень много вызовов с одной строкой на вызов указывает на построчные циклы в коде приложения, а не на проблему базы данных.
4. Сравните mean_exec_time с max_exec_time. Большой разрыв предполагает планы, зависящие от параметров, или ожидание блокировок, а не стабильно медленный оператор.
5. Изучите столбцы блоков: сравнение shared_blks_read с shared_blks_hit отделяет операторы, ограниченные вводом-выводом, от работающих с кешированными данными; temp_blks_written показывает сброс сортировок и хеширования на диск.
6. Возьмите текст лидирующего оператора, замените заполнители $n реалистичными значениями и продолжите с EXPLAIN (ANALYZE, BUFFERS).
7. После изменения сбросьте статистику, выждите такой же период и сравните те же строки по queryid, а не по тексту запроса.

Ожидаемый результат

Короткий список операторов, на которые приходится основная часть времени выполнения, с указанной причиной для каждого (число вызовов, затраты на вызов или ввод-вывод), а также сравнение до и после по queryid для каждого внесённого изменения.

Ограничения и основание проверки

В документации указано, что представление хранит не более pg_stat_statements.max записей (по умолчанию 5000) и при превышении удаляет наименее часто выполняемые, поэтому редкие операторы могут отсутствовать; что queryid нестабилен между основными версиями и между серверами с логической репликацией; и что счётчики выполнения обновляются только при успешном выполнении. Константы нормализуются, и, согласно документации, запросы, различающиеся только количеством элементов в списке констант, объединяются в одну запись, отображаемую как IN ($1 /*, ... */). Процедура даёт относительное ранжирование; конкретные значения времени не заявляются.