The pg_store_plans module provides a means for tracking execution plan statistics of all SQL statements executed by a server.
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 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. plan 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 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. |
pg_store_plans_reset() returns void
pg_store_plans_reset discards all
statistics gathered so far
by pg_store_plans. By default, only
superusers can execute this function.
pg_store_plans(showtext boolean) returns setof
record
The pg_store_plans view is defined in
terms of a function also
named pg_store_plans.
pg_store_plans_info() returns record
pg_store_plans_info view is defined in terms of a function also named pg_store_plans_info.
pg_store_hash_query(query text) returns oid
This function calculates hash value of a query text. The same algorithm is used to calculate queryid in pg_store_plans so this function is usable to join with pg_store_plans.
pg_store_plans_textplan(query text) returns text
This function generates a ordinary text representation from raw representation of plan in pg_store_plans, which is shown there when pg_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 text
This function infaltes a "short format json plan" or "raw format" into normal json format. Short format json is internal format for plan in pg_store_plans, which is shown there when pg_store_plans.plan_formats = 'raw'.
pg_store_plans_xmlplan(query text) returns text
This function generates a XML representation from raw representation of plan in pg_store_plans, which is shown there when pg_store_plans.plan_formats = 'raw'.
pg_store_plans_yamlplan(query text) returns text
This function generates a YAML representation from raw representation of plan in pg_store_plans, which is shown there when pg_store_plans.plan_formats = 'raw'.
pg_store_plans.max is the maximum number of plans tracked by the module (i.e., the maximum number of rows in the pg_store_plans view). 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.
Similarly to pg_stat_statements, pg_store_plans.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions except for some commands, see below), or none to disable statement statistics collection. The default value is top. When all is specified, the commands executed under CREATE EXTENSION and ALTER EXTENSION commands are still ignored. Specify verbose to track all commands including ones excluded by all. Only superusers can change this setting.
pg_store_plans.max_plan_length is 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 specifies how plan texts are stored while server is running. If it is set to file, the plan texts are stored in a temporary file as pg_stat_statements does. shmem means to store plan texts on-memory. The default value is "file". See the discussion below for details.
pg_store_plans.plan_format controls the
format of plans
in pg_store_plans. text
is the default value and to show in ordinary text
representation, json, xml
and yaml to show in corresponding format.
raw to get internal representation which
can be fed to pg_store_plans_*plan
functions.
pg_store_plans.min_duration is 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 causes EXPLAIN ANALYZE output, rather than just EXPLAIN output, to be included in plan. This parameter is off by default.
pg_store_plans.log_buffers causes EXPLAIN (ANALYZE, BUFFERS) output, rather than just EXPLAIN output, to be included in plan. This parameter is off by default.
Setting pg_store_plans.log_timing to 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 when pg_store_plans.log_analyze is TRUE. It defaults to TRUE.
pg_store_plans.log_triggers causes trigger execution statistics to be included in recoreded plans. This parameter has no effect unless pg_store_plans.log_analyze is turned on.
pg_store_plans.verbose causes EXPLAIN VERBOSE output, rather than just EXPLAIN output, to be included in plan. This parameter is off by default.
pg_store_plans.save specifies whether to save plan statistics across server shutdowns. If it is off then statistics are not saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.
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
(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=#