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