pg_store_plans. Хранение планов запросов
Версия: 1.7.
В исходном дистрибутиве установлено по умолчанию: нет.
Связанные компоненты: отсутствуют.
Схема размещения:
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, в противном случае равно нулю) |
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);
Функции
| Функция | Возвращаемое значение | Описание |
|---|---|---|
pg_store_plans_reset() | void | Удаляет всю статистику, собранную на данный момент расширением pg_store_plans. По умолчанию эту функцию могут запускать только суперпользователи |
pg_store_plans(showtext boolean) | setof record | Формирует представление pg_store_plans |
pg_store_plans_info() | record | Формирует представление pg_store_plans_info |
pg_store_plans_textplan(query text) | text | Генерирует обычное текстовое представление из необработанного представления плана в pg_store_plans, которое отображается там, когда pg_store_plans.plan_formats = 'raw'. Поскольку текст результирующего плана генерируется на основе представления в формате JSON, он может немного отличаться от того, что получится непосредственно из командной строки EXPLAIN |
pg_store_plans_JSONplan(query text) | text | Преобразует «план JSON в коротком формате» или «формат raw» в обычный формат JSON. Краткий формат JSON - это внутренний формат для плана в pg_store_plans, который отображается там, когда pg_store_plans.plan_formats = 'raw' |
pg_store_plans_xmlplan(query text) | text | Ге нерирует XML-представление из необработанного представления плана в pg_store_plans, которое отображается там, когда pg_store_plans.plan_formats = 'raw' |
pg_store_plans_yamlplan(query text) | text | Генерирует представление YAML из необработанного представления плана в pg_store_plans, которое отображается там, когда pg_store_plans.plan_formats = 'raw' |
Параметры конфигурации
| Параметр | Описание | Тип | Возможные значения |
|---|---|---|---|
pg_store_plans.max | Максимальное количество планов, отслеживаемых расширением (максимальное количество строк в представлении pg_store_plans). Если наблюдается больше различных планов, чем указанное в значении, информация о наименее выполняемом плане отбрасывается | integer | Значение по умолчанию 1000. Этот параметр может быть установлен только при запуске сервера |
pg_store_plans.track | Аналогично pg_stat_statements, pg_store_plans.track управляет тем, какие запросы учитываются расширением | enum | Только суперпользователи могут изменять этот параметр. Возможные значения: - top (значение по умолчанию) - отслеживание инструкций верхнего уровня (тех, которые выдаются непосредственно клиентами);- all - для отслеживания вложенных инструкций (например, инструкций, вызываемых внутри функций, за исключением некоторых команд). Команды, выполняемые в рамках команд CREATE EXTENSION и ALTER EXTENSION при установке данного значения игнорируются;- none - для отключения сбора статистики;- verbose - для отслеживания всех команд, включая исключенные всеми предыдущими значениями |
pg_store_plans.max_plan_length | Максимальная длина текста планов в байтах в формате raw (сокращенный JSON для хранения). Длина текста плана усекается, если она превышает это значение | integer | Значение по умолчанию равно 5000. Этот параметр может быть установлен только при запуске сервера |
pg_store_plans.plan_storage | Определяет способ хранения текстов планов на сервере | enum | Возможные значения: - file (значение по умолчанию) - тексты планов сохраняются во временном файле аналогично расширению pg_stat_statements;- shmem - хранение текстов планов в оперативной памяти |
pg_store_plans.plan_format | Управление форматом планов в pg_store_plans | enum | Значения по умолчанию text используется для отображения в обычном текстовом представлении, а возможные значения json, xml и yaml используются для соответствующих им форматов. Для получения внутреннего представления, которое может быть передано pg_store_plans_*plan-функциям, используется значение raw |
pg_store_plans.min_duration | Минимальное время выполнения инструкции в миллисекундах, которое приведет к занесению плана инструкции в журнал | integer | При установке этого параметра в нулевое значение (по умолчанию) регистрируются все планы. Только суперпользователи могут изменять этот параметр |
pg_store_plans.log_analyze | Включение выходных данные EXPLAIN ANALYZE (а не только EXPLAIN) | boolean | По умолчанию этот параметр отключен (false) |
pg_store_plans.log_buffers | Включение в план EXPLAIN (ANALYZE, BUFFERS) выходных данных (не только EXPLAIN) | boolean | По умолчанию этот параметр отключен (false) |
pg_store_plans.log_timing | Отключение записи фактического времени (false). Накладные расходы на повторное считывание системных часов могут значительно замедлить выполнение запроса в некоторых системах, поэтому может быть полезно установить этот параметр в значение false, когда требуется только фактическое количество строк, а не точное время выполнения для каждого узла. Время выполнения всей инструкции измеряется всегда, когда pg_store_plans.log_analyze имеет значение true (значение по умолчанию) | boolean | Значение по умолчанию - true |
pg_store_plans.log_triggers | Включение в зарегистрированные планы статистики выполнения триггера. Этот параметр не действует, если не включен параметр pg_store_plans.log_analyze | boolean | Значение по умолчанию - off |
pg_store_plans.verbose | Включение в план вывода EXPLAIN VERBOSE (не только EXPLAIN) | boolean | По умолчанию этот параметр отключен (false) |
pg_store_plans.save | Флаг, указывающий, следует ли сохранять статистику планов при отключении сервера. Если он выключен, то статистика не сохраняется при завершении работы и не перезагружается при запуске сервера | boolean | Значение по умолчанию — включено (true). Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера |
Использование модуля
Далее приведен пример использования расширения:
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.