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> Similarly 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 statements
 384    (those issued directly by clients), <TT CLASS="LITERAL">all</TT> to
 385    also track nested statements (such as statements invoked within
 386    functions except for some commands, see below),
 387    or <TT CLASS="LITERAL">none</TT> to disable statement statistics
 388    collection.  The default value is <TT CLASS="LITERAL">top</TT>.
 389    When <TT CLASS="LITERAL">all</TT> is specified, the commands
 390    executed under <TT CLASS="LITERAL">CREATE EXTENSION</TT>
 391    and <TT CLASS="LITERAL">ALTER EXTENSION</TT> commands are still
 392    ignored.  Specify <TT CLASS="LITERAL">verbose</TT> to track all
 393    commands including ones excluded by <TT CLASS="LITERAL">all</TT>.
 394    Only superusers can change this setting.
 395 </P>
 396 </DD>
 397 <TT CLASS="VARNAME">pg_store_plans.max_plan_length</TT>
 398   (<TT CLASS="TYPE">integer</TT>)</DT>
 399 <DD>
 400 <P> <TT CLASS="VARNAME">pg_store_plans.max_plan_length</TT> is the
 401 maximum byte length of plans in the raw (shortened JSON) format to
 402 store.  The plan text is truncated at the length if it is longer than
 403 that value. The default value is 5000.  This parameter can only be set
 404 at server start.
 405 </P>
 406 </DD>
 407 <DT>
 408 <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT>
 409   (<TT CLASS="TYPE">integer</TT>)</DT>
 410 <DD>
 411   <P> <TT CLASS="VARNAME">pg_store_plans.plan_storage</TT> specifies
 412   how plan texts are stored while server is running. If it is set
 413   to <TT CLASS="LITERAL">file</TT>, the plan texts are stored in a
 414   temporary file as pg_stat_statements
 415   does. <TT CLASS="LITERAL">shmem</TT> means to store plan texts
 416   on-memory.  The default value is "file".  See
 417   the <A HREF="#MEMORY_SETTING">discussion below</A> for details.
 418   </P>
 419 </DD>
 420 <DT>
 421 <TT CLASS="VARNAME">pg_store_plans.plan_format</TT>
 422  (<TT CLASS="TYPE">enum</TT>)
 423 </DT>
 424 <DD>
 425 <P> <TT CLASS="VARNAME">pg_store_plans.plan_format</TT> controls the
 426   format of <TT CLASS="STRUCTFIELD">plans</TT>
 427   in <TT CLASS="STRUCTNAME">pg_store_plans</TT>. <TT CLASS="LITERAL">text</TT>
 428   is the default value and to show in ordinary text
 429   representation, <TT CLASS="LITERAL">json</TT>, <TT CLASS="LITERAL">xml</TT>
 430   and <TT CLASS="LITERAL">yaml</TT> to show in corresponding format.
 431   <TT CLASS="LITERAL">raw</TT> to get internal representation which
 432   can be fed to <CODE CLASS="FUNCTION">pg_store_plans_*plan</CODE >
 433   functions.
 434 </P>
 435 </DD>
 436 <DT>
 437 <TT CLASS="VARNAME">pg_store_plans.min_duration</TT>
 438   (<TT CLASS="TYPE">integer</TT>)
 439 </DT>
 440 <DD>
 441 <P> <TT CLASS="VARNAME">pg_store_plans.min_duration</TT> is the
 442   minumum statement execution time, in milliseconds, that will cause the
 443   statement's plan to be logged. Setting this to zero (the default) logs
 444   all plans. Only superuses can change this setting.
 445 </P>
 446 </DD>
 447 <DT>
 448 <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT>
 449   (<TT CLASS="TYPE">boolean</TT>)
 450 </DT>
 451 <DD>
 452 <P> <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT>
 453   causes <TT CLASS="COMMAND">EXPLAIN ANALYZE</TT> output, rather than
 454   just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be included
 455   in <TT CLASS="STRUCTFIELD">plan</TT>.  This parameter is off by
 456   default.
 457 </P>
 458 </DD>
 459 <DT>
 460 <TT CLASS="VARNAME">pg_store_plans.log_buffers</TT>
 461   (<TT CLASS="TYPE">boolean</TT>)
 462 </DT>
 463 <DD>
 464 <P> <TT CLASS="VARNAME">pg_store_plans.log_buffers</TT>
 465   causes <TT CLASS="COMMAND">EXPLAIN (ANALYZE, BUFFERS)</TT> output,
 466   rather than just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be
 467   included in <TT CLASS="STRUCTFIELD">plan</TT>.  This parameter is off
 468   by default.
 469 </P>
 470 </DD>
 471 <DT>
 472 <TT CLASS="VARNAME">pg_store_plans.log_timing</TT>
 473  (<TT CLASS="TYPE">boolean</TT>)</DT>
 474 <DD>
 475 <P> Setting <TT CLASS="VARNAME">pg_store_plans.log_timing</TT> to
 476   false disables to record actual timings. The overhead of repeatedly
 477   reading the system clock can slow down the query significantly on
 478   some systems, so it may be useful to set this parameter to FALSE
 479   when only actual row counts, and not exact execution times for each
 480   execution nodes, are needed. Run time of the entire statement is
 481   always measured
 482   when <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT> is
 483   TRUE. It defaults to TRUE.
 484 </P>
 485 </DD>
 486 <DT>
 487 <TT CLASS="VARNAME">pg_store_plans.log_triggers</TT
 488 > (<TT CLASS="TYPE">boolean</TT
 489 >)</DT>
 490 <DD>
 491 <P> <TT CLASS="VARNAME">pg_store_plans.log_triggers</TT> causes
 492   trigger execution statistics to be included in recoreded plans. This
 493   parameter has no effect
 494   unless <TT CLASS="VARNAME">pg_store_plans.log_analyze</TT> is
 495   turned on.
 496 </P>
 497 </DD>
 498 <DT>
 499 <TT CLASS="VARNAME">pg_store_plans.verbose</TT>
 500  (<TT CLASS="TYPE">boolean</TT>)
 501 </DT>
 502 <DD>
 503 <P> <TT CLASS="VARNAME">pg_store_plans.verbose</TT>
 504   causes <TT CLASS="COMMAND">EXPLAIN VERBOSE</TT> output, rather than
 505   just <TT CLASS="COMMAND">EXPLAIN</TT> output, to be included
 506   in <TT CLASS="STRUCTFIELD">plan</TT>.  This parameter is off by
 507   default.
 508 </P>
 509 </DD>
 510 <DT>
 511 <TT CLASS="VARNAME">pg_store_plans.save</TT>
 512  (<TT CLASS="TYPE">boolean</TT>)
 513 </DT>
 514 <DD>
 515 <P> <TT CLASS="VARNAME">pg_store_plans.save</TT> specifies whether to
 516   save plan statistics across server shutdowns.  If it
 517   is <TT CLASS="LITERAL">off</TT> then statistics are not saved at
 518   shutdown nor reloaded at server start.  The default value
 519   is <TT CLASS="LITERAL">on</TT>.  This parameter can only be set in
 520   the <TT CLASS="FILENAME">postgresql.conf</TT> file or on the server
 521   command line.
 522 </P>
 523 </DD>
 524 </DL>
 525 </DIV>
 526
 527 <H2 CLASS="SECT2">
 528 <A NAME="MEMORY_SETTING">5. Discussion on plan_storage setting</A>
 529 </H2>
 530 <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>
 531 <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>.
 532 </P>
 533 <P> These parameters must be set in
 534  <TT CLASS="FILENAME">postgresql.conf</TT>.  An example setting follows:
 535 </P><PRE CLASS="PROGRAMLISTING"># postgresql.conf
 536 shared_preload_libraries = 'pg_store_plans, pg_stat_statements'
 537 pg_store_plans.max = 10000
 538 pg_store_plans.track = all</PRE>
 539 <P>
 540 </P>
 541 </DIV>
 542 <DIV CLASS="SECT2">
 543 <H2 CLASS="SECT2">
 544 <A NAME="Sample">6. Sample Output</A>
 545 </H2>
 546 <PRE CLASS="SCREEN">(postgresql.conf has following settings)
 547 shared_preload_libraries = 'pg_store_plans,pg_stat_statements'
 548 pg_store_plans.log_analyze = true
 549 pg_store_plans.log_timing = false
 550
 551 bench=# SELECT pg_store_plans_reset();
 552
 553 $ pgbench -i bench
 554 $ pgbench -c10 -t1000 bench
 555
 556 bench=# \x
 557 bench=#  SELECT s.query, p.plan,
 558         p.calls as "plan calls", s.calls as "stmt calls",
 559         p.total_time / p.calls as "time/call", p.first_call, p.last_call
 560         FROM pg_stat_statements s
 561         JOIN pg_store_plans p USING (queryid) WHERE p.calls &#60; s.calls
 562         ORDER BY query ASC, "time/call" DESC;
 563 -[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 564 query      | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
 565 plan       | Update on pgbench_tellers  (cost=0.00..7.88 rows=0 width=0)                                                                                                                                                                                                            +
 566            |   ->  Seq Scan on pgbench_tellers  (cost=0.00..7.88 rows=1 width=10)                                                                                                                                                                                                   +
 567            |         Filter: (tid = 1)
 568 plan calls | 396
 569 stmt calls | 10000
 570 time/call  | 16.15434492676767
 571 first_call | 2021-11-25 15:11:38.258838+09
 572 last_call  | 2021-11-25 15:11:40.170291+09
 573 -[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 574 query      | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
 575 plan       | Update on pgbench_tellers  (cost=0.14..8.15 rows=0 width=0)                                                                                                                                                                                                            +
 576            |   ->  Index Scan using pgbench_tellers_pkey on pgbench_tellers  (cost=0.14..8.15 rows=1 width=10)                                                                                                                                                                      +
 577            |         Index Cond: (tid = 8)                                                                                                                                                                                                                                          +
 578 plan calls | 9604
 579 stmt calls | 10000
 580 time/call  | 10.287281695439345
 581 first_call | 2021-11-25 15:11:40.161556+09
 582 last_call  | 2021-11-25 15:12:09.957773+09
 583 -[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 584 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
 585 plan       | Sort  (cost=309.71..313.88 rows=1667 width=104)                                                                                                                                                                                                                        +
 586            |   Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC                                                                                                                                                    +
 587            |   ->  Merge Join  (cost=119.66..220.50 rows=1667 width=104)                                                                                                                                                                                                            +
 588            |         Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid)                                                                                                                                                                                              +
 589            |         Join Filter: (pg_store_plans.calls < pg_stat_statements.calls)                                                                                                                                                                                                 +
 590            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=48)                                                                                                                                                                                                               +
 591            |               Sort Key: pg_stat_statements.queryid                                                                                                                                                                                                                     +
 592            |               ->  Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=1000 width=48)                                                                                                                                                                           +
 593            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=72)                                                                                                                                                                                                               +
 594            |               Sort Key: pg_store_plans.queryid                                                                                                                                                                                                                         +
 595            |               ->  Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=72)                                                                                                                                                                               +
 596 plan calls | 3
 597 stmt calls | 4
 598 time/call  | 16.387161
 599 first_call | 2021-11-25 15:20:57.978082+09
 600 last_call  | 2021-11-25 15:23:48.631993+09
 601 -[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 602 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
 603 plan       | Sort  (cost=309.71..313.88 rows=1667 width=104)                                                                                                                                                                                                                        +
 604            |   Sort Key: pg_stat_statements.query, ((pg_store_plans.total_time / (pg_store_plans.calls)::double precision)) DESC                                                                                                                                                    +
 605            |   Sort Method: quicksort  Memory: 26kB                                                                                                                                                                                                                                 +
 606            |   ->  Merge Join  (cost=119.66..220.50 rows=1667 width=104)                                                                                                                                                                                                            +
 607            |         Merge Cond: (pg_stat_statements.queryid = pg_store_plans.queryid)                                                                                                                                                                                              +
 608            |         Join Filter: (pg_store_plans.calls < pg_stat_statements.calls)                                                                                                                                                                                                 +
 609            |         Rows Removed by Join Filter: 7                                                                                                                                                                                                                                 +
 610            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=48)                                                                                                                                                                                                               +
 611            |               Sort Key: pg_stat_statements.queryid                                                                                                                                                                                                                     +
 612            |               Sort Method: quicksort  Memory: 27kB                                                                                                                                                                                                                     +
 613            |               ->  Function Scan on pg_stat_statements  (cost=0.00..10.00 rows=1000 width=48)                                                                                                                                                                           +
 614            |         ->  Sort  (cost=59.83..62.33 rows=1000 width=72)                                                                                                                                                                                                               +
 615            |               Sort Key: pg_store_plans.queryid                                                                                                                                                                                                                         +
 616            |               Sort Method: quicksort  Memory: 30kB                                                                                                                                                                                                                     +
 617            |               ->  Function Scan on pg_store_plans  (cost=0.00..10.00 rows=1000 width=72)                                                                                                                                                                               +
 618 plan calls | 1
 619 stmt calls | 4
 620 time/call  | 4.46928
 621 first_call | 2021-11-25 15:12:27.142535+09
 622 last_call  | 2021-11-25 15:12:27.142536+09
 623
 624 postgres=#
 625 </PRE>
 626 </DIV>
 627 </DIV>
 628 <HR>
 629 </BODY>
 630 </HTML>