docs/index.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
   2 <HTML>
   3 <HEAD>
   4 <TITLE>pg_store_plans</TITLE>
   5 <LINK
   6 REL="STYLESHEET"
   7 TYPE="text/css"
   8 HREF="stylesheet.css">
   9 <META
  10 HTTP-EQUIV="Content-Type"
  11 CONTENT="text/html; charset=ISO-8859-1">
  12 </HEAD>
  13
  14 <BODY>
  15 <DIV CLASS="SECT1">
  16 <H1 CLASS="SECT1">
  17 <A NAME="PGSTOREPLANS">pg_store_plans</A>
  18 </H1>
  19 <P>The <TT CLASS="FILENAME">pg_store_plans</TT> module provides a
  20   means for tracking execution plan statistics of all SQL statements
  21   executed by a server.
  22 </P>
  23
  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
  30   module.  <TT CLASS="LITERAL">pg_store_plans</TT> requires the GUC
  31   variable <TT CLASS="LITERAL">compute_query_id</TT> to be "on" or
  32   "auto". If it is set to
  33   "no", <TT CLASS="LITERAL">pg_store_plans</TT> is silently disabled.
  34 </P>
  35 <DIV CLASS="SECT2">
  36 <H2 CLASS="SECT2">1. The <TT CLASS="STRUCTNAME">pg_store_plans</TT>
  37 View</H2>
  38
  39 <P>The statistics gathered by the module are available via a system
  40    view named <TT CLASS="STRUCTNAME">pg_store_plans</TT>.  This view
  41    contains one row for each distinct set of database ID, user ID and
  42    query ID.  The columns of the view are described in
  43    <A HREF="#PGSTOREPLANS-COLUMNS">Table 1</A>.
  44 </P>
  45
  46 <DIV CLASS="TABLE">
  47 <A NAME="PGSTOREPLANS-COLUMNS"></A>
  48 <P><B>Table 1. <TT>pg_store_plans</TT> Columns</B>
  49 </P>
  50 <TABLE BORDER="1" CLASS="CALSTABLE" >
  51 <COL><COL><COL><COL>
  52 <THEAD><TR><TH>Name</TH><TH>Type</TH><TH>References</TH>
  53        <TH>Description</TH></TR>
  54 </THEAD>
  55 <TBODY>
  56 <TR><TD><TT CLASS="STRUCTFIELD">userid</TT></TD>
  57     <TD><TT CLASS="TYPE">oid</TT></TD>
  58     <TD><A HREF="http://www.postgresql.org/docs/current/static/catalog-pg-authid.html">
  59         <TT CLASS="STRUCTNAME">pg_authid</TT><TT CLASS="LITERAL"></A>.oid</TT></TD>
  60     <TD>OID of user who executed the statement</TD></TR>
  61 <TR><TD><TT CLASS="STRUCTFIELD">dbid</TT></TD>
  62     <TD><TT CLASS="TYPE">oid</TT></TD>
  63     <TD><A HREF="http://www.postgresql.org/docs/current/static/catalog-pg-database.html">
  64         <TT CLASS="STRUCTNAME">pg_database</TT></A><TT CLASS="LITERAL">.oid</TT></TD>
  65     <TD>OID of database in which the statement was executed</TD></TR>
  66 <TR><TD><TT CLASS="STRUCTFIELD">queryid</TT></TD>
  67     <TD><TT CLASS="TYPE">bigint</TT></TD>
  68     <TD>&nbsp;</TD>
  69      <TD>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
  70     with <TT CLASS="LITERAL">pg_stat_statements</TT>.</TD></TD>
  71     </TR>
  72 <TR><TD><TT CLASS="STRUCTFIELD">planid</TT></TD>
  73     <TD><TT CLASS="TYPE">bigint</TT></TD>
  74     <TD>&nbsp;</TD>
  75     <TD>Plan hash code, computed from the normalized plan representation.
  76 </TR>
  77 <TR><TD><TT CLASS="STRUCTFIELD">plan</TT></TD>
  78     <TD><TT CLASS="TYPE">text</TT></TD>
  79     <TD>&nbsp;</TD>
  80     <TD>Text of a representative plan. The format is specified by the
  81     configuration
  82     parameter <TT CLASS="VARNAME">pg_store_plans.plan_format.</TT></TD>
  83     </TR>
  84 <TR><TD><TT CLASS="STRUCTFIELD">calls</TT></TD>
  85     <TD><TT CLASS="TYPE">bigint</TT></TD>
  86     <TD>&nbsp;</TD>
  87     <TD>Number of times executed</TD>
  88     </TR>
  89 <TR><TD><TT CLASS="STRUCTFIELD">total_time</TT></TD>
  90     <TD><TT CLASS="TYPE">double precision</TT></TD>
  91     <TD>&nbsp;</TD>
  92     <TD>Total time spent in the statement using the plan, in milliseconds</TD>
  93     </TR>
  94 <TR><TD><TT CLASS="STRUCTFIELD">rows</TT></TD>
  95     <TD><TT CLASS="TYPE">bigint</TT></TD>
  96     <TD>&nbsp;</TD>
  97     <TD>Total number of rows retrieved or affected by the statement
  98     using the plan</TD>
  99     </TR>
 100 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_hit</TT></TD>
 101     <TD><TT CLASS="TYPE">bigint</TT></TD>
 102     <TD>&nbsp;</TD>
 103     <TD>Total number of shared block cache hits by the statement using
 104     the plan</TD>
 105     </TR>
 106 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_read</TT></TD>
 107     <TD><TT CLASS="TYPE">bigint</TT></TD>
 108     <TD>&nbsp;</TD>
 109     <TD>Total number of shared blocks read by the statement using the
 110     plan</TD>
 111     </TR>
 112 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_dirtied</TT></TD>
 113     <TD><TT CLASS="TYPE">bigint</TT></TD>
 114     <TD>&nbsp;</TD>
 115     <TD>Total number of shared blocks dirtied by the statement using
 116     the plan</TD>
 117     </TR>
 118 <TR><TD><TT CLASS="STRUCTFIELD">shared_blks_written</TT></TD>
 119     <TD><TT CLASS="TYPE">bigint</TT></TD>
 120     <TD>&nbsp;</TD>
 121     <TD>Total number of shared blocks written by the statement using
 122     the plan</TD>
 123     </TR>
 124 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_hit</TT></TD>
 125     <TD><TT CLASS="TYPE">bigint</TT></TD>
 126     <TD>&nbsp;</TD>
 127     <TD>Total number of local block cache hits by the statement using
 128     the plan</TD>
 129     </TR>
 130 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_read</TT></TD>
 131     <TD><TT CLASS="TYPE">bigint</TT></TD>
 132     <TD>&nbsp;</TD>
 133     <TD>Total number of local blocks read by the statement using the
 134     plan</TD>
 135     </TR>
 136 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_dirtied</TT></TD>
 137     <TD><TT CLASS="TYPE">bigint</TT></TD><TD>&nbsp;</TD>
 138     <TD>Total number of local blocks dirtied by the statement using
 139     the plan</TD>
 140     </TR>
 141 <TR><TD><TT CLASS="STRUCTFIELD">local_blks_written</TT></TD>
 142     <TD><TT CLASS="TYPE">bigint</TT></TD>
 143     <TD>&nbsp;</TD>
 144     <TD>Total number of local blocks written by the statement using
 145     the plan</TD>
 146     </TR>
 147 <TR><TD><TT CLASS="STRUCTFIELD">temp_blks_read</TT></TD>
 148     <TD><TT CLASS="TYPE">bigint</TT></TD>
 149     <TD>&nbsp;</TD>
 150     <TD>Total number of temp blocks read by the statement using the
 151     plan</TD>
 152     </TR>
 153 <TR><TD><TT CLASS="STRUCTFIELD">temp_blks_written</TT></TD>
 154     <TD><TT CLASS="TYPE">bigint</TT></TD>
 155     <TD>&nbsp;</TD>
 156     <TD>Total number of temp blocks written by the statement using the
 157     plan</TD>
 158     </TR>
 159 <TR><TD><TT CLASS="STRUCTFIELD">blk_read_time</TT></TD>
 160     <TD><TT CLASS="TYPE">double precision</TT></TD>
 161     <TD>&nbsp;</TD>
 162
 163     <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>
 164     </TR>
 165 <TR><TD><TT CLASS="STRUCTFIELD">blk_write_time</TT></TD>
 166     <TD><TT CLASS="TYPE">double precision</TT></TD>
 167     <TD>&nbsp;</TD>
 168
 169     <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>
 170     </TR>
 171 <TR><TD><TT CLASS="STRUCTFIELD">first_call</TT></TD>
 172     <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
 173     <TD>&nbsp;</TD>
 174     <TD>Timestamp for the least recently call of the query using this
 175     plan.</TD>
 176     </TR>
 177 <TR><TD><TT CLASS="STRUCTFIELD">last_call</TT></TD>
 178     <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
 179     <TD>&nbsp;</TD>
 180     <TD>Timestamp for the most recently call of the query using this
 181     plan.</TD>
 182     </TR>
 183 </TBODY>
 184 </TABLE>
 185 </DIV>
 186
 187 <P>This view, and the functions <CODE CLASS="FUNCTION">pg_store_plans_reset
 188   </CODE> and <CODE CLASS="FUNCTION">pg_store_plans</CODE> and other
 189   auxiliary functions, are available only in databases where
 190   the <TT CLASS="LITERAL">pg_store_plans</TT> is installed
 191   by <TT CLASS="LITERAL">CREATE EXTENSION</TT>.  However,
 192   statistics are tracked across all databases of the server whenever
 193   the <TT CLASS="FILENAME">pg_store_plans</TT> module is loaded onto
 194   the server, regardless of presence of the view.
 195 </P>
 196
 197 <P>For security reasons, non-superusers are not allowed to see the
 198   plan representation, queryid or planid for the queries executed by
 199   other users.
 200 </P>
 201
 202 <P>
 203
 204 <TT CLASS="VARNAME">queryid</TT> is calculated to identify the source
 205 query similary to <TT CLASS="structname">pg_stat_statements</TT> but
 206 in a different algorithm.  <TT CLASS="VARNAME">plan</TT> is
 207 calculated in a similar way. Two plans are considered the same if they
 208 are seemingly equivalent except for the values of literal constants
 209 or fluctuating values such like costs or measured time. </P>
 210
 211 <P> For PostgreSQL 14 or later, you can find the corresponding query
 212 for a <TT CLASS="structname">pg_store_plans</TT> entry
 213 in <TT CLASS="structname">pg_stat_statements</TT> by joining using
 214 <TT CLASS="structname">queryid</TT>, like the following.
 215
 216 </P>
 217 <P>
 218 <PRE CLASS="SCREEN">SELECT s.query, p.plan FROM pg_store_plans p JOIN pg_stat_statements s USING (queryid);</PRE>
 219 </P>
 220
 221 <P> Plan ID is calculated excluding fluctuating properties of plans.  On the other hand, the <TT CLASS="STRUCTNAME">pg_store_plans.plan</TT> view keeps showing the most recent values for those fluctuating properties.
 222 </P>
 223 <P><TT CLASS="STRUCTNAME">pg_store_plans</TT>
 224    and <TT CLASS="STRUCTNAME">pg_stat_statements</TT> maintain thier
 225    entries individually so there is certain unavoidable chance
 226    especially for entries with low execution frequency that no
 227    correspondent is found.
 228 </P>
 229 </DIV>
 230
 231 <DIV CLASS="SECT2">
 232 <H2 CLASS="SECT2">2. The <TT CLASS="STRUCTNAME">pg_store_plans</TT>
 233 View</H2>
 234
 235 <P>The statistics of <TT CLASS="STRUCTNAME">pg_store_plans</TT> module itself are tracked and made available via a view named <TT CLASS="STRUCTNAME">pg_store_plans_info</TT>.  This view contains only a single row. The columns of the view are shown in <A HREF="#PGSTOREPLANSINFO-COLUMNS">Table 2</A>.
 236 </P>
 237
 238 <DIV CLASS="TABLE">
 239 <A NAME="PGSTOREPLANSINFO-COLUMNS"></A>
 240 <P><B>Table 2. <TT>pg_store_plans_info</TT> Columns</B>
 241 </P>
 242 <TABLE BORDER="1" CLASS="CALSTABLE" >
 243 <COL><COL><COL><COL>
 244 <THEAD><TR><TH>Name</TH><TH>Type</TH><TH>References</TH>
 245        <TH>Description</TH></TR>
 246 </THEAD>
 247 <TBODY>
 248 <TR><TD><TT CLASS="STRUCTFIELD">dealloc</TT></TD>
 249     <TD><TT CLASS="TYPE">bigint</TT></TD>
 250     <TD></TD>
 251     <TD>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.</TD></TR>
 252 <TR><TD><TT CLASS="STRUCTFIELD">stats_reset</TT></TD>
 253     <TD><TT CLASS="TYPE">timestamp with time zone</TT></TD>
 254     <TD></TD>
 255     <TD>Time at which all statistics in the pg_store_plans view were last reset.</TD></TR>
 256 </TBODY>
 257 </TABLE>
 258 </DIV>
 259
 260
 261 <DIV CLASS="SECT2">
 262 <H2 CLASS="SECT2">
 263 <A NAME="Functions">3. Functions</A>
 264 </H2>
 265 <DIV CLASS="VARIABLELIST">
 266 <DL> <DT> <CODE CLASS="FUNCTION">pg_store_plans_reset() returns void</CODE>
 267 </DT>
 268 <DD>
 269 <P>
 270  <CODE CLASS="FUNCTION">pg_store_plans_reset</CODE> discards all
 271       statistics gathered so far
 272       by <TT CLASS="FILENAME">pg_store_plans</TT>.  By default, only
 273       superusers can execute this function.
 274      </P>
 275 </DD>
 276 <DT>
 277 <CODE CLASS="FUNCTION">pg_store_plans(showtext boolean) returns setof
 278 record</CODE>
 279 </DT>
 280 <DD>
 281 <P> The <TT CLASS="STRUCTNAME">pg_store_plans</TT> view is defined in
 282       terms of a function also
 283       named <CODE CLASS="FUNCTION">pg_store_plans</CODE >.
 284 </P>
 285 </DD>
 286 <DT> <CODE CLASS="FUNCTION">pg_store_plans_info() returns record</CODE>
 287 </DT>
 288 <DD>
 289 <P>
 290  <CODE CLASS="FUNCTION">pg_store_plans_info</CODE> view is defined in terms of a function also named <CODE CLASS="FUNCTAION">pg_store_plans_info</CODE>.
 291      </P>
 292 </DD>
 293 <DT>
 294 <CODE CLASS="FUNCTION">pg_store_hash_query(query text) returns oid</CODE>
 295 </DT>
 296 <DD>
 297 <P> This function calculates hash value of a query text. The same
 298       algorithm is used to
 299       calculate <TT CLASS="STRUCTFIELD">queryid</TT>
 300       in <TT CLASS="STRUCTNAME">pg_store_plans</TT> so this function
 301       is usable to join
 302       with <TT CLASS="STRUCTNAME">pg_store_plans</TT>.
 303      </P>
 304 </DD>
 305 <DT>
 306 <CODE CLASS="FUNCTION">pg_store_plans_textplan(query text) returns text</CODE>
 307 </DT>
 308 <DD>
 309 <P> This function generates a ordinary text representation from raw
 310       representation of <TT CLASS="STRUCTFIELD">plan</TT>
 311       in <TT CLASS="STRUCTNAME">pg_store_plans</TT>, which is shown
 312       there when <TT CLASS="VARNAME">pg_store_plans.plan_formats</TT>
 313       = 'raw'. Since the result plan text is generated from json
 314       representation, it might be slightly different from what you
 315       will get directly from 'EXPLAIN' commnand.
 316      </P>
 317 </DD>
 318 <DT>
 319 <CODE CLASS="FUNCTION">pg_store_plans_jsonplan(query text) returns text</CODE>
 320 </DT>
 321 <DD>
 322 <P> This function infaltes a "short format json plan" or "raw format"
 323     into normal json format. Short format json is internal format
 324     for <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> =
 327     'raw'.
 328      </P>
 329 </DD>
 330 <DT>
 331 <CODE CLASS="FUNCTION">pg_store_plans_xmlplan(query text) returns text</CODE>
 332 </DT>
 333 <DD>
 334 <P> This function generates a XML 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>
 338       = 'raw'.
 339      </P>
 340 </DD>
 341 <DT>
 342 <CODE CLASS="FUNCTION">pg_store_plans_yamlplan(query text) returns text</CODE>
 343 </DT>
 344 <DD>
 345 <P> This function generates a YAML representation from raw
 346       representation of <TT CLASS="STRUCTFIELD">plan</TT>
 347       in <TT CLASS="STRUCTNAME">pg_store_plans</TT>, which is shown
 348       there when <TT CLASS="VARNAME">pg_store_plans.plan_formats</TT>
 349       = 'raw'.
 350      </P>
 351 </DD>
 352 </DL>
 353 </DIV>
 354 </DIV>
 355 <DIV CLASS="SECT2">
 356 <H2 CLASS="SECT2">
 357 <ANAME="Config">4. Configuration Parameters</A>
 358 </H2>
 359 <P>
 360 </P>
 361 <DIV CLASS="VARIABLELIST">
 362 <DL>
 363 <DT>
 364 <TT CLASS="VARNAME">pg_store_plans.max</TT>
 365   (<TT CLASS="TYPE">integer</TT>)</DT>
 366 <DD>
 367 <P> <TT CLASS="VARNAME">pg_store_plans.max</TT> is the maximum number
 368 of plans tracked by the module (i.e., the maximum number of rows in
 369 the <TT CLASS="STRUCTNAME">pg_store_plans</TT> view).  If more
 370 distinct plans than that are observed, information about the
 371 least-executed plan is discarded.  The default value is 1000.  This
 372 parameter can only be set at server start.
 373      </P>
 374 </DD>
 375 <DT>
 376 <TT CLASS="VARNAME">pg_store_plans.track</TT>
 377  (<TT CLASS="TYPE">enum</TT>)
 378 </DT>
 379 <DD>
 380 <P> Similar to <TT CLASS="STRUCTNAME">pg_stat_statements</TT>,
 381       <TT CLASS="VARNAME">pg_store_plans.track</TT> controls which
 382       statements are counted by the module.
 383       Specify <TT CLASS="LITERAL">top</TT> to track top-level
 384       statements (those issued directly by
 385       clients), <TT CLASS="LITERAL">all</TT> to also track nested
 386       statements (such as statements invoked within functions),
 387       or <TT CLASS="LITERAL">none</TT> to disable statement
 388       statistics collection.  The default value
 389       is <TT CLASS="LITERAL">top</TT>.  Only superusers can change
 390       this setting.
 391      </P>
 392 </DD>
 393 <TT CLASS="VARNAME">pg_store_plans.max_plan_length</TT>
 394   (<TT CLASS="TYPE">integer</TT>)</DT>
 395 <DD>
 396 <P> <TT CLASS="VARNAME">pg_store_plans.max_plan_length</TT> is the
 397 maximum byte length of plans in the raw (shortened JSON) format to
 398 store.  The plan text is truncated at the length if it is longer than
 399 that value. The default value is 5000.  This parameter can only be set
 400 at server start.
 401 </P>
 402 </DD>
 403 <DT>
 404 <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT>
 405   (<TT CLASS="TYPE">integer</TT>)</DT>
 406 <DD>
 407   <P> <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> specifies
 408   how plan texts are stored while server is running. If it is set
 409   to <TT CLASS="LITERAL">file</TT>, the plan texts are stored in a
 410   temporary file as pg_stat_statements
 411   does. <TT CLASS="LITERAL">shmem</TT> means to store plan texts
 412   on-memory.  The default value is "file".  See
 413   the <A HREF="#MEMORY_SETTING">discussion below</A> for details.
 414   </P>
 415 </DD>
 416 <DT>
 417 <TT CLASS="VARNAME">pg_store_plans.plan_format</TT>
 418  (<TT CLASS="TYPE">enum</TT>)
 419 </DT>
 420 <DD>
 421 <P> <TT CLASS="VARNAME">pg_store_plans.plan_format</TT> controls the
 422   format of <TT CLASS="STRUCTFIELD">plans</TT>
 423   in <TT CLASS="STRUCTNAME">pg_store_plans</TT>. <TT CLASS="LITERAL">text</TT>
 424   is the default value and to show in ordinary text
 425   representation, <TT CLASS="LITERAL">json</TT>, <TT CLASS="LITERAL">xml</TT>
 426   and <TT CLASS="LITERAL">yaml</TT> to show in corresponding format.
 427   <TT CLASS="LITERAL">raw</TT> to get internal representation which
 428   can be fed to <CODE CLASS="FUNCTION">pg_store_plans_*plan</CODE >
 429   functions.
 430 </P>
 431 </DD>
 432 <DT>
 433 <TT CLASS="VARNAME">pg_store_plans.min_duration</TT>
 434   (<TT CLASS="TYPE">integer</TT>)
 435 </DT>
 436 <DD>
 437 <P> <TT CLASS="VARNAME">pg_store_plans.min_duration</TT> is the
 438   minumum statement execution time, in milliseconds, that will cause the
 439   statement's plan to be logged. Setting this to zero (the default) logs
 440   all plans. Only superuses can change this setting.
 441 </P>
 442 </DD>
 443 <DT>
 444 <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT>
 445   (<TT CLASS="TYPE">boolean</TT>)
 446 </DT>
 447 <DD>
 448 <P> <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT>
 449   causes <TT CLASS="COMMAND">EXPLAIN ANALYZE</TT> output, rather than
 450   just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be included
 451   in <TT CLASS="STRUCTFIELD">plan</TT>.  This parameter is off by
 452   default.
 453 </P>
 454 </DD>
 455 <DT>
 456 <TT CLASS="VARNAME">pg_store_plans.log_buffers</TT>
 457   (<TT CLASS="TYPE">boolean</TT>)
 458 </DT>
 459 <DD>
 460 <P> <TT CLASS="VARNAME">pg_store_plans.log_buffers</TT>
 461   causes <TT CLASS="COMMAND">EXPLAIN (ANALYZE, BUFFERS)</TT> output,
 462   rather than just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be
 463   included in <TT CLASS="STRUCTFIELD">plan</TT>.  This parameter is off
 464   by default.
 465 </P>
 466 </DD>
 467 <DT>
 468 <TT CLASS="VARNAME">pg_store_plans.log_timing</TT>
 469  (<TT CLASS="TYPE">boolean</TT>)</DT>
 470 <DD>
 471 <P> Setting <TT CLASS="VARNAME">pg_store_plans.log_timing</TT> to
 472   false disables to record actual timings. The overhead of repeatedly
 473   reading the system clock can slow down the query significantly on
 474   some systems, so it may be useful to set this parameter to FALSE
 475   when only actual row counts, and not exact execution times for each
 476   execution nodes, are needed. Run time of the entire statement is
 477   always measured
 478   when <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT> is
 479   TRUE. It defaults to TRUE.
 480 </P>
 481 </DD>
 482 <DT>
 483 <TT CLASS="VARNAME">pg_store_plans.log_triggers</TT
 484 > (<TT CLASS="TYPE">boolean</TT
 485 >)</DT>
 486 <DD>
 487 <P> <TT CLASS="VARNAME">pg_store_plans.log_triggers</TT> causes
 488   trigger execution statistics to be included in recoreded plans. This
 489   parameter has no effect
 490   unless <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT> is
 491   turned on.
 492 </P>
 493 </DD>
 494 <DT>
 495 <TT CLASS="VARNAME">pg_store_plans.verbose</TT>
 496  (<TT CLASS="TYPE">boolean</TT>)
 497 </DT>
 498 <DD>
 499 <P> <TT CLASS="VARNAME">pg_store_plans.verbose</TT>
 500   causes <TT CLASS="COMMAND">EXPLAIN VERBOSE</TT> output, rather than
 501   just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be included
 502   in <TT CLASS="STRUCTFIELD">plan</TT>.  This parameter is off by
 503   default.
 504 </P>
 505 </DD>
 506 <DT>
 507 <TT CLASS="VARNAME">pg_store_plans.save</TT>
 508  (<TT CLASS="TYPE">boolean</TT>)
 509 </DT>
 510 <DD>
 511 <P> <TT CLASS="VARNAME">pg_store_plans.save</TT> specifies whether to
 512   save plan statistics across server shutdowns.  If it
 513   is <TT CLASS="LITERAL">off</TT> then statistics are not saved at
 514   shutdown nor reloaded at server start.  The default value
 515   is <TT CLASS="LITERAL">on</TT>.  This parameter can only be set in
 516   the <TT CLASS="FILENAME">postgresql.conf</TT> file or on the server
 517   command line.
 518 </P>
 519 </DD>
 520 </DL>
 521 </DIV>
 522
 523 <H2 CLASS="SECT2">
 524 <A NAME="MEMORY_SETTING">5. Discussion on plan_storage setting</A>
 525 </H2>
 526 <P><TT CLASS="LITERAL">pg_store_plans</TT> claims additional shared memory proportional to <TT CLASS="VARNAME">pg_store_plans.max</TT>. When <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> 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 <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> is set to "file", plan texts are written to a temporary file as <TT CLASS="LITERAL">pg_stat_statements</TT> does. If <TT CLASS="VARNAME">pg_store_plans.max</TT> is not large enough to store all plans, <TT CLASS="LITERAL">pg_store_plans</TT> reclaims the space for new plans by evicting some portion of the entries.  After several rounds of that eviction, <TT CLASS="LITERAL">pg_store_plans</TT> runs garbage collection on the temporary file, which might be painful for certain workloads. You can see how frequntly that eviction happens in <TT CLASS="STRUCTNAME">pg_store_plans_info.dealloc</TT>.</P>
 527 <P>If pg_store_plans.max is sufficiently large so that garbage collection doesn't happen, "file" is recommended as <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT>.
 528 </P>
 529 <P> These parameters must be set in
 530  <TT CLASS="FILENAME">postgresql.conf</TT>.  An example setting follows:
 531 </P><PRE CLASS="PROGRAMLISTING"># postgresql.conf
 532 shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
 533 pg_store_plans.max = 10000
 534 pg_store_plans.track = all</PRE>
 535 <P>
 536 </P>
 537 </DIV>
 538 <DIV CLASS="SECT2">
 539 <H2 CLASS="SECT2">
 540 <A NAME="Sample">6. Sample Output</A>
 541 </H2>
 542 <PRE CLASS="SCREEN">(postgresql.conf has following settings)
 543 shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
 544 pg_store_plans.log_analyze = true
 545 pg_store_plans.log_timing = false
 546
 547 bench=# SELECT pg_store_plans_reset();
 548
 549 $ pgbench -i bench
 550 $ pgbench -c10 -t1000 bench
 551
 552 bench=# \x
 553 bench=#  SELECT s.query, p.plan,
 554         p.calls as "plan calls", s.calls as "stmt calls",
 555         p.total_time / p.calls as "time/call", p.first_call, p.last_call
 556         FROM pg_stat_statements s
 557         JOIN pg_store_plans p USING (queryid) WHERE p.calls &#60; s.calls
 558         ORDER BY query ASC, "time/call" DESC;
 559 -[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 560 query      | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
 561 plan       | Update on pgbench_tellers  (cost=0.00..7.88 rows=0 width=0)                                                                                                                                                                                                            +
 562            |   ->  Seq Scan on pgbench_tellers  (cost=0.00..7.88 rows=1 width=10)                                                                                                                                                                                                   +
 563            |         Filter: (tid = 1)
 564 plan calls | 396
 565 stmt calls | 10000
 566 time/call  | 16.15434492676767
 567 first_call | 2021-11-25 15:11:38.258838+09
 568 last_call  | 2021-11-25 15:11:40.170291+09
 569 -[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 570 query      | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
 571 plan       | Update on pgbench_tellers  (cost=0.14..8.15 rows=0 width=0)                                                                                                                                                                                                            +
 572            |   ->  Index Scan using pgbench_tellers_pkey on pgbench_tellers  (cost=0.14..8.15 rows=1 width=10)                                                                                                                                                                      +
 573            |         Index Cond: (tid = 8)                                                                                                                                                                                                                                          +
 574 plan calls | 9604
 575 stmt calls | 10000
 576 time/call  | 10.287281695439345
 577 first_call | 2021-11-25 15:11:40.161556+09
 578 last_call  | 2021-11-25 15:12:09.957773+09
 579 -[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 580 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
 581 plan       | Sort  (cost=309.71..313.88 rows=1667 width=104)                                                                                                                                                                                                                        +
 582            |   Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC                                                                                                                                                    +
 583            |   ->  Merge Join  (cost=119.66..220.50 rows=1667 width=104)                                                                                                                                                                                                            +
 584            |         Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid)                                                                                                                                                                                              +
 585            |         Join Filter: (pg_store_plans.calls < pg_stat_statements.calls)                                                                                                                                                                                                 +
 586            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=48)                                                                                                                                                                                                               +
 587            |               Sort Key: pg_stat_statements.queryid                                                                                                                                                                                                                     +
 588            |               ->  Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=1000 width=48)                                                                                                                                                                           +
 589            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=72)                                                                                                                                                                                                               +
 590            |               Sort Key: pg_store_plans.queryid                                                                                                                                                                                                                         +
 591            |               ->  Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=72)                                                                                                                                                                               +
 592 plan calls | 3
 593 stmt calls | 4
 594 time/call  | 16.387161
 595 first_call | 2021-11-25 15:20:57.978082+09
 596 last_call  | 2021-11-25 15:23:48.631993+09
 597 -[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 598 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
 599 plan       | Sort  (cost=309.71..313.88 rows=1667 width=104)                                                                                                                                                                                                                        +
 600            |   Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC                                                                                                                                                    +
 601            |   Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                                 +
 602            |   ->  Merge Join  (cost=119.66..220.50 rows=1667 width=104)                                                                                                                                                                                                            +
 603            |         Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid)                                                                                                                                                                                              +
 604            |         Join Filter: (pg_store_plans.calls < pg_stat_statements.calls)                                                                                                                                                                                                 +
 605            |         Rows Removed by Join Filter: 7                                                                                                                                                                                                                                 +
 606            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=48)                                                                                                                                                                                                               +
 607            |               Sort Key: pg_stat_statements.queryid                                                                                                                                                                                                                     +
 608            |               Sort Method: quicksort  Memory: 27kB                                                                                                                                                                                                                     +
 609            |               ->  Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=1000 width=48)                                                                                                                                                                           +
 610            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=72)                                                                                                                                                                                                               +
 611            |               Sort Key: pg_store_plans.queryid                                                                                                                                                                                                                         +
 612            |               Sort Method: quicksort  Memory: 30kB                                                                                                                                                                                                                     +
 613            |               ->  Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=72)                                                                                                                                                                               +
 614 plan calls | 1
 615 stmt calls | 4
 616 time/call  | 4.46928
 617 first_call | 2021-11-25 15:12:27.142535+09
 618 last_call  | 2021-11-25 15:12:27.142536+09
 619
 620 postgres=#
 621 </PRE>
 622 </DIV>
 623 </DIV>
 624 <HR>
 625 </BODY>
 626 </HTML>