pg_store_plans. Хранение планов запросов
Версия: 1.8.
В исходном дистрибутиве установлено по умолчанию: нет.
Связанные компоненты: отсутствуют.
Схема размещения:
ext.
Расширение pg_store_plans предназначено для накапливания статистики по планам выполнения всех SQL-запросов, выполняемых в БД.
Начиная с PostgreSQL 14 появился единый идентификатор запроса, сгенерированный ядром, и в расширении pg_store_plans он используется. Также появился параметр GUC compute_query_id. Если для compute_query_id установлено значение off, pg_store_plans автоматически отключается. Возможные значения данного параметра:
off- всегда отключено;on- всегда включено;auto(значение по умолчанию) - позволяет таким модулям какpg_stat_statementsавтоматически включить данное вычисление;regress- действует так же, какauto, но идентификатор запроса не показывается в выводе команды EXPLAIN, что облегчает автоматическое регрессионное тестирование.
В рамках работы с расширением доступны представления pg_store_plans и pg_store_plans_info и различные параметры конфигурации.
Доработка
Доработка не проводилась.
Ограничения
Ограничения отсутствуют.
Установка
Для начала использования расширения выполните следующие действия:
-
Пропишите в конфигурационном файле (
postgresql.conf) СУБД Pangolin название расширения в настроечный параметрshared_preload_libraries:shared_preload_libraries = 'pg_store_plans' -
Выполните перезапуск сервера:
systemctl restart postgresql -
Далее расширение нужно установить в целевую БД, для использования собираемой статистики, командой:
CREATE EXTENSION pg_store_plans;
Настройка
Расширение pg_store_plans требует дополнительной разделяемой памяти в объеме, пропорциональном значению pg_store_plans.max. Когда для параметра pg_store_plans.plan_storage установлено значение shmem, ему требуется дополнительная общая память для хранения текстов планов в количестве, равном произведению максимального количества сохраняемых планов (pg_store_plans.max) и максимальной длины отдельного плана (pg_store_plans.max_plan_length). Если для параметра pg_store_plans.plan_storage установлено значение file, тексты плана записываются во временный файл аналогично pg_stat_statements. Если параметр pg_store_plans.max недостаточно велик для хранения всех планов, pg_store_plans освобождает место для новых планов, удаляя некоторую часть записей. После нескольких итераций такого удаления pg_store_plans запускает сборку мусора во временном файле, что может существенно повлиять на нагрузку. Проанализировать данный процесс можно при помощи pg_store_plans_info.dealloc.
Если значение pg_store_plans.max достаточно велико, чтобы сборка мусора не происходила, рекомендуется устанавливать параметр pg_store_plans.plan_storage в значение file.
Эти параметры должны быть установлены в postgresql.conf. Ниже приведен пример настройки:
shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
pg_store_plans.max = 10000
pg_store_plans.track = all
Объекты
Представление pg_store_plans
Для каждого отдельного набора идентификаторов базы данных, идентификатора пользователя и идентификатора запроса предоставляет статистику, собранную расширением.
| Имя | Тип | Описание |
|---|---|---|
dbid | oid | OID пользователя, который выполнил запрос |
queryid | bigint | OID базы данных, в которой был выполнен запрос |
planid | bigint | Идентификатор запроса, сгенерированный ядром или рассчитанный расширением. Позволяет получить текст запроса из расширения pg_stat_statements |
plan | text | Хеш-код плана, вычисляемый на основе нормализованного представления плана |
calls | bigint | Текст плана запроса. Формат задается параметром конфигурации pg_store_plans.plan_format |
total_time | double precision | Количество выполнений запросов с данным планом |
min_time | double precision | Общее время, затраченное на выполнение запросов с использованием плана, в миллисекундах |
max_time | double precision | Минимальное время, затраченное на выполнение запроса с использованием плана, в миллисекундах |
mean_time | double precision | Максимальное время, затраченное на выполнение запроса с использованием плана, в миллисекундах |
stddev_time | double precision | Среднее время, затраченное на выполнение запроса с использованием плана, в миллисекундах |
rows | bigint | Стандартное отклонение времени, затраченное на выполнение запроса с использованием плана, в миллисекундах |
shared_blks_hit | bigint | Общее количество строк, извлеченных или затронутых оператором с использованием плана |
shared_blks_read | bigint | Количество обращений к блокам в общем кеше («попадание» в кеш) с помощью запроса, использующего данный план |
shared_blks_dirtied | bigint | Количество блоков, прочитанных из общего буферного кеша запросом с использованием данного плана |
shared_blks_written | bigint | Общее количество измененных, но не сохраненных запросом файлов сегментов данных («dirty blocks») |
local_blks_hit | bigint | Общее количество блоков, записанных запросом с использованием данного плана |
local_blks_read | bigint | Общее количество обращений к блокам локального кеша («попаданий» в кеш) запроса, использующего данный план |
local_blks_dirtied | bigint | Общее количество блоков локального кеша, измененных, но не сохраненных запросом файлов сегментов данных («dirty blocks») |
local_blks_written | bigint | Общее количество блоков локального кеша, записанных запросом с использованием данного плана |
temp_blks_read | bigint | Общее количество блоков локального кеша, записанных запросом с использованием данного плана |
temp_blks_written | bigint | Общее количество временных блоков, прочитанных запросом с использованием данного плана |
blk_read_time | double precision | Общее время, затраченное запросом, использующим данный план, на чтение блоков, в миллисекундах (если включена опция track_io_timing, в противном случае равно нулю) |
blk_write_time | double precision | Общее время, затраченное запросом, использующим данный план, на запись блоков, в миллисекундах (если включена опция track_io_timing, в противном случае равно нулю) |
temp_blk_read_time | double precision | Общее время, затраченное запросом, использующим данный план, на чтение блоков временных файлов, в миллисекундах (если включена опция track_io_timing, в противном случае равно нулю) |
temp_blk_write_time | double precision | Общее время, затраченное запросом, использующим данный план, на запись блоков временных файлов, в миллисекундах (если включена опция track_io_timing, в противном случае равно нулю) |
first_call | timestamp with time zone | Временная метка для первого вызова запроса с использованием этого плана |
last_call | timestamp with time zone | Временная метка для последнего вызова запроса с использованием этого плана |
DDL представления:
postgres=# \d+ pg_store_plans
View "ext.pg_store_plans"
Column | Type | Collation | Nullable | Default | Storage | Description
---------------------+--------------------------+-----------+----------+---------+----------+-------------
userid | oid | | | | plain |
dbid | oid | | | | plain |
queryid | bigint | | | | plain |
planid | bigint | | | | plain |
plan | text | | | | extended |
calls | bigint | | | | plain |
total_time | double precision | | | | plain |
min_time | double precision | | | | plain |
max_time | double precision | | | | plain |
mean_time | double precision | | | | plain |
stddev_time | double precision | | | | plain |
rows | bigint | | | | plain |
shared_blks_hit | bigint | | | | plain |
shared_blks_read | bigint | | | | plain |
shared_blks_dirtied | bigint | | | | plain |
shared_blks_written | bigint | | | | plain |
local_blks_hit | bigint | | | | plain |
local_blks_read | bigint | | | | plain |
local_blks_dirtied | bigint | | | | plain |
local_blks_written | bigint | | | | plain |
temp_blks_read | bigint | | | | plain |
temp_blks_written | bigint | | | | plain |
blk_read_time | double precision | | | | plain |
blk_write_time | double precision | | | | plain |
first_call | timestamp with time zone | | | | plain |
last_call | timestamp with time zone | | | | plain |
View definition:
SELECT pg_store_plans.userid,
pg_store_plans.dbid,
pg_store_plans.queryid,
pg_store_plans.planid,
pg_store_plans.plan,
pg_store_plans.calls,
pg_store_plans.total_time,
pg_store_plans.min_time,
pg_store_plans.max_time,
pg_store_plans.mean_time,
pg_store_plans.stddev_time,
pg_store_plans.rows,
pg_store_plans.shared_blks_hit,
pg_store_plans.shared_blks_read,
pg_store_plans.shared_blks_dirtied,
pg_store_plans.shared_blks_written,
pg_store_plans.local_blks_hit,
pg_store_plans.local_blks_read,
pg_store_plans.local_blks_dirtied,
pg_store_plans.local_blks_written,
pg_store_plans.temp_blks_read,
pg_store_plans.temp_blks_written,
pg_store_plans.blk_read_time,
pg_store_plans.blk_write_time,
pg_store_plans.first_call,
pg_store_plans.last_call
FROM pg_store_plans() pg_store_plans(userid, dbid, queryid, planid, plan, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time, first_call, last_call);
Данное представление, функции pg_store_plans_reset() и pg_store_plans(), а также ряд вспомогательных функций доступны только в базах данных, где pg_store_plans установлен с помощью CREATE EXTENSION. Тем не менее статистика собирается по всем базам данных сервера всякий раз, когда расширение pg_store_plans загружается на сервер, независимо от его наличия в БД. Установленное расширение позволяет получить к ней доступ.
Пользователям, кроме суперпользователя, не разрешается видеть поля plan, queryid и planid для запросов, выполняемых другими пользователями. Пример вывода значений данных полей с доступом и без:
postgres=> select queryid, planid, plan from ext.pg_store_plans\gx
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------
queryid |
planid |
plan | <insufficient privilege>
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------------------
queryid | 1534825379
planid | 326764836
plan | Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=224) (actual time=0.173..0.176 rows=27 loops=1)
План запроса и queryid, вычисляемый для идентификации исходного запроса, рассчитываются аналогично тому, как это реализовано в расширении pg_stat_statements. Два плана считаются одинаковыми, если их текстовое представление одинаково за исключением значений литеральных констант или изменяющихся значений, таких как затраты или измеренное время.
Идентификатор плана planid вычисляется без учета изменяющихся свойств планов. В поле plan представления pg_store_plans продолжают отображаться самые актуальные значения для этих изменяющихся свойств.
pg_store_plans и pg_stat_statements поддерживают свои записи индивидуально (для этих двух расширений статистика сбрасывается отдельно функциями pg_stat_statements_reset() и pg_store_plans_reset()), поэтому существует вероятность, особенно для запросов с низкой частотой выполнения, что запрос по плану или наоборот будет не найден.
Представление pg_store_plans_info
Статистика самого расширения pg_store_plans собирается и становится доступной через представление pg_store_plans_info:
| Имя | Тип | Описание |
|---|---|---|
| dealloc | bigint | Общее количество раз, когда записи pg_store_plans о наименее выполняемых операторах освобождались, поскольку наблюдалось больше отдельных операторов, чем pg_store_plans.max |
| stats_reset | timestamp with time zone | Время последнего сброса всей статистики в представлении pg_store_plans |
DDL представления:
postgres=# \d+ pg_store_plans_info
View "ext.pg_store_plans_info"
Column | Type | Collation | Nullable | Default | Storage | Description
-------------+--------------------------+-----------+----------+---------+---------+-------------
dealloc | bigint | | | | plain |
stats_reset | timestamp with time zone | | | | plain |
View definition:
SELECT pg_store_plans_info.dealloc,
pg_store_plans_info.stats_reset
FROM pg_store_plans_info() pg_store_plans_info(dealloc, stats_reset);
Функции
Функция | Возвращаемое значение | Описание |
|
| Удаляет всю статистику, собранную на данный момент расширением |
|
| Формирует представление |
|
| Формирует представление |
|
| Генерирует обычное текстовое представление из необработанного представления плана в |
|
| Преобразует «план JSON в коротком формате» или «формат raw» в обычный формат JSON. |
|
| Генерирует XML-представление из необработанного представления плана в |
|
| Генерирует представление YAML из необработанного представления плана в |
Параметры конфигурации
Параметр | Описание | Тип | Возможные значения |
| Максимальное количество планов, отслеживаемых расширением (максимальное количество строк в представлении |
| Значение по умолчанию |
| Аналогично |
| Только суперпользователи могут изменять этот параметр. Возможные значения:
|
| Максимальная длина текста планов в байтах в формате raw (сокращенный JSON для хранения). Длина текста плана усекается, если она превышает это значение |
| Значение по умолчанию равно |
| Определяет способ хранения текстов планов на сервере |
| Возможные значения:
|
| Управление форматом планов в |
| Значения по умолчанию |
| Минимальное время выполнения инструкции в миллисекундах, которое приведет к занесению плана инструкции в журнал |
| При установке этого параметра в нулевое значение (по умолчанию) регистрируются все планы. Только суперпользователи могут изменять этот параметр |
| Включение выходных данные |
| По умолчанию этот параметр отключен ( |
| Включение в план |
| По умолчанию этот параметр отключен ( |
| Отключение записи фактического времени ( |
| Значение по умолчанию - |
| Включение в зарегистрированные планы статистики выполнения триггера. Этот параметр не действует, если не включен параметр |
| Значение по умолчанию - |
| Включение в план вывода |
| По умолчанию этот параметр отключен ( |
| Флаг, указывающий, следует ли сохранять статистику планов при отключении сервера. Если он выключен, то статистика не сохраняется при завершении работы и не перезагружается при запуске сервера |
| Значение по умолчанию — включено ( |
Использование модуля
Далее приведен пример использования расширения:
bench=# SELECT pg_store_plans_reset();
$ pgbench -i bench
$ pgbench -c10 -t1000 bench
bench=# \x
bench=# SELECT s.query, p.plan,
p.calls as "plan calls", s.calls as "stmt calls",
p.total_time / p.calls as "time/call", p.first_call, p.last_call
FROM pg_stat_statements s
JOIN pg_store_plans p USING (queryid) WHERE p.calls < s.calls
ORDER BY query ASC, "time/call" DESC;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
plan | Update on pgbench_tellers (cost=0.00..7.88 rows=0 width=0) +
| -> Seq Scan on pgbench_tellers (cost=0.00..7.88 rows=1 width=10) +
| Filter: (tid = 1)
plan calls | 396
stmt calls | 10000
time/call | 16.15434492676767
first_call | 2021-11-25 15:11:38.258838+09
last_call | 2021-11-25 15:11:40.170291+09
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
plan | Update on pgbench_tellers (cost=0.14..8.15 rows=0 width=0) +
| -> Index Scan using pgbench_tellers_pkey on pgbench_tellers (cost=0.14..8.15 rows=1 width=10) +
| Index Cond: (tid = 8) +
plan calls | 9604
stmt calls | 10000
time/call | 10.287281695439345
first_call | 2021-11-25 15:11:40.161556+09
last_call | 2021-11-25 15:12:09.957773+09
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | select s.query, p.plan, p.calls as "plan calls", s.calls as "stmt calls", p.total_time / p.calls as "time/call", p.first_call, p.last_call from pg_stat_statements s join pg_store_plans p using (queryid) where p.calls < s.calls order by query asc, "time/call" desc
plan | Sort (cost=309.71..313.88 rows=1667 width=104) +
| Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC +
| -> Merge Join (cost=119.66..220.50 rows=1667 width=104) +
| Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid) +
| Join Filter: (pg_store_plans.calls < pg_stat_statements.calls) +
| -> Sort (cost=59.83..62.33 rows=1000 width=48) +
| Sort Key: pg_stat_statements.queryid +
| -> Function Scan on pg_stat_statements (cost=0.00..10.00 rows=1000 width=48) +
| -> Sort (cost=59.83..62.33 rows=1000 width=72) +
| Sort Key: pg_store_plans.queryid +
| -> Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=72) +
plan calls | 3
stmt calls | 4
time/call | 16.387161
first_call | 2021-11-25 15:20:57.978082+09
last_call | 2021-11-25 15:23:48.631993+09
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | select s.query, p.plan, p.calls as "plan calls", s.calls as "stmt calls", p.total_time / p.calls as "time/call", p.first_call, p.last_call from pg_stat_statements s join pg_store_plans p using (queryid) where p.calls < s.calls order by query asc, "time/call" desc
plan | Sort (cost=309.71..313.88 rows=1667 width=104) +
| Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC +
| Sort Method: quicksort Memory: 26kB +
| -> Merge Join (cost=119.66..220.50 rows=1667 width=104) +
| Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid) +
| Join Filter: (pg_store_plans.calls < pg_stat_statements.calls) +
| Rows Removed by Join Filter: 7 +
| -> Sort (cost=59.83..62.33 rows=1000 width=48) +
| Sort Key: pg_stat_statements.queryid +
| Sort Method: quicksort Memory: 27kB +
| -> Function Scan on pg_stat_statements (cost=0.00..10.00 rows=1000 width=48) +
| -> Sort (cost=59.83..62.33 rows=1000 width=72) +
| Sort Key: pg_store_plans.queryid +
| Sort Method: quicksort Memory: 30kB +
| -> Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=72) +
plan calls | 1
stmt calls | 4
time/call | 4.46928
first_call | 2021-11-25 15:12:27.142535+09
last_call | 2021-11-25 15:12:27.142536+09
Ссылки на документацию разработчика
Дополнительно поставляемый модуль pg_store_plans: https://github.com/ossc-db/pg_store_plans.