1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
4 <TITLE>pg_store_plans</TITLE>
10 HTTP-EQUIV="Content-Type"
11 CONTENT="text/html; charset=ISO-8859-1">
17 <A NAME="PGSTOREPLANS">pg_store_plans</A>
19 <P>The <TT CLASS="FILENAME">pg_store_plans</TT> module provides a
20 means for tracking execution plan statistics of all SQL statements
24 <P>The module must be loaded by
25 adding <TT CLASS="LITERAL">pg_store_plans</TT> to
26 <A HREF="http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-SHARED-PRELOAD-LIBRARIES">shared_preload_libraries</A> in
27 <TT CLASS="FILENAME">postgresql.conf</TT>, because it requires
28 additional shared memory. This means that a server restart is
29 required to add or remove the module.
32 <H2 CLASS="SECT2">1. The <TT CLASS="STRUCTNAME">pg_store_plans</TT>
35 <P>The statistics gathered by the module are available via a system
36 view named <TT CLASS="STRUCTNAME">pg_store_plans</TT>. This view
37 contains one row for each distinct set of database ID, user ID and
38 query ID. The columns of the view are described in
39 <A HREF="#PGSTOREPLANS-COLUMNS">Table 1</A>.
43 <A NAME="PGSTOREPLANS-COLUMNS"></A>
44 <P><B>Table 1. <TT>pg_store_plans</TT> Columns</B>
46 <TABLE BORDER="1" CLASS="CALSTABLE" >
48 <THEAD><TR><TH>Name</TH><TH>Type</TH><TH>References</TH>
49 <TH>Description</TH></TR>
52 <TR><TD><TT CLASS="STRUCTFIELD">userid</TT></TD>
53 <TD><TT CLASS="TYPE">oid</TT></TD>
54 <TD><A HREF="http://www.postgresql.org/docs/current/static/catalog-pg-authid.html">
55 <TT CLASS="STRUCTNAME">pg_authid</TT><TT CLASS="LITERAL"></A>.oid</TT></TD>
56 <TD>OID of user who executed the statement</TD></TR>
57 <TR><TD><TT CLASS="STRUCTFIELD">dbid</TT></TD>
58 <TD><TT CLASS="TYPE">oid</TT></TD>
59 <TD><A HREF="http://www.postgresql.org/docs/current/static/catalog-pg-database.html">
60 <TT CLASS="STRUCTNAME">pg_database</TT></A><TT CLASS="LITERAL">.oid</TT></TD>
61 <TD>OID of database in which the statement was executed</TD></TR>
62 <TR><TD><TT CLASS="STRUCTFIELD">queryid</TT></TD>
63 <TD><TT CLASS="TYPE">bigint</TT></TD>
65 <TD>Internal hash code, computed from the statement's query string.</TD>
67 <TR><TD><TT CLASS="STRUCTFIELD">planid</TT></TD>
68 <TD><TT CLASS="TYPE">bigint</TT></TD>
70 <TD>Internal hash code, computed from the statement's plan
73 <TR><TD><TT CLASS="STRUCTFIELD">queryid_stat_statements</TT></TD>
74 <TD><TT CLASS="TYPE">bigint</TT></TD>
76 <TD>A copy of pg_stat_statements' query hash code. This is
77 available only when pg_stat_statements is installed.</TD>
79 <TR><TD><TT CLASS="STRUCTFIELD">plan</TT></TD>
80 <TD><TT CLASS="TYPE">text</TT></TD>
82 <TD>Text of a representative plan. The format is specified by the
84 parameter <TT CLASS="VARNAME">pg_store_plans.plan_format.</TT></TD>
86 <TR><TD><TT CLASS="STRUCTFIELD">calls</TT></TD>
87 <TD><TT CLASS="TYPE">bigint</TT></TD>
89 <TD>Number of times executed</TD>
91 <TR><TD><TT CLASS="STRUCTFIELD">total_time</TT></TD>
92 <TD><TT CLASS="TYPE">double precision</TT></TD>
94 <TD>Total time spent in the statement using the plan, in milliseconds</TD>
96 <TR><TD><TT CLASS="STRUCTFIELD">rows</TT></TD>
97 <TD><TT CLASS="TYPE">bigint</TT></TD>
99 <TD>Total number of rows retrieved or affected by the statement
102 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_hit</TT></TD>
103 <TD><TT CLASS="TYPE">bigint</TT></TD>
105 <TD>Total number of shared block cache hits by the statement using
108 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_read</TT></TD>
109 <TD><TT CLASS="TYPE">bigint</TT></TD>
111 <TD>Total number of shared blocks read by the statement using the
114 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_dirtied</TT></TD>
115 <TD><TT CLASS="TYPE">bigint</TT></TD>
117 <TD>Total number of shared blocks dirtied by the statement using
120 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_written</TT></TD>
121 <TD><TT CLASS="TYPE">bigint</TT></TD>
123 <TD>Total number of shared blocks written by the statement using
126 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_hit</TT></TD>
127 <TD><TT CLASS="TYPE">bigint</TT></TD>
129 <TD>Total number of local block cache hits by the statement using
132 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_read</TT></TD>
133 <TD><TT CLASS="TYPE">bigint</TT></TD>
135 <TD>Total number of local blocks read by the statement using the
138 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_dirtied</TT></TD>
139 <TD><TT CLASS="TYPE">bigint</TT></TD><TD> </TD>
140 <TD>Total number of local blocks dirtied by the statement using
143 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_written</TT></TD>
144 <TD><TT CLASS="TYPE">bigint</TT></TD>
146 <TD>Total number of local blocks written by the statement using
149 <TR><TD><TT CLASS="STRUCTFIELD">temp_blks_read</TT></TD>
150 <TD><TT CLASS="TYPE">bigint</TT></TD>
152 <TD>Total number of temp blocks read by the statement using the
155 <TR><TD><TT CLASS="STRUCTFIELD">temp_blks_written</TT></TD>
156 <TD><TT CLASS="TYPE">bigint</TT></TD>
158 <TD>Total number of temp blocks written by the statement using the
161 <TR><TD><TT CLASS="STRUCTFIELD">blk_read_time</TT></TD>
162 <TD><TT CLASS="TYPE">double precision</TT></TD>
165 <TD>Total time the statement using the plan spent reading blocks, in milliseconds (if <A HREF="http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</A> is enabled, otherwise zero)</TD>
167 <TR><TD><TT CLASS="STRUCTFIELD">blk_write_time</TT></TD>
168 <TD><TT CLASS="TYPE">double precision</TT></TD>
171 <TD>Total time the statement using the plan spent writing blocks, in milliseconds (if <A HREF="http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-IO-TIMING">track_io_timing</A> is enabled, otherwise zero)</TD>
173 <TR><TD><TT CLASS="STRUCTFIELD">first_call</TT></TD>
174 <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
176 <TD>Timestamp for the least recently call of the query using this
179 <TR><TD><TT CLASS="STRUCTFIELD">last_call</TT></TD>
180 <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
182 <TD>Timestamp for the most recently call of the query using this
189 <P>This view, and the functions <CODE CLASS="FUNCTION">pg_store_plans_reset
190 </CODE> and <CODE CLASS="FUNCTION">pg_store_plans</CODE> and other
191 auxiliary functions, are available only in databases where
192 the <TT CLASS="LITERAL">pg_store_plans</TT> is installed
193 by <TT CLASS="LITERAL">CREATE EXTENSION</TT>. However,
194 statistics are tracked across all databases of the server whenever
195 the <TT CLASS="FILENAME">pg_store_plans</TT> module is loaded onto
196 the server, regardless of presence of the view.
199 <P>For security reasons, non-superusers are not allowed to see the
200 plan representation, queryid or planid for the queries executed by
206 <TT CLASS="VARNAME">queryid</TT> is calculated to identify the source
207 query similary to <TT CLASS="structname">pg_stat_statements</TT> but
208 in a different algorithm. <TT CLASS="VARNAME">plan</TT> is
209 calculated in a similar way. Two plans are considered the same if they
210 are seemingly equivalent except for the values of literal constants
211 or fluctuating values such like costs or measured time. </P>
213 <P> For PostgreSQL 9.4 or later, you can find the corresponding query
214 for a <TT CLASS="structname">pg_store_plans</TT> entry
215 in <TT CLASS="structname">pg_stat_statements</TT> by joining using
216 <TT CLASS="structname">queryid_stat_statements</TT>. Otherwise it is
217 identified by using <TT CLASS="VARNAME">queryid</TT>
218 and <CODE CLASS="FUNCTION">pg_store_plans_hash_query</CODE >, like
223 <PRE CLASS="SCREEN">SELECT s.query, p.plan FROM pg_store_plans p JOIN pg_stat_statements s ON (pg_store_plans_hash_query(s.query)) = p.queryid;</PRE>
226 <P> However plan id is calculated ignoring fluctuating values, the
227 values for most recent execution are still displayed
228 in <TT CLASS="STRUCTNAME">pg_store_plans.plan</TT>.
230 <P> In some cases, <TT CLASS="STRUCTNAME">pg_stat_statements</TT>
231 merges semantically equivalent queries which are considered
233 <TT CLASS="STRUCTNAME">pg_stat_statements</TT>. In the cases
234 correspondent in <TT CLASS="STRUCTNAME">pg_stat_statements</TT> might
235 not be found, but there is a small chance that this happenes. In
236 contrast, there also is a small chance that some queries might be
237 regarded as equivalent and merged into one entry
238 in <TT CLASS="STRUCTNAME">pg_store_plans</TT> but differentiated
239 in <TT CLASS="STRUCTNAME">pg_stat_statements</TT> mainly for utility
243 <P><TT CLASS="STRUCTNAME">pg_store_plans</TT>
244 and <TT CLASS="STRUCTNAME">pg_stat_statements</TT> maintain thier
245 entries individually so there is certain unavoidable chance
246 especially for entries with low execution frequency that no
247 correspondent is found.
249 <P><TT CLASS="STRUCTFIELD">queryid_stat_statements</TT> has the same
250 restriction to <TT CLASS="STRUCTNAME">pg_stat_statements</TT> in
251 terms of stability. Although <TT CLASS="STRUCTFIELD">queryid</TT>
252 and <TT CLASS="STRUCTFIELD">planid</TT>
253 in <TT CLASS="STRUCTNAME">pg_store_plans</TT> doesn't have such a
254 restriction, assuming long-term stability is also discouraged.
259 <A NAME="Functions">2. Functions</A>
261 <DIV CLASS="VARIABLELIST">
262 <DL> <DT> <CODE CLASS="FUNCTION">pg_store_plans_reset() returns void</CODE>
266 <CODE CLASS="FUNCTION">pg_store_plans_reset</CODE> discards all
267 statistics gathered so far
268 by <TT CLASS="FILENAME">pg_store_plans</TT>. By default, only
269 superusers can execute this function.
273 <CODE CLASS="FUNCTION">pg_store_plans(showtext boolean) returns setof
277 <P> The <TT CLASS="STRUCTNAME">pg_store_plans</TT> view is defined in
278 terms of a function also
279 named <CODE CLASS="FUNCTION">pg_store_plans</CODE >.
283 <CODE CLASS="FUNCTION">pg_store_hash_query(query text) returns oid</CODE>
286 <P> This function calculates hash value of a query text. The same
288 calculate <TT CLASS="STRUCTFIELD">queryid</TT>
289 in <TT CLASS="STRUCTNAME">pg_store_plans</TT> so this function
291 with <TT CLASS="STRUCTNAME">pg_store_plans</TT>.
295 <CODE CLASS="FUNCTION">pg_store_plans_textplan(query text) returns text</CODE>
298 <P> This function generates a ordinary text representation from raw
299 representation of <TT CLASS="STRUCTFIELD">plan</TT>
300 in <TT CLASS="STRUCTNAME">pg_store_plans</TT>, which is shown
301 there when <TT CLASS="VARNAME">pg_store_plans.plan_formats</TT>
302 = 'raw'. Since the result plan text is generated from json
303 representation, it might be slightly different from what you
304 will get directly from 'EXPLAIN' commnand.
308 <CODE CLASS="FUNCTION">pg_store_plans_jsonplan(query text) returns text</CODE>
311 <P> This function infaltes a "short format json plan" or "raw format"
312 into normal json format. Short format json is internal format
313 for <TT CLASS="STRUCTFIELD">plan</TT>
314 in <TT CLASS="STRUCTNAME">pg_store_plans</TT>, which is shown
315 there when <TT CLASS="VARNAME">pg_store_plans.plan_formats</TT> =
320 <CODE CLASS="FUNCTION">pg_store_plans_xmlplan(query text) returns text</CODE>
323 <P> This function generates a XML representation from raw
324 representation of <TT CLASS="STRUCTFIELD">plan</TT>
325 in <TT CLASS="STRUCTNAME">pg_store_plans</TT>, which is shown
326 there when <TT CLASS="VARNAME">pg_store_plans.plan_formats</TT>
331 <CODE CLASS="FUNCTION">pg_store_plans_yamlplan(query text) returns text</CODE>
334 <P> This function generates a YAML representation from raw
335 representation of <TT CLASS="STRUCTFIELD">plan</TT>
336 in <TT CLASS="STRUCTNAME">pg_store_plans</TT>, which is shown
337 there when <TT CLASS="VARNAME">pg_store_plans.plan_formats</TT>
346 <ANAME="Config">3. Configuration Parameters</A>
350 <DIV CLASS="VARIABLELIST">
353 <TT CLASS="VARNAME">pg_store_plans.max</TT>
354 (<TT CLASS="TYPE">integer</TT>)</DT>
356 <P> <TT CLASS="VARNAME">pg_store_plans.max</TT> is the maximum number
357 of plans tracked by the module (i.e., the maximum number of rows in
358 the <TT CLASS="STRUCTNAME">pg_store_plans</TT> view). If more
359 distinct plans than that are observed, information about the
360 least-executed plan is discarded. The default value is 1000. This
361 parameter can only be set at server start.
365 <TT CLASS="VARNAME">pg_store_plans.track</TT>
366 (<TT CLASS="TYPE">enum</TT>)
369 <P> Similar to <TT CLASS="STRUCTNAME">pg_stat_statements</TT>,
370 <TT CLASS="VARNAME">pg_store_plans.track</TT> controls which
371 statements are counted by the module.
372 Specify <TT CLASS="LITERAL">top</TT> to track top-level
373 statements (those issued directly by
374 clients), <TT CLASS="LITERAL">all</TT> to also track nested
375 statements (such as statements invoked within functions),
376 or <TT CLASS="LITERAL">none</TT> to disable statement
377 statistics collection. The default value
378 is <TT CLASS="LITERAL">top</TT>. Only superusers can change
383 <TT CLASS="VARNAME">pg_store_plans.plan_format</TT>
384 (<TT CLASS="TYPE">enum</TT>)
387 <P> <TT CLASS="VARNAME">pg_store_plans.plan_format</TT> controls the
388 format of <TT CLASS="STRUCTFIELD">plans</TT>
389 in <TT CLASS="STRUCTNAME">pg_store_plans</TT>. <TT CLASS="LITERAL">text</TT>
390 is the default value and to show in ordinary text
391 representation, <TT CLASS="LITERAL">json</TT>, <TT CLASS="LITERAL">xml</TT>
392 and <TT CLASS="LITERAL">yaml</TT> to show in corresponding format.
393 <TT CLASS="LITERAL">raw</TT> to get internal representation which
394 can be fed to <CODE CLASS="FUNCTION">pg_store_plans_*plan</CODE >
399 <TT CLASS="VARNAME">pg_store_plans.min_duration</TT>
400 (<TT CLASS="TYPE">integer</TT>)
403 <P> <TT CLASS="VARNAME">pg_store_plans.min_duration</TT> is the
404 minumum statement execution time, in milliseconds, that will cause the
405 statement's plan to be logged. Setting this to zero (the default) logs
406 all plans. Only superuses can change this setting.
410 <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT>
411 (<TT CLASS="TYPE">boolean</TT>)
414 <P> <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT>
415 causes <TT CLASS="COMMAND">EXPLAIN ANALYZE</TT> output, rather than
416 just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be included
417 in <TT CLASS="STRUCTFIELD">plan</TT>. This parameter is off by
422 <TT CLASS="VARNAME">pg_store_plans.log_buffers</TT>
423 (<TT CLASS="TYPE">boolean</TT>)
426 <P> <TT CLASS="VARNAME">pg_store_plans.log_buffers</TT>
427 causes <TT CLASS="COMMAND">EXPLAIN (ANALYZE, BUFFERS)</TT> output,
428 rather than just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be
429 included in <TT CLASS="STRUCTFIELD">plan</TT>. This parameter is off
434 <TT CLASS="VARNAME">pg_store_plans.log_timing</TT>
435 (<TT CLASS="TYPE">boolean</TT>)</DT>
437 <P> Setting <TT CLASS="VARNAME">pg_store_plans.log_timing</TT> to
438 false disables to record actual timings. The overhead of repeatedly
439 reading the system clock can slow down the query significantly on
440 some systems, so it may be useful to set this parameter to FALSE
441 when only actual row counts, and not exact execution times for each
442 execution nodes, are needed. Run time of the entire statement is
444 when <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT> is
445 TRUE. It defaults to TRUE.
449 <TT CLASS="VARNAME">pg_store_plans.log_triggers</TT
450 > (<TT CLASS="TYPE">boolean</TT
453 <P> <TT CLASS="VARNAME">pg_store_plans.log_triggers</TT> causes
454 trigger execution statistics to be included in recoreded plans. This
455 parameter has no effect
456 unless <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT> is
461 <TT CLASS="VARNAME">pg_store_plans.verbose</TT>
462 (<TT CLASS="TYPE">boolean</TT>)
465 <P> <TT CLASS="VARNAME">pg_store_plans.verbose</TT>
466 causes <TT CLASS="COMMAND">EXPLAIN VERBOSE</TT> output, rather than
467 just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be included
468 in <TT CLASS="STRUCTFIELD">plan</TT>. This parameter is off by
473 <TT CLASS="VARNAME">pg_store_plans.save</TT>
474 (<TT CLASS="TYPE">boolean</TT>)
477 <P> <TT CLASS="VARNAME">pg_store_plans.save</TT> specifies whether to
478 save plan statistics across server shutdowns. If it
479 is <TT CLASS="LITERAL">off</TT> then statistics are not saved at
480 shutdown nor reloaded at server start. The default value
481 is <TT CLASS="LITERAL">on</TT>. This parameter can only be set in
482 the <TT CLASS="FILENAME">postgresql.conf</TT> file or on the server
488 <P> The module requires additional shared memory proportional to
489 <TT CLASS="VARNAME">pg_store_plans.max</TT>. Note that this memory
490 is consumed whenever the module is loaded, even if
491 <TT CLASS="VARNAME">pg_store_plans.track</TT> is set
492 to <TT CLASS="LITERAL">none</TT>.
494 <P> These parameters must be set in
495 <TT CLASS="FILENAME">postgresql.conf</TT>. Typical usage might be:
496 </P><PRE CLASS="PROGRAMLISTING"># postgresql.conf
497 shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
498 pg_store_plans.max = 10000
499 pg_store_plans.track = all</PRE>
505 <A NAME="Sample">4. Sample Output</A>
507 <PRE CLASS="SCREEN">(postgresql.conf has following settings)
508 shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
509 pg_store_plans.log_analyze = true
510 pg_store_plans.log_timing = false
512 bench=# SELECT pg_store_plans_reset();
515 $ pgbench -c10 -t3000 bench
518 bench=# SELECT s.query, p.plan,
519 p.calls as "plan calls", s.calls as "stmt calls",
520 p.total_time / p.calls as "time/call", p.first_call, p.last_call
521 FROM pg_stat_statements s
522 JOIN pg_store_plans p ON
523 (p.queryid = pg_store_plans_hash_query(s.query) and p.calls < s.calls)
524 ORDER BY query ASC, "time/call" DESC;
525 -[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------
526 query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
527 plan | Update on pgbench_branches (cost=0.00..8.01 rows=1 width=370) (actual rows=0 loops=1)
528 | -> Seq Scan on pgbench_branches (cost=0.00..8.01 rows=1 width=370) (actual rows=1 loops=1)
532 time/call | 40.096513957518
533 first_call | 2014-04-25 14:29:17.163924+09
534 last_call | 2014-04-25 14:31:29.421635+09
535 -[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------
536 query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
537 plan | Update on pgbench_branches (cost=0.12..8.14 rows=1 width=370) (actual rows=0 loops=1)
538 | -> Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.12..8.14 rows=1 width=370) (actual rows=1 loops=1)
539 | Index Cond: (bid = 1)
542 time/call | 39.1920771311645
543 first_call | 2014-04-25 14:31:29.288913+09
544 last_call | 2014-04-25 14:33:31.287061+09
545 -[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------
546 query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
547 plan | Update on pgbench_tellers (cost=0.14..8.16 rows=1 width=358) (actual rows=0 loops=1)
548 | -> Index Scan using pgbench_tellers_pkey on pgbench_tellers (cost=0.14..8.16 rows=1 width=358) (actual rows=1 loops=1)
549 | Index Cond: (tid = 7)
553 first_call | 2014-04-25 14:30:37.850293+09
554 last_call | 2014-04-25 14:32:38.083977+09
555 -[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------
556 query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
557 plan | Update on pgbench_tellers (cost=4.14..8.16 rows=1 width=358) (actual rows=0 loops=1)
558 | -> Bitmap Heap Scan on pgbench_tellers (cost=4.14..8.16 rows=1 width=358) (actual rows=1 loops=1)
559 | Recheck Cond: (tid = 10)
560 | -> Bitmap Index Scan using pgbench_tellers_pkey (cost=0.00..4.14 rows=1 width=0) (actual rows=1 loops=1)
561 | Index Cond: (tid = 10)
564 time/call | 33.6455953793834
565 first_call | 2014-04-25 14:29:17.162871+09
566 last_call | 2014-04-25 14:33:31.28646+09</PRE>