Модуль pg_store_plans
Модуль pg_store_plans предоставляет средства для отслеживания статистики плана выполнения всех операторов SQL, выполняемых сервером.
Модуль необходимо загрузить, добавив pg_store_plans в shared_preload_libraries в postgresql.conf , поскольку для этого требуется дополнительная общая память. Это означает, что для добавления или удаления модуля требуется перезагрузка сервера. pg_store_plans требует, чтобы переменная GUC Compute_query_id была «включена» или «автоматически». Если установлено значение «нет», pg_store_plans автоматически отключается.
1. Представление (view) pg_store_plans
Статистика, собранная модулем, доступна через системное представление pg_store_plans . Это представление содержит по одной строке для каждого отдельного набора идентификаторов базы данных, идентификаторов пользователей и идентификаторов запросов. Столбцы представления описаны в Таблице 1 .
Таблица 1. Столбцы pg_store_plans
Имя | Тип | References | Описание |
|---|---|---|---|
userid | oid | pg_authid.oid | OID пользователя, выполнившего оператор |
dbid | oid | pg_database.oid | OID базы данных, в которой был выполнен оператор |
queryid | bigint | Идентификатор запроса, сгенерированный ядром. Если для параметра calculate_query_id установлено значение "нет", pg_store_plan автоматически отключается. Его можно использовать в качестве ключа соединения с pg_stat_statements . | |
planid | bigint | Хэш-код плана, вычисленный из нормализованного представления плана. | |
plan | text | Текст репрезентативного плана. Формат задается параметром конфигурации pg_store_plans.plan_format. | |
calls | bigint | Количество выполненных раз | |
total_time | double precision | Общее время нахождения в выписке по плану, в миллисекундах | |
rows | bigint | Общее количество строк, извлеченных или затронутых оператором с использованием плана | |
shared_blks_hit | bigint | Общее количество попаданий в кэш общих блоков оператором, использующим план | |
shared_blks_read | bigint | Общее количество общих блоков, прочитанных оператором с использованием плана | |
shared_blks_dirtied | bigint | Общее количество общих блоков, загаженных оператором с использованием плана | |
shared_blks_written | bigint | Общее количество общих блоков, записанных оператором с использованием плана | |
local_blks_hit | bigint | Общее количество попаданий в локальный кэш блоков оператором, использующим план | |
local_blks_read | bigint | Общее количество локальных блоков, прочитанных оператором с использованием плана | |
local_blks_dirtied | bigint | Общее количество локальных блоков, загаженных оператором с использованием плана | |
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 | Отметка времени для последнего вызова запроса с использованием этого плана. |
Это представление, а также функции pg_store_plans_reset и pg_store_plansдругие вспомогательные функции доступны только в базах данных, где pg_store_plans установлен с помощью CREATE EXTENSION . Однако статистика отслеживается по всем базам данных сервера всякий раз, когда модуль pg_store_plans загружается на сервер, независимо от наличия представления.
Из соображений безопасности пользователям, не являющимся суперпользователями, не разрешается просматривать представление плана, queryid или planid для запросов, выполняемых другими пользователями.
queryid вычисляется для идентификации исходного запроса аналогично pg_stat_statements , но по другому алгоритму. план рассчитывается аналогично. Два плана считаются одинаковыми, если они кажутся эквивалентными, за исключением значений буквальных констант или флуктуирующих значений, таких как затраты или измеренное время.
Для PostgreSQL 14 или более поздней версии вы можете найти соответствующий запрос для записи pg_store_plans в pg_stat_statements , присоединившись с помощью queryid , как показано ниже.
SELECT s.query, p.plan FROM pg_store_plans p JOIN pg_stat_statements s USING (queryid);
Идентификатор плана рассчитывается без учета меняющихся свойств планов. С другой стороны, в представлении pg_store_plans.plan отображаются самые последние значения этих изменяющихся свойств.
pg_store_plans и pg_stat_statements обслуживают свои записи по отдельности, поэтому существует определенный неизбежный шанс, особенно для записей с низкой частотой выполнения, что корреспондент не будет найден.
2. Представление pg_store_plans
Статистика самого модуля pg_store_plans отслеживается и становится доступной через представление с именем pg_store_plans_info . Это представление содержит только одну строку. Столбцы представления показаны в Таблице 2 .
Таблица 2. Столбцы pg_store_plans_info
Имя | Тип | References | Описание |
|---|---|---|---|
dealloc | bigint | Общее количество раз, когда записи pg_store_plans о наименее выполняемых инструкциях были освобождены из-за того, что наблюдалось больше отдельных инструкций, чем в pg_store_plans.max. | |
stats_reset | timestamp with time zone | Время последнего сброса всей статистики в представлении pg_store_plans. |
3. Функции
pg_store_plans_reset() returns void
pg_store_plans_reset сбрасывает всю статистику, собранную pg_store_plans . По умолчанию эту функцию могут выполнять только суперпользователи.
pg_store_plans(showtext boolean) returns setof record
Представление pg_store_plans определяется с помощью функции, также называемой pg_store_plans.pg_store_plans_info() returns recordpg_store_plans_infoпредставление определяется в терминах функции, также называемой
pg_store_plans_info.pg_store_hash_query(query text) returns oid
Эта функция вычисляет хеш-значение текста запроса. Тот же алгоритм используется для вычисления queryid в pg_store_plans , поэтому эту функцию можно использовать для соединения с pg_store_plans .
pg_store_plans_textplan(query text) returns text
Эта функция генерирует обычное текстовое представление из необработанного представления плана в pg_store_plans , которое отображается там, когда pg_store_plans.plan_formats = 'raw'. Поскольку текст плана результатов генерируется из представления json, он может немного отличаться от того, что вы получите непосредственно из команды «EXPLAIN».
pg_store_plans_jsonplan(query text) returns text
Эта функция преобразует «краткий формат json-плана» или «необработанный формат» в обычный формат json. Краткий формат json — это внутренний формат плана в pg_store_plans , который отображается там, когда pg_store_plans.plan_formats = 'raw'.
pg_store_plans_xmlplan(query text) returns text
Эта функция создает XML-представление из необработанного представления плана в pg_store_plans , которое отображается там, когда pg_store_plans.plan_formats = 'raw'.
pg_store_plans_yamlplan(query text) returns text
Эта функция генерирует представление YAML из необработанного представления плана в pg_store_plans , которое отображается там, когда pg_store_plans.plan_formats = 'raw'.
4. Параметры конфигурации
pg_store_plans.max ( целое число )
pg_store_plans.max — это максимальное количество планов, отслеживаемых модулем (т. е. максимальное количество строк в представлении pg_store_plans ). Если наблюдается больше различных планов, информация о наименее выполненном плане отбрасывается. Значение по умолчанию — 1000. Этот параметр можно задать только при запуске сервера.
pg_store_plans.track ( перечисление )
Аналогично pg_stat_statements , pg_store_plans.track контролирует, какие операторы учитываются модулем. Укажите top , чтобы отслеживать операторы верхнего уровня (выданные непосредственно клиентами), all , чтобы также отслеживать вложенные операторы (например, операторы, вызываемые внутри функций, за исключением некоторых команд, см. ниже), или none, чтобы отключить сбор статистики операторов. Значение по умолчанию — top . Когда все указано, команды, выполняемые в командах CREATE EXTENSION и ALTER EXTENSION , по-прежнему игнорируются. Укажите подробный отслеживать все команды, в том числе исключенные всеми . Только суперпользователи могут изменить этот параметр.
pg_store_plans.max_plan_length ( целое число )
pg_store_plans.max_plan_length — максимальная длина планов в необработанном (сокращенном формате JSON) для хранения в байтах. Текст плана усекается по длине, если он длиннее этого значения. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.
pg_store_plans.plan_storage ( целое число )
pg_store_plans.plan_storage указывает, как хранятся тексты планов во время работы сервера. Если установлено значение file , тексты плана сохраняются во временном файле, как это делает pg_stat_statements. shmem означает хранить тексты плана в памяти. Значение по умолчанию — «файл». Подробности см . в обсуждении ниже .
pg_store_plans.plan_format ( перечисление )
pg_store_plans.plan_format управляет форматом планов в pg_store_plans . text является значением по умолчанию и отображается в обычном текстовом представлении, json , xml и yaml отображается в соответствующем формате. raw , чтобы получить внутреннее представление, которое можно передать pg_store_plans_*plan функциям.
pg_store_plans.min_duration ( целое число )
pg_store_plans.min_duration — это минимальное время выполнения оператора в миллисекундах, при котором план оператора регистрируется. Установка этого параметра равным нулю (по умолчанию) регистрирует все планы. Только суперпользователи могут изменить эту настройку.
pg_store_plans.log_analyze ( логическое значение )
pg_store_plans.log_analyze приводит к включению в план вывода EXPLAIN ANALYZE , а не только вывода EXPLAIN . Этот параметр отключен по умолчанию.
pg_store_plans.log_buffers ( логическое значение )
pg_store_plans.log_buffers заставляет вывод EXPLAIN (ANALYZE, BUFFERS) , а не только вывод EXPLAIN , включаться в план . Этот параметр отключен по умолчанию.
pg_store_plans.log_timing ( логическое значение )
Установка для pg_store_plans.log_timing значения false отключает запись фактического времени. Накладные расходы на повторное чтение системных часов могут значительно замедлить выполнение запроса в некоторых системах, поэтому может быть полезно установить для этого параметра значение FALSE, когда требуется только фактическое количество строк, а не точное время выполнения для каждого узла выполнения. Время выполнения всего оператора всегда измеряется, когда pg_store_plans.log_analyze имеет значение TRUE. По умолчанию оно равно ИСТИНА.
pg_store_plans.log_triggers ( логическое значение )
pg_store_plans.log_triggers заставляет статистику выполнения триггера включаться в записываемые планы. Этот параметр не действует, если не включен параметр pg_store_plans.log_analyze .
pg_store_plans.verbose ( логическое значение )
pg_store_plans.verbose приводит к включению в план вывода EXPLAIN VERBOSE , а не только вывода EXPLAIN . Этот параметр отключен по умолчанию.
pg_store_plans.save ( логическое значение )
pg_store_plans.save указывает, сохранять ли статистику плана при выключении сервера. Если он выключен , то статистика не сохраняется при завершении работы и не перезагружается при запуске сервера. Значение по умолчанию включено . Этот параметр можно задать только в файле postgresql.conf или в командной строке сервера.
5. Обсуждение настройки plan_storage
pg_store_plans требует дополнительной общей памяти, пропорциональной pg_store_plans.max . Когда для pg_store_plans.plan_storage задано значение «shmem», он требует дополнительной дополнительной общей памяти для хранения текстов планов в объеме, равном произведению максимального количества планов для хранения (pg_store_plans.max) и максимальной длины отдельного плана (pg_store_plans. макс_план_длина). Если для pg_store_plans.plan_storage установлено значение «файл», тексты планов записываются во временный файл, как это делает 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 .
Эти параметры должны быть установлены в postgresql.conf . Пример настройки следующий:
# postgresql.conf shared_preload_libraries = 'pg_store_plans, pg_stat_statements' pg_store_plans.max = 10000 pg_store_plans.track = все
6. Пример вывода
(postgresql.conf имеет следующие настройки)
shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
pg_store_plans.log_analyze = true
pg_store_plans.log_timing = falseВ консоли
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
postgres=#