February 20, 2024
Summary: The pg_store_plans module provides a means for tracking execution plan statistics of all SQL statements executed by a server.
Table of Contents
The module must be loaded by adding pg_store_plans to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is required to add or remove the module. pg_store_plans requires the GUC variable compute_query_id to be “on” or “auto”. If it is set to “no”, pg_store_plans is silently disabled.
The pg_store_plans View
The statistics gathered by the module are available via a system view named pg_store_plans. This view contains one row for each distinct set of database ID, user ID and query ID. The columns of the view are described in Table 1.
Table 1. pg_store_plans Columns
| Name | Type | References | Description |
|---|---|---|---|
userid |
oid |
pg_authid.oid |
OID of user who executed the statement |
dbid |
oid |
pg_database.oid |
OID of database in which the statement was executed |
queryid |
bigint |
Core-generated query ID. If compute_query_id is set to “no”, pg_store_plan is silently disabled. This is usable as the join key with pg_stat_statements. |
|
planid |
bigint |
Plan hash code, computed from the normalized plan representation. | |
plan |
text |
Text of a representative plan. The format is specified by the configuration parameter pg_store_plans.plan_format. |
|
calls |
bigint |
Number of times executed | |
total_time |
double precision |
Total time spent in the statement using the plan, in milliseconds | |
rows |
bigint |
Total number of rows retrieved or affected by the statement using the plan | |
shared_blks_hit |
bigint |
Total number of shared block cache hits by the statement using the plan | |
shared_blks_read |
bigint |
Total number of shared blocks read by the statement using the plan | |
shared_blks_dirtied |
bigint |
Total number of shared blocks dirtied by the statement using the plan | |
shared_blks_written |
bigint |
Total number of shared blocks written by the statement using the plan | |
local_blks_hit |
bigint |
Total number of local block cache hits by the statement using the plan | |
local_blks_read |
bigint |
Total number of local blocks read by the statement using the plan | |
local_blks_dirtied |
bigint |
Total number of local blocks dirtied by the statement using the plan | |
local_blks_written |
bigint |
Total number of local blocks written by the statement using the plan | |
temp_blks_read |
bigint |
Total number of temp blocks read by the statement using the plan | |
temp_blks_written |
bigint |
Total number of temp blocks written by the statement using the plan | |
blk_read_time |
double precision |
Total time the statement using the plan spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
blk_write_time |
double precision |
Total time the statement using the plan spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
temp_blk_read_time |
double precision |
Total time the statement using the plan spent reading temporary file blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
temp_blk_write_time |
double precision |
Total time the statement using the plan spent writing temporary file blocks, in milliseconds (if track_io_timing is enabled, otherwise zero) | |
first_call |
timestamp with time zone |
Timestamp for the least recently call of the query using this plan. | |
last_call |
timestamp with time zone |
Timestamp for the most recently call of the query using this plan. |
This view, and the functions pg_store_plans_reset and pg_store_plans and other auxiliary functions, are available only in databases where the pg_store_plans is installed by CREATE EXTENSION. However, statistics are tracked across all databases of the server whenever the pg_store_plans module is loaded onto the server, regardless of presence of the view.
For security reasons, non-superusers are not allowed to see the plan representation, queryid or planid for the queries executed by other users.
queryid is calculated to identify the source query similary to pg_stat_statements but in a different algorithm. planid is calculated in a similar way. Two plans are considered the same if they are seemingly equivalent except for the values of literal constants or fluctuating values such like costs or measured time.
For PostgreSQL 14 or later, you can find the corresponding query for a pg_store_plans entry in pg_stat_statements by joining using queryid, like the following.
SELECT s.query, p.plan FROM pg_store_plans p JOIN pg_stat_statements s USING (queryid);
Plan ID is calculated excluding fluctuating properties of plans. On the other hand, the pg_store_plans.plan view keeps showing the most recent values for those fluctuating properties.
pg_store_plans and pg_stat_statements maintain thier entries individually so there is certain unavoidable chance especially for entries with low execution frequency that no correspondent is found.
The pg_store_plans_info View
The statistics of pg_store_plans module itself are tracked and made available via a view named pg_store_plans_info. This view contains only a single row. The columns of the view are shown in Table 2.
Table 2. pg_store_plans_info Columns
| Name | Type | References | Description |
|---|---|---|---|
dealloc |
bigint |
Total number of times pg_store_plans entries about the least-executed statements were deallocated because more distinct statements than pg_store_plans.max were observed. | |
stats_reset |
timestamp with time zone |
Time at which all statistics in the pg_store_plans view were last reset. |
Functions
-
pg_store_plans_reset() returns voidpg_store_plans_resetdiscards all statistics gathered so far bypg_store_plans. By default, only superusers can execute this function. -
pg_store_plans(showtext boolean) returns setof recordThe
pg_store_plansview is defined in terms of a function also namedpg_store_plans. -
pg_store_plans_info() returns recordpg_store_plans_infoview is defined in terms of a function also namedpg_store_plans_info. -
pg_store_hash_query(query text) returns oidThis function calculates hash value of a query text. The same algorithm is used to calculate
queryidinpg_store_plansso this function is usable to join withpg_store_plans. -
pg_store_plans_textplan(query text) returns textThis function generates a ordinary text representation from raw representation of
planinpg_store_plans, which is shown there whenpg_store_plans.plan_formats= ‘raw’. Since the result plan text is generated from json representation, it might be slightly different from what you will get directly from ‘EXPLAIN’ commnand. -
pg_store_plans_jsonplan(query text) returns textThis function infaltes a “short format json plan” or “raw format” into normal json format. Short format json is internal format for
planinpg_store_plans, which is shown there whenpg_store_plans.plan_formats= ‘raw’. -
pg_store_plans_xmlplan(query text) returns textThis function generates a XML representation from raw representation of
planinpg_store_plans, which is shown there whenpg_store_plans.plan_formats= ‘raw’. -
pg_store_plans_yamlplan(query text) returns textThis function generates a YAML representation from raw representation of
planinpg_store_plans, which is shown there whenpg_store_plans.plan_formats= ‘raw’.
Configuration Parameters
-
pg_store_plans.max(integer)pg_store_plans.maxis the maximum number of plans tracked by the module (i.e., the maximum number of rows in thepg_store_plansview). If more distinct plans than that are observed, information about the least-executed plan is discarded. The default value is 1000. This parameter can only be set at server start. -
pg_store_plans.track(enum)Similarly to
pg_stat_statements,pg_store_plans.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 except for some commands, see below), ornoneto disable statement statistics collection. The default value istop. Whenallis specified, the commands executed underCREATE EXTENSIONandALTER EXTENSIONcommands are still ignored. Specifyverboseto track all commands including ones excluded byall. Only superusers can change this setting.pg_store_plans.max_plan_lengthis the maximum byte length of plans in the raw (shortened JSON) format to store. The plan text is truncated at the length if it is longer than that value. The default value is 5000. This parameter can only be set at server start. -
pg_store_plans.plan_storage(integer)pg_store_plans.plan_storagespecifies how plan texts are stored while server is running. If it is set tofile, the plan texts are stored in a temporary file as pg_stat_statements does.shmemmeans to store plan texts on-memory. The default value is “file”. See the discussion below for details. -
pg_store_plans.plan_format(enum)pg_store_plans.plan_formatcontrols the format ofplansinpg_store_plans.textis the default value and to show in ordinary text representation,json,xmlandyamlto show in corresponding format.rawto get internal representation which can be fed topg_store_plans_*planfunctions. -
pg_store_plans.min_duration(integer)pg_store_plans.min_durationis the minumum statement execution time, in milliseconds, that will cause the statement’s plan to be logged. Setting this to zero (the default) logs all plans. Only superuses can change this setting. -
pg_store_plans.log_analyze(boolean)pg_store_plans.log_analyzecausesEXPLAIN ANALYZEoutput, rather than justEXPLAINoutput, to be included inplan. This parameter is off by default. -
pg_store_plans.log_buffers(boolean)pg_store_plans.log_bufferscausesEXPLAIN (ANALYZE, BUFFERS)output, rather than justEXPLAINoutput, to be included inplan. This parameter is off by default. -
pg_store_plans.log_timing(boolean)Setting
pg_store_plans.log_timingto false disables to record actual timings. The overhead of repeatedly reading the system clock can slow down the query significantly on some systems, so it may be useful to set this parameter to FALSE when only actual row counts, and not exact execution times for each execution nodes, are needed. Run time of the entire statement is always measured whenpg_store_plans.log_analyzeis TRUE. It defaults to TRUE. -
pg_store_plans.log_triggers(boolean)pg_store_plans.log_triggerscauses trigger execution statistics to be included in recoreded plans. This parameter has no effect unlesspg_store_plans.log_analyzeis turned on. -
pg_store_plans.verbose(boolean)pg_store_plans.verbosecausesEXPLAIN VERBOSEoutput, rather than justEXPLAINoutput, to be included inplan. This parameter is off by default. -
pg_store_plans.save(boolean)pg_store_plans.savespecifies whether to save plan statistics across server shutdowns. If it isoffthen statistics are not 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.
Discussion on plan_storage setting
pg_store_plans claims additional shared memory proportional to pg_store_plans.max. When pg_store_plans.plan_storage is set to “shmem”, it claims further additional shared memory to store plan texts in an amount of the product of the maximum number of plans to store (pg_store_plans.max) and the maximum length of individual plan (pg_store_plans.max_plan_length). If pg_store_plans.plan_storage is set to “file”, plan texts are written to a temporary file as pg_stat_statements does. If pg_store_plans.max is not large enough to store all plans, pg_store_plans reclaims the space for new plans by evicting some portion of the entries. After several rounds of that eviction, pg_store_plans runs garbage collection on the temporary file, which might be painful for certain workloads. You can see how frequntly that eviction happens in pg_store_plans_info.dealloc.
If pg_store_plans.max is sufficiently large so that garbage collection doesn’t happen, “file” is recommended as pg_store_plans.plan_storage.
These parameters must be set in postgresql.conf. An example setting follows:
# postgresql.conf
shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
pg_store_plans.max = 10000
pg_store_plans.track = all
Sample Output
Let’s configure some extension’s parameters in postgresql.conf like the following:
# postgresql.conf
shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
pg_store_plans.log_analyze = true
pg_store_plans.log_timing = false
Joining the pg_stat_statements and pg_store_plans views can provide us with more insightful information about improving our PostgreSQL performance. First, we need to make sure to reset our usages of both views to discard all statistics gathered so far by both views:
SELECT pg_store_plans_reset();
SELECT pg_stat_statements_reset();
Then you can run the pgbench command:
$ pgbench -i bench
$ pgbench -c10 -t1000 bench
Then you can use the following query to join the two views:
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;
which outputs the following:
-[ 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