F.51. pg_store_plans#
F.51. pg_store_plans #
F.51.2. Description #
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.
F.51.3. 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
Table F.26. Table 1. pg_store_plans
|
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.
F.51.4. The pg_store_plans 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. |
F.51.5. Functions #
-
pg_store_plans_reset() returns void pg_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 record The
pg_store_plansview is defined in terms of a function also namedpg_store_plans.-
pg_store_plans_info() returns record pg_store_plans_infoview is defined in terms of a function also namedpg_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
queryidinpg_store_plansso this function is usable to join withpg_store_plans.-
pg_store_plans_textplan(query text) returns text This 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 text This 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 text This 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 text This function generates a YAML representation from raw representation of
planinpg_store_plans, which is shown there whenpg_store_plans.plan_formats= 'raw'.
F.51.6. 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".-
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.-
pg_store_plans.store_last_plan(boolean) pg_store_plans.store_last_planspecifies whether tha text of plan should always be updated. Default isfalse.-
pg_store_plans.sample_rate(float) pg_store_plans.sample_ratespecifies fraction of queries to process. Under high loads, it allows to reduce the impact ofpg_store_planson performance. Default is0.0.-
pg_store_plans.slow_statement_duration(integer) pg_store_plans.slow_statement_durationspecifies unconditional record plan of slow statement. Default is0.
F.51.7. 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
F.51.8. Sample Output #
(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=#