Debian Codegraph @

doc/src/sgml/pgstatstatements.sgml

raw
1 <!-- doc/src/sgml/pgstatstatements.sgml -->
2
3 <sect1 id="pgstatstatements" xreflabel="pg_stat_statements">
4 <title>pg_stat_statements &mdash; track statistics of SQL planning and execution</title>
5
6 <indexterm zone="pgstatstatements">
7 <primary>pg_stat_statements</primary>
8 </indexterm>
9
10 <para>
11 The <filename>pg_stat_statements</filename> module provides a means for
12 tracking planning and execution statistics of all SQL statements executed by
13 a server.
14 </para>
15
16 <para>
17 The module must be loaded by adding <literal>pg_stat_statements</literal> to
18 <xref linkend="guc-shared-preload-libraries"/> in
19 <filename>postgresql.conf</filename>, because it requires additional shared memory.
20 This means that a server restart is needed to add or remove the module.
21 In addition, query identifier calculation must be enabled in order for the
22 module to be active, which is done automatically if <xref linkend="guc-compute-query-id"/>
23 is set to <literal>auto</literal> or <literal>on</literal>, or any third-party
24 module that calculates query identifiers is loaded.
25 </para>
26
27 <para>
28 When <filename>pg_stat_statements</filename> is active, it tracks
29 statistics across all databases of the server. To access and manipulate
30 these statistics, the module provides views
31 <structname>pg_stat_statements</structname> and
32 <structname>pg_stat_statements_info</structname>,
33 and the utility functions <function>pg_stat_statements_reset</function> and
34 <function>pg_stat_statements</function>. These are not available globally but
35 can be enabled for a specific database with
36 <command>CREATE EXTENSION pg_stat_statements</command>.
37 </para>
38
39 <sect2 id="pgstatstatements-pg-stat-statements">
40 <title>The <structname>pg_stat_statements</structname> View</title>
41
42 <para>
43 The statistics gathered by the module are made available via a
44 view named <structname>pg_stat_statements</structname>. This view
45 contains one row for each distinct combination of database ID, user
46 ID, query ID and whether it's a top-level statement or not (up to
47 the maximum number of distinct statements that the module can track).
48 The columns of the view are shown in
49 <xref linkend="pgstatstatements-columns"/>.
50 </para>
51
52 <table id="pgstatstatements-columns">
53 <title><structname>pg_stat_statements</structname> Columns</title>
54 <tgroup cols="1">
55 <thead>
56 <row>
57 <entry role="catalog_table_entry"><para role="column_definition">
58 Column Type
59 </para>
60 <para>
61 Description
62 </para></entry>
63 </row>
64 </thead>
65
66 <tbody>
67 <row>
68 <entry role="catalog_table_entry"><para role="column_definition">
69 <structfield>userid</structfield> <type>oid</type>
70 (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
71 </para>
72 <para>
73 OID of user who executed the statement
74 </para></entry>
75 </row>
76
77 <row>
78 <entry role="catalog_table_entry"><para role="column_definition">
79 <structfield>dbid</structfield> <type>oid</type>
80 (references <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>)
81 </para>
82 <para>
83 OID of database in which the statement was executed
84 </para></entry>
85 </row>
86
87 <row>
88 <entry role="catalog_table_entry"><para role="column_definition">
89 <structfield>toplevel</structfield> <type>bool</type>
90 </para>
91 <para>
92 True if the query was executed as a top-level statement
93 (always true if <varname>pg_stat_statements.track</varname> is set to
94 <literal>top</literal>)
95 </para></entry>
96 </row>
97
98 <row>
99 <entry role="catalog_table_entry"><para role="column_definition">
100 <structfield>queryid</structfield> <type>bigint</type>
101 </para>
102 <para>
103 Hash code to identify identical normalized queries.
104 </para></entry>
105 </row>
106
107 <row>
108 <entry role="catalog_table_entry"><para role="column_definition">
109 <structfield>query</structfield> <type>text</type>
110 </para>
111 <para>
112 Text of a representative statement
113 </para></entry>
114 </row>
115
116 <row>
117 <entry role="catalog_table_entry"><para role="column_definition">
118 <structfield>plans</structfield> <type>bigint</type>
119 </para>
120 <para>
121 Number of times the statement was planned
122 (if <varname>pg_stat_statements.track_planning</varname> is enabled,
123 otherwise zero)
124 </para></entry>
125 </row>
126
127 <row>
128 <entry role="catalog_table_entry"><para role="column_definition">
129 <structfield>total_plan_time</structfield> <type>double precision</type>
130 </para>
131 <para>
132 Total time spent planning the statement, in milliseconds
133 (if <varname>pg_stat_statements.track_planning</varname> is enabled,
134 otherwise zero)
135 </para></entry>
136 </row>
137
138 <row>
139 <entry role="catalog_table_entry"><para role="column_definition">
140 <structfield>min_plan_time</structfield> <type>double precision</type>
141 </para>
142 <para>
143 Minimum time spent planning the statement, in milliseconds.
144 This field will be zero if <varname>pg_stat_statements.track_planning</varname>
145 is disabled, or if the counter has been reset using the
146 <function>pg_stat_statements_reset</function> function with the
147 <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
148 and never been planned since.
149 </para></entry>
150 </row>
151
152 <row>
153 <entry role="catalog_table_entry"><para role="column_definition">
154 <structfield>max_plan_time</structfield> <type>double precision</type>
155 </para>
156 <para>
157 Maximum time spent planning the statement, in milliseconds.
158 This field will be zero if <varname>pg_stat_statements.track_planning</varname>
159 is disabled, or if the counter has been reset using the
160 <function>pg_stat_statements_reset</function> function with the
161 <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
162 and never been planned since.
163 </para></entry>
164 </row>
165
166 <row>
167 <entry role="catalog_table_entry"><para role="column_definition">
168 <structfield>mean_plan_time</structfield> <type>double precision</type>
169 </para>
170 <para>
171 Mean time spent planning the statement, in milliseconds
172 (if <varname>pg_stat_statements.track_planning</varname> is enabled,
173 otherwise zero)
174 </para></entry>
175 </row>
176
177 <row>
178 <entry role="catalog_table_entry"><para role="column_definition">
179 <structfield>stddev_plan_time</structfield> <type>double precision</type>
180 </para>
181 <para>
182 Population standard deviation of time spent planning the statement,
183 in milliseconds
184 (if <varname>pg_stat_statements.track_planning</varname> is enabled,
185 otherwise zero)
186 </para></entry>
187 </row>
188
189 <row>
190 <entry role="catalog_table_entry"><para role="column_definition">
191 <structfield>calls</structfield> <type>bigint</type>
192 </para>
193 <para>
194 Number of times the statement was executed
195 </para></entry>
196 </row>
197
198 <row>
199 <entry role="catalog_table_entry"><para role="column_definition">
200 <structfield>total_exec_time</structfield> <type>double precision</type>
201 </para>
202 <para>
203 Total time spent executing the statement, in milliseconds
204 </para></entry>
205 </row>
206
207 <row>
208 <entry role="catalog_table_entry"><para role="column_definition">
209 <structfield>min_exec_time</structfield> <type>double precision</type>
210 </para>
211 <para>
212 Minimum time spent executing the statement, in milliseconds,
213 this field will be zero until this statement
214 is executed first time after reset performed by the
215 <function>pg_stat_statements_reset</function> function with the
216 <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
217 </para></entry>
218 </row>
219
220 <row>
221 <entry role="catalog_table_entry"><para role="column_definition">
222 <structfield>max_exec_time</structfield> <type>double precision</type>
223 </para>
224 <para>
225 Maximum time spent executing the statement, in milliseconds,
226 this field will be zero until this statement
227 is executed first time after reset performed by the
228 <function>pg_stat_statements_reset</function> function with the
229 <structfield>minmax_only</structfield> parameter set to <literal>true</literal>
230 </para></entry>
231 </row>
232
233 <row>
234 <entry role="catalog_table_entry"><para role="column_definition">
235 <structfield>mean_exec_time</structfield> <type>double precision</type>
236 </para>
237 <para>
238 Mean time spent executing the statement, in milliseconds
239 </para></entry>
240 </row>
241
242 <row>
243 <entry role="catalog_table_entry"><para role="column_definition">
244 <structfield>stddev_exec_time</structfield> <type>double precision</type>
245 </para>
246 <para>
247 Population standard deviation of time spent executing the statement, in milliseconds
248 </para></entry>
249 </row>
250
251 <row>
252 <entry role="catalog_table_entry"><para role="column_definition">
253 <structfield>rows</structfield> <type>bigint</type>
254 </para>
255 <para>
256 Total number of rows retrieved or affected by the statement
257 </para></entry>
258 </row>
259
260 <row>
261 <entry role="catalog_table_entry"><para role="column_definition">
262 <structfield>shared_blks_hit</structfield> <type>bigint</type>
263 </para>
264 <para>
265 Total number of shared block cache hits by the statement
266 </para></entry>
267 </row>
268
269 <row>
270 <entry role="catalog_table_entry"><para role="column_definition">
271 <structfield>shared_blks_read</structfield> <type>bigint</type>
272 </para>
273 <para>
274 Total number of shared blocks read by the statement
275 </para></entry>
276 </row>
277
278 <row>
279 <entry role="catalog_table_entry"><para role="column_definition">
280 <structfield>shared_blks_dirtied</structfield> <type>bigint</type>
281 </para>
282 <para>
283 Total number of shared blocks dirtied by the statement
284 </para></entry>
285 </row>
286
287 <row>
288 <entry role="catalog_table_entry"><para role="column_definition">
289 <structfield>shared_blks_written</structfield> <type>bigint</type>
290 </para>
291 <para>
292 Total number of shared blocks written by the statement
293 </para></entry>
294 </row>
295
296 <row>
297 <entry role="catalog_table_entry"><para role="column_definition">
298 <structfield>local_blks_hit</structfield> <type>bigint</type>
299 </para>
300 <para>
301 Total number of local block cache hits by the statement
302 </para></entry>
303 </row>
304
305 <row>
306 <entry role="catalog_table_entry"><para role="column_definition">
307 <structfield>local_blks_read</structfield> <type>bigint</type>
308 </para>
309 <para>
310 Total number of local blocks read by the statement
311 </para></entry>
312 </row>
313
314 <row>
315 <entry role="catalog_table_entry"><para role="column_definition">
316 <structfield>local_blks_dirtied</structfield> <type>bigint</type>
317 </para>
318 <para>
319 Total number of local blocks dirtied by the statement
320 </para></entry>
321 </row>
322
323 <row>
324 <entry role="catalog_table_entry"><para role="column_definition">
325 <structfield>local_blks_written</structfield> <type>bigint</type>
326 </para>
327 <para>
328 Total number of local blocks written by the statement
329 </para></entry>
330 </row>
331
332 <row>
333 <entry role="catalog_table_entry"><para role="column_definition">
334 <structfield>temp_blks_read</structfield> <type>bigint</type>
335 </para>
336 <para>
337 Total number of temp blocks read by the statement
338 </para></entry>
339 </row>
340
341 <row>
342 <entry role="catalog_table_entry"><para role="column_definition">
343 <structfield>temp_blks_written</structfield> <type>bigint</type>
344 </para>
345 <para>
346 Total number of temp blocks written by the statement
347 </para></entry>
348 </row>
349
350 <row>
351 <entry role="catalog_table_entry"><para role="column_definition">
352 <structfield>shared_blk_read_time</structfield> <type>double precision</type>
353 </para>
354 <para>
355 Total time the statement spent reading shared blocks, in milliseconds
356 (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
357 </para></entry>
358 </row>
359
360 <row>
361 <entry role="catalog_table_entry"><para role="column_definition">
362 <structfield>shared_blk_write_time</structfield> <type>double precision</type>
363 </para>
364 <para>
365 Total time the statement spent writing shared blocks, in milliseconds
366 (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
367 </para></entry>
368 </row>
369
370 <row>
371 <entry role="catalog_table_entry"><para role="column_definition">
372 <structfield>local_blk_read_time</structfield> <type>double precision</type>
373 </para>
374 <para>
375 Total time the statement spent reading local blocks, in milliseconds
376 (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
377 </para></entry>
378 </row>
379
380 <row>
381 <entry role="catalog_table_entry"><para role="column_definition">
382 <structfield>local_blk_write_time</structfield> <type>double precision</type>
383 </para>
384 <para>
385 Total time the statement spent writing local blocks, in milliseconds
386 (if <xref linkend="guc-track-io-timing"/> is enabled, otherwise zero)
387 </para></entry>
388 </row>
389
390 <row>
391 <entry role="catalog_table_entry"><para role="column_definition">
392 <structfield>temp_blk_read_time</structfield> <type>double precision</type>
393 </para>
394 <para>
395 Total time the statement spent reading temporary file blocks, in
396 milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
397 otherwise zero)
398 </para></entry>
399 </row>
400
401 <row>
402 <entry role="catalog_table_entry"><para role="column_definition">
403 <structfield>temp_blk_write_time</structfield> <type>double precision</type>
404 </para>
405 <para>
406 Total time the statement spent writing temporary file blocks, in
407 milliseconds (if <xref linkend="guc-track-io-timing"/> is enabled,
408 otherwise zero)
409 </para></entry>
410 </row>
411
412 <row>
413 <entry role="catalog_table_entry"><para role="column_definition">
414 <structfield>wal_records</structfield> <type>bigint</type>
415 </para>
416 <para>
417 Total number of WAL records generated by the statement
418 </para></entry>
419 </row>
420
421 <row>
422 <entry role="catalog_table_entry"><para role="column_definition">
423 <structfield>wal_fpi</structfield> <type>bigint</type>
424 </para>
425 <para>
426 Total number of WAL full page images generated by the statement
427 </para></entry>
428 </row>
429
430 <row>
431 <entry role="catalog_table_entry"><para role="column_definition">
432 <structfield>wal_bytes</structfield> <type>numeric</type>
433 </para>
434 <para>
435 Total amount of WAL generated by the statement in bytes
436 </para></entry>
437 </row>
438
439 <row>
440 <entry role="catalog_table_entry"><para role="column_definition">
441 <structfield>wal_buffers_full</structfield> <type>bigint</type>
442 </para>
443 <para>
444 Number of times the WAL buffers became full
445 </para></entry>
446 </row>
447
448 <row>
449 <entry role="catalog_table_entry"><para role="column_definition">
450 <structfield>jit_functions</structfield> <type>bigint</type>
451 </para>
452 <para>
453 Total number of functions JIT-compiled by the statement
454 </para></entry>
455 </row>
456
457 <row>
458 <entry role="catalog_table_entry"><para role="column_definition">
459 <structfield>jit_generation_time</structfield> <type>double precision</type>
460 </para>
461 <para>
462 Total time spent by the statement on generating JIT code, in milliseconds
463 </para></entry>
464 </row>
465
466 <row>
467 <entry role="catalog_table_entry"><para role="column_definition">
468 <structfield>jit_inlining_count</structfield> <type>bigint</type>
469 </para>
470 <para>
471 Number of times functions have been inlined
472 </para></entry>
473 </row>
474
475 <row>
476 <entry role="catalog_table_entry"><para role="column_definition">
477 <structfield>jit_inlining_time</structfield> <type>double precision</type>
478 </para>
479 <para>
480 Total time spent by the statement on inlining functions, in milliseconds
481 </para></entry>
482 </row>
483
484 <row>
485 <entry role="catalog_table_entry"><para role="column_definition">
486 <structfield>jit_optimization_count</structfield> <type>bigint</type>
487 </para>
488 <para>
489 Number of times the statement has been optimized
490 </para></entry>
491 </row>
492
493 <row>
494 <entry role="catalog_table_entry"><para role="column_definition">
495 <structfield>jit_optimization_time</structfield> <type>double precision</type>
496 </para>
497 <para>
498 Total time spent by the statement on optimizing, in milliseconds
499 </para></entry>
500 </row>
501
502 <row>
503 <entry role="catalog_table_entry"><para role="column_definition">
504 <structfield>jit_emission_count</structfield> <type>bigint</type>
505 </para>
506 <para>
507 Number of times code has been emitted
508 </para></entry>
509 </row>
510
511 <row>
512 <entry role="catalog_table_entry"><para role="column_definition">
513 <structfield>jit_emission_time</structfield> <type>double precision</type>
514 </para>
515 <para>
516 Total time spent by the statement on emitting code, in milliseconds
517 </para></entry>
518 </row>
519
520 <row>
521 <entry role="catalog_table_entry"><para role="column_definition">
522 <structfield>jit_deform_count</structfield> <type>bigint</type>
523 </para>
524 <para>
525 Total number of tuple deform functions JIT-compiled by the statement
526 </para></entry>
527 </row>
528
529 <row>
530 <entry role="catalog_table_entry"><para role="column_definition">
531 <structfield>jit_deform_time</structfield> <type>double precision</type>
532 </para>
533 <para>
534 Total time spent by the statement on JIT-compiling tuple deform
535 functions, in milliseconds
536 </para></entry>
537 </row>
538
539 <row>
540 <entry role="catalog_table_entry"><para role="column_definition">
541 <structfield>parallel_workers_to_launch</structfield> <type>bigint</type>
542 </para>
543 <para>
544 Number of parallel workers planned to be launched
545 </para></entry>
546 </row>
547
548 <row>
549 <entry role="catalog_table_entry"><para role="column_definition">
550 <structfield>parallel_workers_launched</structfield> <type>bigint</type>
551 </para>
552 <para>
553 Number of parallel workers actually launched
554 </para></entry>
555 </row>
556
557 <row>
558 <entry role="catalog_table_entry"><para role="column_definition">
559 <structfield>stats_since</structfield> <type>timestamp with time zone</type>
560 </para>
561 <para>
562 Time at which statistics gathering started for this statement
563 </para></entry>
564 </row>
565
566 <row>
567 <entry role="catalog_table_entry"><para role="column_definition">
568 <structfield>minmax_stats_since</structfield> <type>timestamp with time zone</type>
569 </para>
570 <para>
571 Time at which min/max statistics gathering started for this
572 statement (fields <structfield>min_plan_time</structfield>,
573 <structfield>max_plan_time</structfield>,
574 <structfield>min_exec_time</structfield> and
575 <structfield>max_exec_time</structfield>)
576 </para></entry>
577 </row>
578 </tbody>
579 </tgroup>
580 </table>
581
582 <para>
583 For security reasons, only superusers and roles with privileges of the
584 <literal>pg_read_all_stats</literal> role are allowed to see the SQL text and
585 <structfield>queryid</structfield> of queries executed by other users.
586 Other users can see the statistics, however, if the view has been installed
587 in their database.
588 </para>
589
590 <para>
591 Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
592 <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>)
593 and utility commands are combined into a single
594 <structname>pg_stat_statements</structname> entry whenever they have identical query
595 structures according to an internal hash calculation. Typically, two
596 queries will be considered the same for this purpose if they are
597 semantically equivalent except for the values of literal constants
598 appearing in the query.
599 </para>
600
601 <note>
602 <para>
603 The following details about constant replacement and
604 <structfield>queryid</structfield> only apply when <xref
605 linkend="guc-compute-query-id"/> is enabled. If you use an external
606 module instead to compute <structfield>queryid</structfield>, you
607 should refer to its documentation for details.
608 </para>
609 </note>
610
611 <para>
612 When a constant's value has been ignored for purposes of matching the query
613 to other queries, the constant is replaced by a parameter symbol, such
614 as <literal>$1</literal>, in the <structname>pg_stat_statements</structname>
615 display.
616 The rest of the query text is that of the first query that had the
617 particular <structfield>queryid</structfield> hash value associated with the
618 <structname>pg_stat_statements</structname> entry.
619 </para>
620
621 <para>
622 Queries on which normalization can be applied may be observed with constant
623 values in <structname>pg_stat_statements</structname>, especially when there
624 is a high rate of entry deallocations. To reduce the likelihood of this
625 happening, consider increasing <varname>pg_stat_statements.max</varname>.
626 The <structname>pg_stat_statements_info</structname> view, discussed below
627 in <xref linkend="pgstatstatements-pg-stat-statements-info"/>,
628 provides statistics about entry deallocations.
629 </para>
630
631 <para>
632 In some cases, queries with visibly different texts might get merged into a
633 single <structname>pg_stat_statements</structname> entry; as explained above,
634 this is expected to happen for semantically equivalent queries.
635 In addition, if the only difference between queries is the number of elements
636 in a list of constants, the list will get squashed down to a single element but shown
637 with a commented-out list indicator:
638
639 <screen>
640 =# SELECT pg_stat_statements_reset();
641 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7);
642 =# SELECT * FROM test WHERE a IN (1, 2, 3, 4, 5, 6, 7, 8);
643 =# SELECT query, calls FROM pg_stat_statements
644 WHERE query LIKE 'SELECT%';
645 -[ RECORD 1 ]------------------------------
646 query | SELECT * FROM test WHERE a IN ($1 /*, ... */)
647 calls | 2
648 </screen>
649
650 In addition to these cases, there is a small chance of hash collisions
651 causing unrelated queries to be merged into one entry.
652 (This cannot happen for queries belonging to different users or databases,
653 however.)
654 </para>
655
656 <para>
657 Since the <structfield>queryid</structfield> hash value is computed on the
658 post-parse-analysis representation of the queries, the opposite is
659 also possible: queries with identical texts might appear as
660 separate entries, if they have different meanings as a result of
661 factors such as different <varname>search_path</varname> settings.
662 </para>
663
664 <para>
665 Consumers of <structname>pg_stat_statements</structname> may wish to use
666 <structfield>queryid</structfield> (perhaps in combination with
667 <structfield>dbid</structfield> and <structfield>userid</structfield>) as a more stable
668 and reliable identifier for each entry than its query text.
669 However, it is important to understand that there are only limited
670 guarantees around the stability of the <structfield>queryid</structfield> hash
671 value. Since the identifier is derived from the
672 post-parse-analysis tree, its value is a function of, among other
673 things, the internal object identifiers appearing in this representation.
674 This has some counterintuitive implications. For example,
675 <filename>pg_stat_statements</filename> will consider two apparently-identical
676 queries to be distinct, if they reference for example a function that was
677 dropped and recreated between the executions of the two queries.
678 Conversely, if a table is dropped and recreated between the
679 executions of queries, two apparently-identical queries may be
680 considered the same. However, if the alias for a table is different
681 for otherwise-similar queries, these queries will be considered
682 distinct.
683 The hashing process is also sensitive to differences in
684 machine architecture and other facets of the platform.
685 Furthermore, it is not safe to assume that <structfield>queryid</structfield>
686 will be stable across major versions of <productname>PostgreSQL</productname>.
687 </para>
688
689 <para>
690 Two servers participating in replication based on physical WAL replay can
691 be expected to have identical <structfield>queryid</structfield> values for
692 the same query. However, logical replication schemes do not promise to
693 keep replicas identical in all relevant details, so
694 <structfield>queryid</structfield> will not be a useful identifier for
695 accumulating costs across a set of logical replicas.
696 If in doubt, direct testing is recommended.
697 </para>
698
699 <para>
700 Generally, it can be assumed that <structfield>queryid</structfield> values
701 are stable between minor version releases of <productname>PostgreSQL</productname>,
702 providing that instances are running on the same machine architecture and
703 the catalog metadata details match. Compatibility will only be broken
704 between minor versions as a last resort.
705 </para>
706
707 <para>
708 The parameter symbols used to replace constants in
709 representative query texts start from the next number after the
710 highest <literal>$</literal><replaceable>n</replaceable> parameter in the original query
711 text, or <literal>$1</literal> if there was none. It's worth noting that in
712 some cases there may be hidden parameter symbols that affect this
713 numbering. For example, <application>PL/pgSQL</application> uses hidden parameter
714 symbols to insert values of function local variables into queries, so that
715 a <application>PL/pgSQL</application> statement like <literal>SELECT i + 1 INTO j</literal>
716 would have representative text like <literal>SELECT i + $2</literal>.
717 </para>
718
719 <para>
720 The representative query texts are kept in an external disk file, and do
721 not consume shared memory. Therefore, even very lengthy query texts can
722 be stored successfully. However, if many long query texts are
723 accumulated, the external file might grow unmanageably large. As a
724 recovery method if that happens, <filename>pg_stat_statements</filename> may
725 choose to discard the query texts, whereupon all existing entries in
726 the <structname>pg_stat_statements</structname> view will show
727 null <structfield>query</structfield> fields, though the statistics associated with
728 each <structfield>queryid</structfield> are preserved. If this happens, consider
729 reducing <varname>pg_stat_statements.max</varname> to prevent
730 recurrences.
731 </para>
732
733 <para>
734 <structfield>plans</structfield> and <structfield>calls</structfield> aren't
735 always expected to match because planning and execution statistics are
736 updated at their respective end phase, and only for successful operations.
737 For example, if a statement is successfully planned but fails during
738 the execution phase, only its planning statistics will be updated.
739 If planning is skipped because a cached plan is used, only its execution
740 statistics will be updated.
741 </para>
742 </sect2>
743
744 <sect2 id="pgstatstatements-pg-stat-statements-info">
745 <title>The <structname>pg_stat_statements_info</structname> View</title>
746
747 <indexterm>
748 <primary>pg_stat_statements_info</primary>
749 </indexterm>
750
751 <para>
752 The statistics of the <filename>pg_stat_statements</filename> module
753 itself are tracked and made available via a view named
754 <structname>pg_stat_statements_info</structname>. This view contains
755 only a single row. The columns of the view are shown in
756 <xref linkend="pgstatstatementsinfo-columns"/>.
757 </para>
758
759 <table id="pgstatstatementsinfo-columns">
760 <title><structname>pg_stat_statements_info</structname> Columns</title>
761 <tgroup cols="1">
762 <thead>
763 <row>
764 <entry role="catalog_table_entry"><para role="column_definition">
765 Column Type
766 </para>
767 <para>
768 Description
769 </para></entry>
770 </row>
771 </thead>
772
773 <tbody>
774 <row>
775 <entry role="catalog_table_entry"><para role="column_definition">
776 <structfield>dealloc</structfield> <type>bigint</type>
777 </para>
778 <para>
779 Total number of times <structname>pg_stat_statements</structname>
780 entries about the least-executed statements were deallocated
781 because more distinct statements than
782 <varname>pg_stat_statements.max</varname> were observed
783 </para></entry>
784 </row>
785 <row>
786 <entry role="catalog_table_entry"><para role="column_definition">
787 <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
788 </para>
789 <para>
790 Time at which all statistics in the
791 <structname>pg_stat_statements</structname> view were last reset.
792 </para></entry>
793 </row>
794
795 </tbody>
796 </tgroup>
797 </table>
798 </sect2>
799
800 <sect2 id="pgstatstatements-funcs">
801 <title>Functions</title>
802
803 <variablelist>
804 <varlistentry>
805 <term>
806 <function>pg_stat_statements_reset(userid Oid, dbid Oid, queryid
807 bigint, minmax_only boolean) returns timestamp with time zone</function>
808 <indexterm>
809 <primary>pg_stat_statements_reset</primary>
810 </indexterm>
811 </term>
812
813 <listitem>
814 <para>
815 <function>pg_stat_statements_reset</function> discards statistics
816 gathered so far by <filename>pg_stat_statements</filename> corresponding
817 to the specified <structfield>userid</structfield>, <structfield>dbid</structfield>
818 and <structfield>queryid</structfield>. If any of the parameters are not
819 specified, the default value <literal>0</literal>(invalid) is used for
820 each of them and the statistics that match with other parameters will be
821 reset. If no parameter is specified or all the specified parameters are
822 <literal>0</literal>(invalid), it will discard all statistics.
823 If all statistics in the <filename>pg_stat_statements</filename>
824 view are discarded, it will also reset the statistics in the
825 <structname>pg_stat_statements_info</structname> view.
826 When <structfield>minmax_only</structfield> is <literal>true</literal> only the
827 values of minimum and maximum planning and execution time will be reset (i.e.
828 <structfield>min_plan_time</structfield>, <structfield>max_plan_time</structfield>,
829 <structfield>min_exec_time</structfield> and <structfield>max_exec_time</structfield>
830 fields). The default value for <structfield>minmax_only</structfield> parameter is
831 <literal>false</literal>. Time of last min/max reset performed is shown in
832 <structfield>minmax_stats_since</structfield> field of the
833 <structname>pg_stat_statements</structname> view.
834 This function returns the time of a reset. This time is saved to
835 <structfield>stats_reset</structfield> field of
836 <structname>pg_stat_statements_info</structname> view or to
837 <structfield>minmax_stats_since</structfield> field of the
838 <structname>pg_stat_statements</structname> view if the corresponding reset was
839 actually performed.
840 By default, this function can only be executed by superusers.
841 Access may be granted to others using <command>GRANT</command>.
842 </para>
843 </listitem>
844 </varlistentry>
845
846 <varlistentry>
847 <term>
848 <function>pg_stat_statements(showtext boolean) returns setof record</function>
849 <indexterm>
850 <primary>pg_stat_statements</primary>
851 <secondary>function</secondary>
852 </indexterm>
853 </term>
854
855 <listitem>
856 <para>
857 The <structname>pg_stat_statements</structname> view is defined in
858 terms of a function also named <function>pg_stat_statements</function>.
859 It is possible for clients to call
860 the <function>pg_stat_statements</function> function directly, and by
861 specifying <literal>showtext := false</literal> have query text be
862 omitted (that is, the <literal>OUT</literal> argument that corresponds
863 to the view's <structfield>query</structfield> column will return nulls). This
864 feature is intended to support external tools that might wish to avoid
865 the overhead of repeatedly retrieving query texts of indeterminate
866 length. Such tools can instead cache the first query text observed
867 for each entry themselves, since that is
868 all <filename>pg_stat_statements</filename> itself does, and then retrieve
869 query texts only as needed. Since the server stores query texts in a
870 file, this approach may reduce physical I/O for repeated examination
871 of the <structname>pg_stat_statements</structname> data.
872 </para>
873 </listitem>
874 </varlistentry>
875 </variablelist>
876 </sect2>
877
878 <sect2 id="pgstatstatements-config-params">
879 <title>Configuration Parameters</title>
880
881 <variablelist>
882 <varlistentry>
883 <term>
884 <varname>pg_stat_statements.max</varname> (<type>integer</type>)
885 <indexterm>
886 <primary><varname>pg_stat_statements.max</varname> configuration parameter</primary>
887 </indexterm>
888 </term>
889
890 <listitem>
891 <para>
892 <varname>pg_stat_statements.max</varname> is the maximum number of
893 statements tracked by the module (i.e., the maximum number of rows
894 in the <structname>pg_stat_statements</structname> view). If more distinct
895 statements than that are observed, information about the least-executed
896 statements is discarded. The number of times such information was
897 discarded can be seen in the
898 <structname>pg_stat_statements_info</structname> view.
899 The default value is 5000.
900 This parameter can only be set at server start.
901 </para>
902 </listitem>
903 </varlistentry>
904
905 <varlistentry>
906 <term>
907 <varname>pg_stat_statements.track</varname> (<type>enum</type>)
908 <indexterm>
909 <primary><varname>pg_stat_statements.track</varname> configuration parameter</primary>
910 </indexterm>
911 </term>
912
913 <listitem>
914 <para>
915 <varname>pg_stat_statements.track</varname> controls which statements
916 are counted by the module.
917 Specify <literal>top</literal> to track top-level statements (those issued
918 directly by clients), <literal>all</literal> to also track nested statements
919 (such as statements invoked within functions), or <literal>none</literal> to
920 disable statement statistics collection.
921 The default value is <literal>top</literal>.
922 Only superusers can change this setting.
923 </para>
924 </listitem>
925 </varlistentry>
926
927 <varlistentry>
928 <term>
929 <varname>pg_stat_statements.track_utility</varname> (<type>boolean</type>)
930 <indexterm>
931 <primary><varname>pg_stat_statements.track_utility</varname> configuration parameter</primary>
932 </indexterm>
933 </term>
934
935 <listitem>
936 <para>
937 <varname>pg_stat_statements.track_utility</varname> controls whether
938 utility commands are tracked by the module. Utility commands are
939 all those other than <command>SELECT</command>, <command>INSERT</command>,
940 <command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>.
941 The default value is <literal>on</literal>.
942 Only superusers can change this setting.
943 </para>
944 </listitem>
945 </varlistentry>
946
947 <varlistentry>
948 <term>
949 <varname>pg_stat_statements.track_planning</varname> (<type>boolean</type>)
950 <indexterm>
951 <primary><varname>pg_stat_statements.track_planning</varname> configuration parameter</primary>
952 </indexterm>
953 </term>
954
955 <listitem>
956 <para>
957 <varname>pg_stat_statements.track_planning</varname> controls whether
958 planning operations and duration are tracked by the module.
959 Enabling this parameter may incur a noticeable performance penalty,
960 especially when statements with identical query structure are executed
961 by many concurrent connections which compete to update a small number of
962 <structname>pg_stat_statements</structname> entries.
963 The default value is <literal>off</literal>.
964 Only superusers can change this setting.
965 </para>
966 </listitem>
967 </varlistentry>
968
969 <varlistentry>
970 <term>
971 <varname>pg_stat_statements.save</varname> (<type>boolean</type>)
972 <indexterm>
973 <primary><varname>pg_stat_statements.save</varname> configuration parameter</primary>
974 </indexterm>
975 </term>
976
977 <listitem>
978 <para>
979 <varname>pg_stat_statements.save</varname> specifies whether to
980 save statement statistics across server shutdowns.
981 If it is <literal>off</literal> then statistics are not saved at
982 shutdown nor reloaded at server start.
983 The default value is <literal>on</literal>.
984 This parameter can only be set in the <filename>postgresql.conf</filename>
985 file or on the server command line.
986 </para>
987 </listitem>
988 </varlistentry>
989 </variablelist>
990
991 <para>
992 The module requires additional shared memory proportional to
993 <varname>pg_stat_statements.max</varname>. Note that this
994 memory is consumed whenever the module is loaded, even if
995 <varname>pg_stat_statements.track</varname> is set to <literal>none</literal>.
996 </para>
997
998 <para>
999 These parameters must be set in <filename>postgresql.conf</filename>.
1000 Typical usage might be:
1001
1002 <programlisting>
1003 # postgresql.conf
1004 shared_preload_libraries = 'pg_stat_statements'
1005
1006 compute_query_id = on
1007 pg_stat_statements.max = 10000
1008 pg_stat_statements.track = all
1009 </programlisting>
1010 </para>
1011 </sect2>
1012
1013 <sect2 id="pgstatstatements-sample-output">
1014 <title>Sample Output</title>
1015
1016 <screen>
1017 bench=# SELECT pg_stat_statements_reset();
1018
1019 $ pgbench -i bench
1020 $ pgbench -c10 -t300 bench
1021
1022 bench=# \x
1023 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
1024 nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
1025 FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
1026 -[ RECORD 1 ]---+--------------------------------------------------&zwsp;------------------
1027 query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
1028 calls | 3000
1029 total_exec_time | 25565.855387
1030 rows | 3000
1031 hit_percent | 100.0000000000000000
1032 -[ RECORD 2 ]---+--------------------------------------------------&zwsp;------------------
1033 query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
1034 calls | 3000
1035 total_exec_time | 20756.669379
1036 rows | 3000
1037 hit_percent | 100.0000000000000000
1038 -[ RECORD 3 ]---+--------------------------------------------------&zwsp;------------------
1039 query | copy pgbench_accounts from stdin
1040 calls | 1
1041 total_exec_time | 291.865911
1042 rows | 100000
1043 hit_percent | 100.0000000000000000
1044 -[ RECORD 4 ]---+--------------------------------------------------&zwsp;------------------
1045 query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
1046 calls | 3000
1047 total_exec_time | 271.232977
1048 rows | 3000
1049 hit_percent | 98.8454011741682975
1050 -[ RECORD 5 ]---+--------------------------------------------------&zwsp;------------------
1051 query | alter table pgbench_accounts add primary key (aid)
1052 calls | 1
1053 total_exec_time | 160.588563
1054 rows | 0
1055 hit_percent | 100.0000000000000000
1056
1057
1058 bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
1059 WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';
1060
1061 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
1062 nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
1063 FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
1064 -[ RECORD 1 ]---+--------------------------------------------------&zwsp;------------------
1065 query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
1066 calls | 3000
1067 total_exec_time | 20756.669379
1068 rows | 3000
1069 hit_percent | 100.0000000000000000
1070 -[ RECORD 2 ]---+--------------------------------------------------&zwsp;------------------
1071 query | copy pgbench_accounts from stdin
1072 calls | 1
1073 total_exec_time | 291.865911
1074 rows | 100000
1075 hit_percent | 100.0000000000000000
1076 -[ RECORD 3 ]---+--------------------------------------------------&zwsp;------------------
1077 query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
1078 calls | 3000
1079 total_exec_time | 271.232977
1080 rows | 3000
1081 hit_percent | 98.8454011741682975
1082 -[ RECORD 4 ]---+--------------------------------------------------&zwsp;------------------
1083 query | alter table pgbench_accounts add primary key (aid)
1084 calls | 1
1085 total_exec_time | 160.588563
1086 rows | 0
1087 hit_percent | 100.0000000000000000
1088 -[ RECORD 5 ]---+--------------------------------------------------&zwsp;------------------
1089 query | vacuum analyze pgbench_accounts
1090 calls | 1
1091 total_exec_time | 136.448116
1092 rows | 0
1093 hit_percent | 99.9201915403032721
1094
1095 bench=# SELECT pg_stat_statements_reset(0,0,0);
1096
1097 bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
1098 nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
1099 FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
1100 -[ RECORD 1 ]---+--------------------------------------------------&zwsp;---------------------------
1101 query | SELECT pg_stat_statements_reset(0,0,0)
1102 calls | 1
1103 total_exec_time | 0.189497
1104 rows | 1
1105 hit_percent |
1106 -[ RECORD 2 ]---+--------------------------------------------------&zwsp;---------------------------
1107 query | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit / +
1108 | nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
1109 | FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
1110 calls | 0
1111 total_exec_time | 0
1112 rows | 0
1113 hit_percent |
1114
1115 </screen>
1116 </sect2>
1117
1118 <sect2 id="pgstatstatements-authors">
1119 <title>Authors</title>
1120
1121 <para>
1122 Takahiro Itagaki <email>[email protected]</email>.
1123 Query normalization added by Peter Geoghegan <email>[email protected]</email>.
1124 </para>
1125 </sect2>
1126
1127 </sect1>