F.45. pgpro_stats_2 — сбор статистики планирования и выполнения всех обрабатываемых сервером SQL-операторов (экспериментальная функциональность) #
Расширение pgpro_stats_2 предоставляет статистику планирования и выполнения всех обрабатываемых сервером SQL-операторов. Оно основано на доработанных и улучшенных расширениях для сбора статистики pg_stat_statements и pg_wait_sampling и реализует следующую дополнительную функциональность:
сохранение помимо текста запросов соответствующих планов запросов;
вычисление статистики по событиям ожидания для выполняемых запросов.
pg_wait_sampling можно отключить с помощью параметра конфигурации.
Примечание
Во время остановки сервера расширение pgpro_stats_2 выгружает собранную статистику в три файла на диске в формате, который зависит от количества и порядка столбцов в представлении pgpro_stats_statements. Если этот формат изменился в новой версии pgpro_stats_2, то ранее сохранённые статистики не будут прочитаны при первом перезапуске сервера после обновления расширения, и в журнал будет записана ошибка:
LOG: pgpro_stats_2: could not read file "pg_stat/pg_stat_statements_Q.stat"
или
LOG: pgpro_stats_2: could not read file "pg_stat/pg_stat_statements_P.stat"
или
LOG: pgpro_stats_2: could not read file "pg_stat/pg_stat_statements_S.stat"
Содержимое файлов будет проигнорировано, а соответствующие статистики — обнулены. Чтобы узнать, изменился ли формат, ознакомьтесь с Замечаниями к выпускам для устанавливаемой версии pgpro_stats_2. Чтобы сохранить ранее собранные статистики, выгрузите их в CSV-файлы перед обновлением.
F.45.1. Ограничения #
При некоторых обстоятельствах
pgpro_stats_2может не сопоставить параметры в тексте запроса с теми же параметрами в соответствующем плане запроса.Некоторые запросы SPI в статистике не учитываются.
Тексты и планы некоторых запросов SPI не нормализованы.
Расширение
pgpro_stats_2несовместимо с pg_stat_statements, pg_wait_sampling, pgpro_stats, а также другими расширениями, встраивающими свои обработчики в анализатор, планировщик или исполнитель и влияющими на разбор, планирование или выполнение запросов. Более того, если иpgpro_stats_2, и любое другое расширение из перечисленных выше включены в списокshared_preload_libraries, сервер баз данных не запустится. Также обратите внимание, что для получения окончательной версии запросов и планов библиотекаpgpro_stats_2должна указываться последней в спискеshared_preload_libraries, но есть расширения, которые работают, только если их библиотека указана последней.pgpro_stats_2может не работать корректно со сторонними расширениями, добавляющими в планы узлыCustomScanиForeignScan.
F.45.2. Ограничения текущей версии #
В настоящее время pgpro_pwr и Postgres Pro Enterprise Manager не поддерживают интеграцию с
pgpro_stats_2и не используют собираемыеpgpro_stats_2данные.Запросы к представлениям
pg_wait_sampling_statementsиpg_wait_sampling_objectsвыполняются медленно, но это не влияет на производительность сервера.
F.45.3. Установка и настройка #
Расширение pgpro_stats_2 предоставляется вместе с Postgres Pro Standard в виде отдельного пакета pgpro-stats-std-18 (подробные инструкции по установке представлены в Главе 16). После установки pgpro_stats_2 выполните следующие действия, чтобы подготовить pgpro_stats_2 к работе:
Добавьте
pgpro_stats_2в параметр shared_preload_libraries в файлеpostgresql.conf:shared_preload_libraries = 'pgpro_stats_2'
Перезапустите Postgres Pro Standard, чтобы изменения вступили в силу.
Когда сервер перезапустится,
pgpro_stats_2начнёт собирать статистику по всем базам данных в кластере. При необходимости вы можете изменять область сбора или отключить его, воспользовавшись параметрами конфигурацииpgpro_stats_2.Обращаться к собираемой статистике вы сможете после того, как создадите расширение
pgpro_stats_2:CREATE EXTENSION pgpro_stats_2;
Кроме того, необходимо включить вычисление идентификатора запроса и идентификатора плана запроса, чтобы расширение pgpro_stats_2 было активно. Это происходит автоматически, если для compute_query_id задано значение auto или on, или загружен любой сторонний модуль, вычисляющий идентификаторы запросов.
F.45.4. Использование #
F.45.4.1. Сбор статистики по операторам запросов и планам #
После того как расширение pgpro_stats_2 будет установлено, оно начнёт собирать статистику по выполняемым операторам. Собираемые данные во многом пересекаются с теми, что собирает pg_stat_statements, но также включают информацию о планах запросов и событиях ожидания для каждого типа запроса. Полученная статистика сохраняется в памяти, а прочитать её можно через представление pgpro_stats_statements.
Для сбора статистики по событиям ожидания pgpro_stats_2 производит выборки с определённой периодичностью. Эта периодичность задаётся параметром pgpro_stats.profile_period и по умолчанию равна 10 мс. Если выборка показывает, что процесс находится в состоянии ожидания, к общей длительности ожидания события добавляется значение pgpro_stats.profile_period. Таким образом, оценка длительности ожидания остаётся корректной даже при изменениях значения pgpro_stats.profile_period. Если вас не интересует статистика по событиям ожидания, сбор этой информации можно отключить, установив для параметра pgpro_stats.pg_wait_sampling значение false.
Показатели pgpro_stats_statements.plans и pgpro_stats_statements.calls не обязательно должны совпадать, так как статистика планирования и выполнения обновляется в конце соответствующей фазы и только при успешном завершении этой фазы. Например, если для оператора успешно выполнилось планирование, но во время выполнения произошла ошибка, изменится только статистика планирования. Если же планирование пропускается по причине использования кешированного плана, увеличивается только счётчик выполнения.
Например, давайте создадим таблицу со случайными данными и построим в ней индекс:
CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i); CREATE INDEX test_x_idx ON test (x);
Выполните несколько раз следующий запрос с различными значениями :x_min и :x_max:
select * from test where x >= :x_min and x <= :x_max;
После этого собранная статистика должна появиться в представлении pgpro_stats_statements:
postgres=# SELECT queryid, query, planid, plan FROM pg_stats_statements WHERE query LIKE 'select * from test where%';
-[ RECORD 1 ]----------------------------------------------------
queryid | -1730632209943640345
query | select * from test where x >= $1 AND x <= $2
planid | -4215043083291098626
plan | Seq Scan on public.test +
| Output: i, x +
| Filter: ((test.x >= $1) AND (test.x <= $2)) +
| Query Identifier: -1730632209943640345 +
|
-[ RECORD 2 ]----------------------------------------------------
queryid | -1730632209943640345
query | select * from test where x >= $1 AND x <= $2
planid | -8025285020772903731
plan | Bitmap Heap Scan on public.test +
| Output: i, x +
| Recheck Cond: ((test.x >= $1) AND (test.x <= $2)) +
| -> Bitmap Index Scan on test_x_idx +
| Index Cond: ((test.x >= $1) AND (test.x <= $2))+
| Query Identifier: -1730632209943640345 +
|
-[ RECORD 3 ]----------------------------------------------------
queryid | -1730632209943640345
query | select * from test where x >= $1 AND x <= $2
planid | 6075533348212423849
plan | Index Scan using test_x_idx on public.test +
| Output: i, x +
| Index Cond: ((test.x >= $1) AND (test.x <= $2)) +
| Query Identifier: -1730632209943640345 +F.45.5. Представления #
F.45.5.1. Представление pgpro_stats_statements #
Статистика, собираемая модулем, выводится в представлении pgpro_stats_statements. Это представление содержит отдельные строки для каждой комбинации идентификаторов базы данных, пользователя, запроса и плана (но в количестве, не превышающем максимальное число различных операторов, которые может отслеживать модуль). Столбцы представления показаны в Таблице F.29.
Таблица F.29. Столбцы pgpro_stats_statements
| Имя | Тип | Ссылки | Описание |
|---|---|---|---|
userid | oid | | OID пользователя, выполнявшего оператор |
dbid | oid | | OID базы данных, в которой выполнялся оператор |
toplevel | bool | true, если данный запрос выполнялся на верхнем уровне (всегда true, если для параметра pgpro_stats.track задано значение top) | |
queryid | bigint | Хеш-код для идентификации нормализованного запроса | |
planid | bigint | Хеш-код для идентификации нормализованного плана оператора | |
query | text | Текст, представляющий оператор | |
plan | text | План запроса, представленный в формате, определённом параметром конфигурации pgpro_stats.plan_format | |
plans | int8 | Число операций планирования этого оператора (если включён параметр pgpro_stats.track_planning, иначе 0) | |
total_plan_time | float8 | Общее время, затраченное на планирование этого оператора в миллисекундах (если включён параметр pgpro_stats.track_planning, иначе 0) | |
min_plan_time | float8 | Минимальное время, затраченное на планирование этого оператора в миллисекундах (если включён параметр pgpro_stats.track_planning, иначе 0) | |
max_plan_time | float8 | Максимальное время, затраченное на планирование этого оператора в миллисекундах (если включён параметр pgpro_stats.track_planning, иначе 0) | |
mean_plan_time | float8 | Среднее время, затраченное на планирование этого оператора в миллисекундах (если включён параметр pgpro_stats.track_planning, иначе 0) | |
stddev_plan_time | float8 | Стандартное отклонение времени, затраченного на планирование этого оператора в миллисекундах (если включён параметр pgpro_stats.track_planning, иначе 0) | |
calls | int8 | Счётчик выполнений данного оператора | |
total_exec_time | float8 | Общее время, затраченное на выполнение оператора, в миллисекундах | |
min_exec_time | float8 | Минимальное время, потраченное на выполнение оператора, в миллисекундах | |
max_exec_time | float8 | Максимальное время, потраченное на выполнение оператора, в миллисекундах | |
mean_exec_time | float8 | Среднее время, потраченное на выполнение оператора, в миллисекундах | |
stddev_exec_time | float8 | Стандартное отклонение времени, затраченного на выполнение оператора, в миллисекундах | |
rows | int8 | Общее число строк, полученных или затронутых оператором | |
shared_blks_hit | int8 | Общее число попаданий разделяемых блоков в кеш для данного оператора | |
shared_blks_read | int8 | Общее число разделяемых блоков, прочитанных данным оператором | |
shared_blks_dirtied | int8 | Общее число разделяемых блоков, «загрязнённых» данным оператором | |
shared_blks_written | int8 | Общее число разделяемых блоков, записанных данным оператором | |
local_blks_hit | int8 | Общее число попаданий локальных блоков в кеш для данного оператора | |
local_blks_read | int8 | Общее число локальных блоков, прочитанных данным оператором | |
local_blks_dirtied | int8 | Общее число локальных блоков, «загрязнённых» данным оператором | |
local_blks_written | int8 | Общее число локальных блоков, записанных данным оператором | |
temp_blks_read | int8 | Общее число временных блоков, прочитанных данным оператором | |
temp_blks_written | int8 | Общее число временных блоков, записанных данным оператором | |
shared_blk_read_time | float8 | Общее время, затраченное оператором на чтение разделяемых блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае) | |
shared_blk_write_time | float8 | Общее время, затраченное оператором на запись разделяемых блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае) | |
local_blk_read_time | float8 | Общее время, затраченное оператором на чтение локальных блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае). В версиях Postgres Pro ниже 17 всегда содержит ноль | |
local_blk_write_time | float8 | Общее время, затраченное оператором на запись локальных блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае). В версиях Postgres Pro ниже 17 всегда содержит ноль | |
temp_blk_read_time | float8 | Общее время, затраченное оператором на чтение временных блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае). В версиях Postgres Pro ниже 15 всегда содержит ноль | |
temp_blk_write_time | float8 | Общее время, затраченное оператором на запись временных блоков, в миллисекундах (если включён track_io_timing, или ноль в противном случае). В версиях Postgres Pro ниже 15 всегда содержит ноль | |
wal_records | int8 | Общее число записей WAL, сгенерированных при выполнении оператора | |
wal_fpi | int8 | Общее число образов полных страниц в WAL, сгенерированных при выполнении оператора | |
wal_bytes | numeric | Общий объём WAL (в байтах), сгенерированный при выполнении оператора | |
wal_buffers_full | bigint | Количество случаев переполнения буферов WAL. В версиях Postgres Pro ниже 18 всегда содержит ноль | |
jit_functions | int8 | Общее число функций, скомпилированных в JIT-код при выполнении оператора. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_generation_time | float8 | Общее время, затраченное на компиляцию JIT-кода при выполнении оператора, в миллисекундах. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_inlining_count | int8 | Количество встраиваний функций, используемых в операторе. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_inlining_time | float8 | Общее время, затраченное на встраивание функций при выполнении оператора, в миллисекундах. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_optimization_count | int8 | Количество JIT-оптимизаций для оператора. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_optimization_time | float8 | Общее время, затраченное на JIT-оптимизацию при выполнении оператора, в миллисекундах. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_emission_count | int8 | Сколько раз выдавался код при выполнении оператора. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_emission_time | float8 | Общее время, затраченное на выдачу кода при выполнении оператора, в миллисекундах. В версиях Postgres Pro ниже 15 всегда содержит ноль | |
jit_deform_count | int8 | Общее число функций преобразования кортежей, скомпилированных в JIT-код при выполнении оператора. В версиях Postgres Pro ниже 17 всегда содержит ноль | |
jit_deform_time | float8 | Общее время, затраченное оператором на компилирование функций преобразования кортежей в JIT-код, в миллисекундах. В версиях Postgres Pro ниже 17 всегда содержит ноль | |
parallel_workers_to_launch | bigint | Число параллельных рабочих процессов, которые планируется запустить. В версиях Postgres Pro ниже 18 всегда содержит ноль | |
parallel_workers_launched | bigint | Число запущенных параллельных рабочих процессов. В версиях Postgres Pro ниже 18 всегда содержит ноль | |
stats_since | timestamp with time zone | Время, когда начался сбор статистики по этому оператору | |
minmax_stats_since | timestamp with time zone | Время, когда начался сбор статистики минимальных/максимальных значений по этому оператору (столбцы min_plan_time, max_plan_time, min_exec_time и max_exec_time) |
Примечание
Если параметр pgpro_stats.pg_wait_sampling включён, статистика событий ожидания доступна через встроенное расширение pg_wait_sampling.
Аналогично расширению pg_stat_statements, DML запросы (то есть SELECT, INSERT, UPDATE, DELETE и MERGE) в pgpro_stats_2 объединяются в одну запись, когда они имеют идентичные структуры запросов согласно внутреннему вычисленному хешу. Обычно два запроса будут считаться равными при таком сравнении, если они семантически равнозначны, не считая значений констант, фигурирующих в запросе. Когда значение константы игнорируется в целях сравнения запроса с другими запросами, эта константа заменяется в выводе pgpro_stats_2 обозначением параметра, например, $k, где k - натуральное число. Если в запросе уже есть параметры, то начальное значение k равно номеру, следующим за номером последнего параметра $n в исходном тексте запроса. Если параметров нет, то начальное значение k равно единице. Стоит отметить, что в некоторых случаях на эту нумерацию могут влиять скрытые символы параметров. Например, PL/pgSQL применяет такие символы для добавления в запросы значений локальных переменных функций, так что оператор PL/pgSQL вида SELECT i + 1 INTO j будет представлен в тексте как SELECT i + $2.
В некоторых случаях запросы с визуально различными текстами могут быть объединены в одну запись pgpro_stats_statements; как описано выше, это характерно для семантически равнозначных запросов. Кроме того, если единственное различие между запросами — число элементов в списке констант, список сжимается до одного элемента, но отображается с закомментированным индикатором списка:
=# SELECT pgpro_stats_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8); =# SELECT query, calls FROM pgpro_stats_statements WHERE query LIKE 'SELECT%'; -[ RECORD 1 ]------------------------------ query | SELECT * FROM test WHERE a IN ($1 /*, ... */) calls | 2
Кроме этих случаев есть небольшая вероятность, что из-за наложений хеша несвязанные запросы могут оказаться объединёнными в одной записи. (Однако это невозможно для запросов, относящимся к разным пользователям или базам данных.)
Подобный механизм объединения текстов pgpro_stats_2 использует и для текстов планов. При этом делается попытка сопоставить номера констант в тексте плана соответствующим номерам констант в тексте запроса. Если такая попытка для какой-либо константы в тексте плана оканчивается неудачей, то ей присваивается следующий номер, больший максимального номера заменённой константы в тексте запроса. Например, для запроса:
SELECT 1::int, 'abc'::VARCHAR(3), 2::int;
в его тексте и в тексте соответствующего плана pgpro_stats_2 подставит номера констант следующим образом:
postgres=# SELECT query, plan FROM pgpro_stats_statements;
query | plan
------------------------------------------------+--------------------------------------------------
SELECT $1::int, $2::VARCHAR(3), $3::int | Result +
| Output: $1, $4, $3 +В данном тексте плана удалось сопоставить константы с номерами 1 и 3 из текста запроса, а константу с номером 2 сопоставить не удалось, и она была заменена номером, следующим за максимальным номером в тексте запроса — номером 4.
При подстановке номеров в тесте плана делается исключение для номера версии XML документа. Если в исходном запросе он был задан константой, например '1.0', то в тексте плана это значение сохранится как есть и не будет заменено на подстановочный символ вида $k. Если в исходном запросе номер версии XML документа был задан выражением, то подстановка символов вместо констант будет сделана по обычным правилам.
F.45.5.2. Представление pgpro_stats_info #
Статистика самого модуля pgpro_stats_2 собирается и выдаётся через представление pgpro_stats_info. Это представление содержит только одну строку. Столбцы представления показаны в Таблице F.30.
Таблица F.30. Столбцы pgpro_stats_info
| Имя | Тип | Описание |
|---|---|---|
dealloc | bigint | Сколько всего раз в pgpro_stats_statements отбрасывались записи о редко выполняемых операторах, поскольку на обработку поступало больше различных операторов, чем задано в pgpro_stats.max |
stats_reset | timestamp with time zone | Время последнего сброса всех статистических данных в представлении pgpro_stats_statements |
F.45.6. Функции #
-
pgpro_stats_statements_reset(userid Oid, dbid Oid, queryid bigint, planid bigint, minmax_only boolean) returns timestamp with time zone Функция
pgpro_stats_statements_resetочищает всю статистику, собранную к этому времени модулемpgpro_stats_2для заданного пользователя (userid), базы данных (dbid), запроса (queryid) и плана (planid). В случае отсутствия одного из параметров для него подразумевается нулевое значение (неприменимое ограничение) и очищается статистика, соответствующая другим параметрам. Если никакой параметр не задан или все параметры имеют нулевое значение (неприменимое), очищается вся статистика. Вместе с очисткой всей статистики вpgpro_stats_statementsтакже сбрасывается статистика вpgpro_stats_info. Когда для параметраminmax_onlyустановлено значениеtrue, сбрасываются значения только минимального и максимального времени планирования и выполнения оператора (т. е. значения столбцовmin_plan_time,max_plan_time,min_exec_timeиmax_exec_time). Значениеminmax_onlyпо умолчанию —false. Время последнего сброса минимальных/максимальных значений можно увидеть в столбцеminmax_stats_sinceпредставленияpgpro_stats_statements. Функция возвращает время сброса статистики. Это время записывается в полеstats_resetпредставленияpgpro_stats_infoилиminmax_stats_sinceпредставленияpgpro_stats_statements, если соответствующий сброс значений был действительно выполнен. По умолчанию эту функцию могут выполнять только суперпользователи. Другим пользователям можно дать доступ к ней, используяGRANT.-
pgpro_stats_statements(showtext boolean) returns setof record Представление
pgpro_stats_statementsопределяется как результат вызова одноимённой функции. Функциюpgpro_stats_statementsможно также вызывать непосредственно, а с аргументомshowtext := falseможно получить результат без текста запроса (то есть в выходном аргументе, соответствующем столбцу представленияquery, будет выдаваться null). Эта возможность предусмотрена для того, чтобы внешние средства мониторинга могли не загружать каждый раз текст запросов, который может быть довольно объёмным. Средство мониторинга может просто один раз запомнить текст запросов для каждой записи в представлении (как это и делает сам модульpgpro_stats_2) и затем запрашивать текст для новых запросов только при необходимости. Так как сервер сохраняет текст запросов в файле, при таком подходе постоянное чтение содержимогоpgpro_stats_statementsбудет создавать меньшую нагрузку на ввод/вывод.-
pgpro_stats_info() returns record Представление
pgpro_stats_infoпостроено на базе одноимённой функции, которую пользователи также могут вызывать непосредственно.
F.45.7. Параметры конфигурации #
F.45.7.1. Параметры включения расширения #
pgpro_stats.pg_wait_sampling(boolean) #Параметр
pgpro_stats.pg_wait_samplingвключает функциональность встроенного расширения pg_wait_sampling. Значение по умолчанию —true. При изменении этого параметра требуется перезапустить сервер.
F.45.7.2. Основные параметры #
F.45.7.2.1. Параметры, связанные с pg_stat_statements #
Примечание
Все параметры конфигурации, описанные ниже, также могут использоваться с префиксом pg_stat_statements вместо pgpro_stats. Например, pg_stat_statements.max задаёт тот же параметр конфигурации, что и pgpro_stats.max.
pgpro_stats.max(integer) #pgpro_stats_2 собирает статистику только о наиболее часто встречающихся запросах. Чем меньше частота появления некоторого запроса во время работы сервера, тем меньше вероятность его попадания в статистику. Редко возникающие запросы будут почти сразу вытесняться из статистики более частыми. Параметр
pgpro_stats.maxзадаёт максимальное число различных уникальных пар (нормализованный текст запроса, нормализованный текст плана), отслеживаемых pgpro_stats_2 (то есть максимальное число строк в представленииpgpro_stats_statements). Чем выше значение этого параметра, тем о большем числе запросов информация сохранится, но ценой снижения производительности сервера как во время ожидания на блокировках при работе с таблицей статистики в разделяемой памяти, так и при периодической сборке мусора в файле с текстами запросов и планов. Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.pgpro_stats.track(enum) #Параметр
pgpro_stats.trackопределяет, какие операторы будут отслеживаться модулем. Со значениемtopотслеживаются операторы верхнего уровня (те, что непосредственно выполняются клиентами), со значениемallтакже отслеживаются вложенные операторы (например, операторы, вызываемые внутри функций) с уровнем вложенности не более 100, значениеnoneполностью отключает сбор статистики по операторам. Значение по умолчанию —top. Изменять этот параметр могут только суперпользователи.pgpro_stats.track_utility(boolean) #Параметр
pgpro_stats.track_utilityопределяет, будет ли этот модуль отслеживать служебные команды. Служебными командами считаются команды, отличные отSELECT,INSERT,UPDATEиDELETE. Значение по умолчанию —on(вкл.). Изменить этот параметр могут только суперпользователи.pgpro_stats.track_planning(boolean) #Параметр
pgpro_stats.track_planningопределяет, будет ли этот модуль отслеживать операции планирования и их длительность. Включение этого параметра может привести к заметному снижению производительности, особенно когда во множестве одновременных сеансов выполняются операторы с одинаковой структурой запросов, в результате чего эти сеансы пытаются одновременно изменить одни и те же записи вpgpro_stats_statements. Значение по умолчанию —off(выкл.). Изменить этот параметр могут только суперпользователи.pgpro_stats.save(boolean) #Параметр
pgpro_stats.saveопределяет, должна ли статистика операторов сохраняться после перезагрузки сервера. Если он отключён (имеет значениеoff), статистика не сохраняется при остановке сервера и не перезагружается при запуске. Значение по умолчанию —on(вкл.). Этот параметр можно задать только вpostgresql.confили в командной строке сервера.pgpro_stats.plan_format(text) #Параметр
pgpro_stats.plan_formatвыбирает формат плана запроса в выводеEXPLAIN. Возможные значения:text,xml,jsonиyaml. Значение по умолчанию:text. При изменении этого параметра требуется перезапустить сервер.
F.45.7.2.2. Параметры, связанные с pg_wait_sampling #
pgpro_stats.enable_profile(boolean) #Устарел. Вместо него используйте функциональность pg_wait_sampling, которая включается установкой для параметра pgpro_stats.pg_wait_sampling значения
true(по умолчанию). Параметрpgpro_stats.enable_profileвключает сбор образцов событий ожидания для отдельных операторов. Значение по умолчанию —true. При изменении этого параметра требуется перезапустить сервер.pgpro_stats.profile_period(integer) #Устарел. Вместо него используйте
pg_wait_sampling.profile_period.pgpro_stats.profile_periodзадаёт интервал (в миллисекундах) для сбора информации о событиях ожидания. Значение по умолчанию:10. Изменить этот параметр могут только суперпользователи.
F.45.8. Авторы #
Postgres Professional, Москва, Россия
F.45. pgpro_stats_2 — tracking planning and execution statistics of all SQL statements executed by the server (experimental) #
The pgpro_stats_2 extension provides statistics on the planning and execution of all SQL statements executed by the server. pgpro_stats_2 is actually a wrapper around the modified and improved pg_stat_statements and pg_wait_sampling statistical extensions and provides the following additional functionality:
storing query plans in addition to query statements
calculating wait event statistics for executed queries
pg_wait_sampling can be disabled using a configuration parameter.
Note
When the server gets shut down, pgpro_stats_2 saves the collected statistics to three dump files on disk in the format that depends on the number and order of columns in the pgpro_stats_statements view. If this format has changed in a new pgpro_stats_2 version, during the first server restart after upgrading the extension, the saved statistics will fail to be read with an error reported to the log:
LOG: pgpro_stats_2: could not read file "pg_stat/pg_stat_statements_Q.stat"
or
LOG: pgpro_stats_2: could not read file "pg_stat/pg_stat_statements_P.stat"
or
LOG: pgpro_stats_2: could not read file "pg_stat/pg_stat_statements_S.stat"
The contents of the files will be ignored, and respective statistics will be nullified. To learn whether the format has changed, see the Release Notes for pgpro_stats_2 version being installed. To retain the previously collected statistics, download them as CSV files before upgrading.
F.45.1. Limitations #
pgpro_stats_2can sometimes fail to match identical parameters in the query statement and the corresponding query plan.Some SPI queries are not included into statistics.
Texts and plans of some SPI queries are not normalized.
pgpro_stats_2is incompatible with pg_stat_statements, pg_wait_sampling, pgpro_stats, as well as other extensions that use parser, planner, or executor hooks to modify parse and plan trees and execution of the queries. Moreover, ifpgpro_stats_2and any of the above extensions are on the list ofshared_preload_libraries, the database server will not start. Note also that in order to dump the final versions of the queries and plans,pgpro_stats_2should be the last on the list ofshared_preload_libraries, but some existing extensions may not work at all unless they are the last on this list.pgpro_stats_2may not work correctly with third-party extensions that produceCustomScanandForeignScannodes.
F.45.2. Limitations of the Current Version #
Currently, pgpro_pwr and Postgres Pro Enterprise Manager do not support integration with
pgpro_stats_2and do not use the data it collects.Queries against the
pg_wait_sampling_statementsandpg_wait_sampling_objectsviews are slow, but this does not affect the server performance.
F.45.3. Installation and Setup #
pgpro_stats_2 is provided with Postgres Pro Standard as a separate pre-built package pgpro-stats-std-18 (for the detailed installation instructions, see Chapter 16). Once you have pgpro_stats_2 installed, complete the following steps to enable pgpro_stats_2:
Add
pgpro_stats_2to the shared_preload_libraries parameter in thepostgresql.conffile:shared_preload_libraries = 'pgpro_stats_2'
Restart the Postgres Pro Standard instance for the changes to take effect.
Once the server is reloaded,
pgpro_stats_2starts tracking statistics across all databases of the cluster. If required, you can change the scope of statistics collection or disable it altogether usingpgpro_stats_2configuration parameters.To access the collected statistics, you have to create the
pgpro_stats_2extension:CREATE EXTENSION pgpro_stats_2;
In addition, query identifier and query plan identifier calculation must be enabled in order for pgpro_stats_2 to be active, which is done automatically if compute_query_id and compute_plan_id are set to auto or on, or any third-party module that calculates query identifiers and plan identifiers is loaded.
F.45.4. Usage #
F.45.4.1. Collecting Statistics on Query Statements and Plans #
Once installed, the pgpro_stats_2 extension starts collecting statistics on the executed statements. The collected data is similar to the one provided by pg_stat_statements, but also includes information on query plans and wait events for each query type. The statistics is saved into memory and is accessible through the pgpro_stats_statements view.
To collect statistics on wait events, pgpro_stats_2 uses time-based sampling. Wait events are sampled at the time interval specified by the pgpro_stats.profile_period parameter, which is set to 10ms by default. If the sampling shows that the process is waiting, the pgpro_stats.profile_period value is added to the wait event duration. Thus, time estimation for each wait event remains valid even if the pgpro_stats.profile_period parameter value has changed. If you are not interested in wait event statistics, you can disable wait event sampling by setting the pgpro_stats.pg_wait_sampling parameter to false.
pgpro_stats_statements.plans and pgpro_stats_statements.calls are not always expected to match because planning and execution statistics are updated at their respective end phase, and only for successful operations. For example, if a statement is successfully planned but fails during the execution phase, only its planning statistics will be updated. If planning is skipped because a cached plan is used, only its execution statistics will be updated.
As an example, let's create a table with some random data and build an index on this table:
CREATE TABLE test AS (SELECT i, random() x FROM generate_series(1,1000000) i); CREATE INDEX test_x_idx ON test (x);
Now run the following query several times using different values for :x_min and :x_max:
select * from test where x >= :x_min and x <= :x_max;
The collected statistics should appear in the pgpro_stats_statements view:
postgres=# SELECT queryid, query, planid, plan FROM pg_stats_statements WHERE query LIKE 'select * from test where%';
-[ RECORD 1 ]----------------------------------------------------
queryid | -1730632209943640345
query | select * from test where x >= $1 AND x <= $2
planid | -4215043083291098626
plan | Seq Scan on public.test +
| Output: i, x +
| Filter: ((test.x >= $1) AND (test.x <= $2)) +
| Query Identifier: -1730632209943640345 +
|
-[ RECORD 2 ]----------------------------------------------------
queryid | -1730632209943640345
query | select * from test where x >= $1 AND x <= $2
planid | -8025285020772903731
plan | Bitmap Heap Scan on public.test +
| Output: i, x +
| Recheck Cond: ((test.x >= $1) AND (test.x <= $2)) +
| -> Bitmap Index Scan on test_x_idx +
| Index Cond: ((test.x >= $1) AND (test.x <= $2))+
| Query Identifier: -1730632209943640345 +
|
-[ RECORD 3 ]----------------------------------------------------
queryid | -1730632209943640345
query | select * from test where x >= $1 AND x <= $2
planid | 6075533348212423849
plan | Index Scan using test_x_idx on public.test +
| Output: i, x +
| Index Cond: ((test.x >= $1) AND (test.x <= $2)) +
| Query Identifier: -1730632209943640345 +
F.45.5. Views #
F.45.5.1. The pgpro_stats_statements View #
The statistics gathered by the module are available in the pgpro_stats_statements view. This view contains one row for each distinct database ID, user ID, query ID, and plan ID (up to the maximum number of distinct statements that the module can track). The columns of the view are shown in Table F.29.
Table F.29. pgpro_stats_statements Columns
| Name | Type | References | Description |
|---|---|---|---|
userid | oid | | OID of user who executed the statement |
dbid | oid | | OID of database in which the statement was executed |
toplevel | bool | true if the query was executed as a top-level statement (always true if pgpro_stats.track is set to top) | |
queryid | bigint | Hash code to identify the normalized query | |
planid | bigint | Hash code to identify the normalized statement's plan | |
query | text | Text of a representative statement | |
plan | text | The text of the query plan, in the format defined by the pgpro_stats.plan_format configuration parameter | |
plans | int8 | Number of times the statement was planned (if pgpro_stats.track_planning is enabled, otherwise zero) | |
total_plan_time | float8 | Total time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero) | |
min_plan_time | float8 | Minimum time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero) | |
max_plan_time | float8 | Maximum time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero) | |
mean_plan_time | float8 | Mean time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero) | |
stddev_plan_time | float8 | Population standard deviation of time spent planning the statement, in milliseconds (if pgpro_stats.track_planning is enabled, otherwise zero) | |
calls | int8 | Number of times the statement was executed | |
total_exec_time | float8 | Total time spent executing the statement, in milliseconds | |
min_exec_time | float8 | Minimum time spent executing the statement, in milliseconds | |
max_exec_time | float8 | Maximum time spent executing the statement, in milliseconds | |
mean_exec_time | float8 | Mean time spent executing the statement, in milliseconds | |
stddev_exec_time | float8 | Population standard deviation of time spent executing the statement, in milliseconds | |
rows | int8 | Total number of rows retrieved or affected by the statement | |
shared_blks_hit | int8 | Total number of shared block cache hits by the statement | |
shared_blks_read | int8 | Total number of shared blocks read by the statement | |
shared_blks_dirtied | int8 | Total number of shared blocks dirtied by the statement | |
shared_blks_written | int8 | Total number of shared blocks written by the statement | |
local_blks_hit | int8 | Total number of local block cache hits by the statement | |
local_blks_read | int8 | Total number of local blocks read by the statement | |
local_blks_dirtied | int8 | Total number of local blocks dirtied by the statement | |
local_blks_written | int8 | Total number of local blocks written by the statement | |
temp_blks_read | int8 | Total number of temp blocks read by the statement | |
temp_blks_written | int8 | Total number of temp blocks written by the statement | |
shared_blk_read_time | float8 | Total time the statement spent reading shared blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
shared_blk_write_time | float8 | Total time the statement spent writing shared blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
local_blk_read_time | float8 | Total time the statement spent reading local blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 17, contains zero | |
local_blk_write_time | float8 | Total time the statement spent writing local blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 17, contains zero | |
temp_blk_read_time | float8 | Total time the statement spent reading temp blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 15, contains zero | |
temp_blk_write_time | float8 | Total time the statement spent writing temp blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). In Postgres Pro versions lower than 15, contains zero | |
wal_records | int8 | Total number of WAL records generated by the statement | |
wal_fpi | int8 | Total number of WAL full page images generated by the statement | |
wal_bytes | numeric | Total amount of WAL bytes generated by the statement | |
wal_buffers_full | bigint | Number of times the WAL buffers became full. In Postgres Pro versions lower than 18, contains zero | |
jit_functions | int8 | Total number of functions JIT-compiled by the statement. In Postgres Pro versions lower than 15, contains zero | |
jit_generation_time | float8 | Total time spent by the statement on generating JIT code, in milliseconds. In Postgres Pro versions lower than 15, contains zero | |
jit_inlining_count | int8 | Number of times functions used in the statement have been inlined. In Postgres Pro versions lower than 15, contains zero | |
jit_inlining_time | float8 | Total time spent by the statement on inlining functions, in milliseconds. In Postgres Pro versions lower than 15, contains zero | |
jit_optimization_count | int8 | Number of times the statement has been optimized. In Postgres Pro versions lower than 15, contains zero | |
jit_optimization_time | float8 | Total time spent by the statement on JIT optimization, in milliseconds. In Postgres Pro versions lower than 15, contains zero | |
jit_emission_count | int8 | Number of times code has been emitted by the statement. In Postgres Pro versions lower than 15, contains zero | |
jit_emission_time | float8 | Total time spent by the statement on emitting code, in milliseconds. In Postgres Pro versions lower than 15, contains zero | |
jit_deform_count | int8 | Total number of tuple deform functions JIT-compiled by the statement. In Postgres Pro versions lower than 17, contains zero | |
jit_deform_time | float8 | Total time spent by the statement on JIT-compiling tuple deform functions, in milliseconds. In Postgres Pro versions lower than 17, contains zero | |
parallel_workers_to_launch | bigint | Number of parallel workers planned to be launched. In Postgres Pro versions lower than 18, contains zero | |
parallel_workers_launched | bigint | Number of parallel workers actually launched. In Postgres Pro versions lower than 18, contains zero | |
stats_since | timestamp with time zone | Time at which statistics gathering started for this statement | |
minmax_stats_since | timestamp with time zone | Time at which min/max statistics gathering started for this statement (fields min_plan_time, max_plan_time, min_exec_time and max_exec_time) |
Note
Statistics on wait events is available in the integrated pg_wait_sampling extension if pgpro_stats.pg_wait_sampling is enabled.
Take into account that like pg_stat_statements, pgpro_stats_2 normalizes into one record those DML queries (containing SELECT, INSERT, UPDATE, DELETE and MERGE commands) that have equivalent structures according to some internal hash value. Being compared this way, two queries are normally considered equal if they are semantically equivalent up to constants included in the queries. When the value of a constant in a query is ignored for comparison with other queries, this constant is replaced in the pgpro_stats_2 output with a symbol of a parameter, such as, $k, where k is a positive integer. If a query already contains parameters, the initial value of k equals the number following the last number of a $n parameter in the original query text. If there are no parameters, the initial value of k equals 1. Note that sometimes hidden parameter symbols affect this numbering. For example, PL/pgSQL uses such hidden symbols to insert values of function local variables into queries, so a PL/pgSQL statement SELECT i + 1 INTO j will be represented as SELECT i + $2 in the normalized query text.
In some cases, queries with visibly different texts might get merged into a single pgpro_stats_statements entry; as explained above, this is expected to happen for semantically equivalent queries. In addition, if the only difference between queries is the number of elements in a list of constants, the list will get squashed down to a single element but shown with a commented-out list indicator:
=# SELECT pgpro_stats_statements_reset(); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7); =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8); =# SELECT query, calls FROM pgpro_stats_statements WHERE query LIKE 'SELECT%'; -[ RECORD 1 ]------------------------------ query | SELECT * FROM test WHERE a IN ($1 /*, ... */) calls | 2
In addition to these cases, there is a small chance of hash collisions causing unrelated queries to be merged into one entry. (This cannot happen for queries belonging to different users or databases, however.)
pgpro_stats_2 uses a similar technique to normalize plan texts. When doing so, an attempt is made to associate numbers of constants in the plan text with the corresponding numbers of constants in the query text. If such an attempt appears unsuccessful for a certain constant in the plan text, it is assigned the number following the maximum number of a constant replaced in the query text. For example, consider the query:
SELECT 1::int, 'abc'::VARCHAR(3), 2::int;
pgpro_stats_2 will replace numbers of constants in the query text and in the text of the corresponding plan as follows:
postgres=# SELECT query, plan FROM pgpro_stats_statements;
query | plan
------------------------------------------------+--------------------------------------------------
SELECT $1::int, $2::VARCHAR(3), $3::int | Result +
| Output: $1, $4, $3 +
In this plan text, it appeared possible to associate constants numbered 1 and 3 from the query text, but not the constant numbered 2, and the latter was replaced with the number following the maximum number in the query text, that is, number 4.
Replacement of numbers in plan texts has an exception for version numbers of XML documents. If in the original query such a number is represented with a constant, e.g., '1.0', it is retained as is in the plan text rather than replaced with $k. If the version number of an XML document is represented with an expression, replacement of constants follows usual rules.
F.45.5.2. The pgpro_stats_info View #
The statistics of the pgpro_stats_2 module itself are tracked and made available in the pgpro_stats_info view. This view contains only a single row. The columns of the view are shown in Table F.30.
Table F.30. pgpro_stats_info Columns
| Name | Type | Description |
|---|---|---|
dealloc | bigint | Total number of times pgpro_stats_statements entries about the least-executed statements were deallocated because more distinct statements than pgpro_stats.max were observed |
stats_reset | timestamp with time zone | Time at which all statistics in the pgpro_stats_statements view were last reset |
F.45.6. Functions #
-
pgpro_stats_statements_reset(userid Oid, dbid Oid, queryid bigint, planid bigint, minmax_only boolean) returns timestamp with time zone pgpro_stats_statements_resetdiscards statistics gathered so far bypgpro_stats_2corresponding to the specifieduserid,dbid,queryid, andplanid. If any of the parameters are not specified, the default value0(invalid) is used for each of them and the statistics that match with other parameters will be reset. If no parameter is specified or all the specified parameters are0(invalid), it will discard all statistics. If all statistics in thepgpro_stats_statementsview are discarded, it will also reset the statistics in thepgpro_stats_infoview. Whenminmax_onlyistrue, only the values of minimum and maximum planning and execution time will be reset (i.e.min_plan_time,max_plan_time,min_exec_time, andmax_exec_timefields). The default value for theminmax_onlyparameter isfalse. Time of last min/max reset performed is shown inminmax_stats_sincefield of thepgpro_stats_statementsview. This function returns the time of a reset. This time is saved to thestats_resetfield of thepgpro_stats_infoview or to theminmax_stats_sincefield of thepgpro_stats_statementsview if the corresponding reset was actually performed. By default, this function can only be executed by superusers. Access may be granted to others usingGRANT.-
pgpro_stats_statements(showtext boolean) returns setof record The
pgpro_stats_statementsview is defined in terms of a function also namedpgpro_stats_statements. Users can also call thepgpro_stats_statementsfunction directly, and by specifyingshowtext := falsemake query text be omitted (that is, theOUTargument that corresponds to thequerycolumn of the view will return nulls). This feature is intended to support external tools that might wish to avoid the overhead of repeatedly retrieving query texts of indeterminate length. Such tools can instead cache the first query text observed for each entry themselves, since that is allpgpro_stats_2itself does, and then retrieve query texts only as needed. Since the server stores query texts in a file, this approach may reduce physical I/O for repeated examination of thepgpro_stats_statementsdata.-
pgpro_stats_info() returns record pgpro_stats_infoview is defined in terms of a function also namedpgpro_stats_info. Users can also call thepgpro_stats_infofunction directly.
F.45.7. Configuration Parameters #
F.45.7.1. Extension Usage Enabling Settings #
-
pgpro_stats.pg_wait_sampling(boolean) # pgpro_stats.pg_wait_samplingenables the functionality of the integrated pg_wait_sampling extension. The default value istrue. Changing this parameter requires a server restart.
F.45.7.2. General Settings #
F.45.7.2.1. Settings Related to pg_stat_statements #
Note
All the configuration parameters described below can also be used with the pg_stat_statements prefix instead of pgpro_stats. For example: pg_stat_statements.max specifies the same configuration parameter as pgpro_stats.max.
-
pgpro_stats.max(integer) # pgpro_stats_2 only collects statistics about the most frequent queries. The less frequently a query appears during the server operation, the less probable its inclusion in the statistics. Rare queries are almost immediately evicted from the statistics by more frequent queries. The
pgpro_stats.maxparameter defines the maximum number of unique pairs (normalized query text, normalized plan text) tracked by the module, (i.e., the maximum number of rows in thepgpro_stats_statementsview). The larger this value, the larger the number of queries for which the information is stored. But this is achieved at the cost of reduced server performance when waiting for locks trying to access the statistics table in the shared memory and when periodically collecting garbage in the file with texts of queries and plans. The default value is 5000. This parameter can only be set at server start.-
pgpro_stats.track(enum) # pgpro_stats.trackcontrols which statements are counted by the module. Specifytopto track top-level statements (those issued directly by clients),allto also track nested statements (such as statements invoked within functions) with nesting level not greater than 100, ornoneto disable statement statistics collection. The default value istop. Only superusers can change this setting.-
pgpro_stats.track_utility(boolean) # pgpro_stats.track_utilitycontrols whether utility commands are tracked by the module. Utility commands are all those other thanSELECT,INSERT,UPDATE, andDELETE. The default value ison. Only superusers can change this setting.-
pgpro_stats.track_planning(boolean) # pgpro_stats.track_planningcontrols whether planning operations and duration are tracked by the module. Enabling this parameter may incur a noticeable performance penalty, especially when statements with identical query structure are executed by many concurrent connections which compete to update a small number ofpgpro_stats_statementsentries. The default value isoff. Only superusers can change this setting.-
pgpro_stats.save(boolean) # pgpro_stats.savespecifies whether to save statement statistics across server shutdowns. If it isoffthen statistics are neither saved at shutdown nor reloaded at server start. The default value ison. This parameter can only be set in thepostgresql.conffile or on the server command line.-
pgpro_stats.plan_format(text) # pgpro_stats.plan_formatselects theEXPLAINformat for the query plan. Possible values aretext,xml,json, andyaml. The default value istext. Changing this parameter requires a server restart.
F.45.7.2.2. Settings Related to pg_wait_sampling #
-
pgpro_stats.enable_profile(boolean) # Deprecated. Instead, use the pg_wait_sampling functionality, which is enabled by setting pgpro_stats.pg_wait_sampling to
true(default).pgpro_stats.enable_profileenables sampling of wait events for separate statements. The default value istrue. Changing this parameter requires a server restart.-
pgpro_stats.profile_period(integer) # Deprecated. Use
pg_wait_sampling.profile_periodinstead.pgpro_stats.profile_periodspecifies the period, in milliseconds, during which to sample wait events. The default value is10. Only superusers can change this setting.
F.45.8. Authors #
Postgres Professional, Moscow, Russia