F.51. pg_store_plans#
F.51. pg_store_plans #
F.51.2. Описание #
Модуль pg_store_plans предоставляет средства для
отслеживания статистики планов выполнения всех операторов SQL, выполняемых
сервером.
Модуль должен быть загружен путем добавления
pg_store_plans в
shared_preload_libraries
в postgresql.conf, поскольку он требует
дополнительной общей памяти. Это означает, что для добавления или удаления модуля
требуется перезагрузка сервера.
pg_store_plans требует, чтобы переменная GUC
compute_query_id была установлена в "on" или
"auto". Если она установлена на "no",
pg_store_plans тихо отключается.
F.51.3. Представление pg_store_plans #
Статистика, собранная модулем, доступна через системное представление под названием pg_store_plans. Это представление содержит одну строку для каждого уникального набора ID базы данных, ID пользователя и ID запроса. Столбцы этого представления описаны в Table 1.
Таблица 1. Колонки pg_store_plans
Таблица F.26. Таблица 1. pg_store_plans
|
Имя |
Тип |
Ссылки |
Описание |
|---|---|---|---|
|
userid |
oid |
pg_authid.oid |
OID пользователя, выполнившего оператор |
|
dbid |
oid |
pg_database.oid |
OID базы данных, в которой был выполнен оператор |
|
queryid |
bigint |
ID запроса, сгенерированный ядром. Если compute_query_id установлено в значение "no", pg_store_plan тихо отключается. Это можно использовать как ключ для соединения с pg_stat_statements. | |
|
planid |
bigint |
Хеш-код плана, вычисленный из нормализованного представления плана. | |
|
план |
текст |
Текст представительского плана. Формат указывается параметром конфигурации pg_store_plans.plan_format. | |
|
вызовы |
bigint |
Количество выполнений | |
|
total_time |
двойная точность |
Общее время, проведенное на выполнении инструкции с использованием плана, в миллисекундах | |
|
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 |
двойная точность |
Общее время, которое оператор, использующий план, потратил на чтение блоков, в миллисекундах (если включен track_io_timing, иначе ноль) | |
|
blk_write_time |
двойная точность |
Общее время, которое оператор, использующий план, потратил на запись блоков, в миллисекундах (если включен track_io_timing, в противном случае ноль) | |
|
temp_blk_read_time |
двойная точность |
Общее время, которое оператор, использующий план, потратил на чтение блоков временного файла, в миллисекундах (если включен track_io_timing, иначе ноль) | |
|
temp_blk_write_time |
двойная точность |
Общее время, которое оператор, использующий план, потратил на запись блоков временного файла, в миллисекундах (если включен 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, но с использованием другого
алгоритма. plan рассчитывается аналогичным образом. Два
плана считаются одинаковыми, если они кажутся эквивалентными, за исключением
значений литеральных констант или колеблющихся значений, таких как
стоимость или измеренное время.
Для 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);
ID плана рассчитывается без учета колеблющихся свойств планов. С другой стороны, представление pg_store_plans.plan продолжает отображать самые последние значения для этих колеблющихся свойств.
pg_store_plans и
pg_stat_statements поддерживают свои записи
индивидуально, поэтому есть определенный неизбежный шанс, особенно для
записей с низкой частотой выполнения, что соответствующий элемент не будет найден.
F.51.4. Представление pg_store_plans #
Статистика модуля pg_store_plans отслеживается и доступна через представление с именем pg_store_plans_info. Это представление содержит только одну строку. Столбцы этого представления показаны в Table 2.
Table 2.
pg_store_plans_info Columns
| Имя | Тип | Ссылки | Описание |
|---|---|---|---|
dealloc
|
bigint
| Общее количество случаев, когда записи pg_store_plans о наименее выполняемых операторах были освобождены, потому что было зарегистрировано больше различных операторов, чем pg_store_plans.max. | |
stats_reset
|
timestamp with time zone
| Время, когда все статистики в представлении pg_store_plans были в последний раз сброшены. |
F.51.5. Функции #
-
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 record Представление
pg_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 Эта функция генерирует обычное текстовое представление из сырого представления
planвpg_store_plans, которое отображается там, когдаpg_store_plans.plan_formats= 'raw'. Поскольку результирующий текст плана генерируется из json представления, он может немного отличаться от того, что вы получите непосредственно из команды 'EXPLAIN'.-
pg_store_plans_jsonplan(query text) returns text Эта функция преобразует "короткий формат json плана" или "сырой формат" в обычный формат json. Короткий формат json является внутренним форматом для
planвpg_store_plans, который отображается там, когдаpg_store_plans.plan_formats= 'raw'.-
pg_store_plans_xmlplan(query text) returns text Эта функция генерирует XML представление из сырого представления
planвpg_store_plans, которое отображается там, когдаpg_store_plans.plan_formats= 'raw'.-
pg_store_plans_yamlplan(query text) returns text Эта функция генерирует представление YAML из сырого представления
planвpg_store_plans, которое отображается там, когдаpg_store_plans.plan_formats= 'raw'.
F.51.6. Параметры конфигурации #
-
pg_store_plans.max(integer) pg_store_plans.max- это максимальное количество планов, отслеживаемых модулем (то есть максимальное количество строк в представленииpg_store_plans). Если наблюдается больше различных планов, чем это число, информация о наименее исполняемом плане отбрасывается. Значение по умолчанию - 1000. Этот параметр можно установить только при запуске сервера.-
pg_store_plans.track(enum) Аналогично
pg_stat_statements,pg_store_plans.trackконтролирует, какие операторы учитываются модулем. Укажитеtop, чтобы отслеживать операторы верхнего уровня (те, которые напрямую выполняются клиентами),all, чтобы также отслеживать вложенные операторы (например, операторы, вызываемые внутри функций, за исключением некоторых команд, см. ниже), илиnone, чтобы отключить сбор статистики операторов. Значение по умолчанию -top. Когда указаноall, команды, выполняемые под командамиCREATE EXTENSIONиALTER EXTENSION, все еще игнорируются. Укажитеverbose, чтобы отслеживать все команды, включая те, которые исключеныall. Только суперпользователи могут изменить эту настройку.pg_store_plans.max_plan_length- это максимальная длина планов в байтах в сыром (сокращенном JSON) формате для хранения. Текст плана обрезается до этой длины, если он длиннее указанного значения. Значение по умолчанию - 5000. Этот параметр можно установить только при запуске сервера.-
pg_store_plans.plan_storage(integer) pg_store_plans.plan_storageопределяет, как тексты планов хранятся во время работы сервера. Если он установлен наfile, тексты планов хранятся в временном файле, как это делает pg_stat_statements.shmemозначает хранение текстов планов в памяти. Значение по умолчанию - "file".-
pg_store_plans.plan_format(enum) pg_store_plans.plan_formatконтролирует форматplansвpg_store_plans.textявляется значением по умолчанию и отображается в обычном текстовом представлении,json,xmlиyamlотображаются в соответствующем формате.rawпозволяет получить внутреннее представление, которое может быть передано функциямpg_store_plans_*plan.-
pg_store_plans.min_duration(integer) pg_store_plans.min_duration- это минимальное время выполнения оператора, в миллисекундах, которое вызовет запись плана оператора. Установка этого значения в ноль (по умолчанию) ведет к записи всех планов. Только суперпользователи могут изменить эту настройку.-
pg_store_plans.log_analyze(boolean) pg_store_plans.log_analyzeприводит к выводуEXPLAIN ANALYZE, а не просто к выводуEXPLAIN, который будет включен вplan. Этот параметр по умолчанию выключен.-
pg_store_plans.log_buffers(boolean) pg_store_plans.log_buffersприводит к выводуEXPLAIN (ANALYZE, BUFFERS), а не просто к выводуEXPLAIN, который будет включен вplan. Этот параметр по умолчанию выключен.-
pg_store_plans.log_timing(boolean) Установка
pg_store_plans.log_timingв false отключает запись фактических временных интервалов. Постоянное чтение системных часов может значительно замедлить запрос на некоторых системах, поэтому может быть полезно установить этот параметр в FALSE, когда нужны только фактические количества строк, а не точное время выполнения для каждого узла выполнения. Время выполнения всего оператора всегда измеряется, когдаpg_store_plans.log_analyzeустановлен в TRUE. По умолчанию установлено в TRUE.-
pg_store_plans.log_triggers(boolean) pg_store_plans.log_triggersприводит к включению статистики выполнения триггеров в записанные планы. Этот параметр не имеет эффекта, еслиpg_store_plans.log_analyzeне включен.-
pg_store_plans.verbose(boolean) pg_store_plans.verboseвызывает выводEXPLAIN VERBOSE, а не просто выводEXPLAIN, для включения вplan. Этот параметр по умолчанию выключен.-
pg_store_plans.save(boolean) pg_store_plans.saveопределяет, следует ли сохранять статистику планов между выключениями сервера. Если этоoff, то статистика не сохраняется при выключении и не загружается при запуске сервера. Значение по умолчанию -on. Этот параметр можно установить только в файлеpostgresql.confили на командной строке сервера.-
pg_store_plans.store_last_plan(boolean) pg_store_plans.store_last_planопределяет, должен ли текст плана всегда обновляться. По умолчаниюfalse.-
pg_store_plans.sample_rate(float) pg_store_plans.sample_rateуказывает долю запросов для обработки. При высоких нагрузках это позволяет снизить влияниеpg_store_plansна производительность. По умолчанию0.0.-
pg_store_plans.slow_statement_duration(integer) pg_store_plans.slow_statement_durationуказывает безусловный план записи медленного оператора. Значение по умолчанию -0.
F.51.7. Обсуждение настройки plan_storage #
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 достаточно велик, чтобы сбор мусора не происходил, рекомендуется использовать "file" в качестве
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 = all
F.51.8. Пример вывода #
(postgresql.conf has following settings)
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=#