| 1 |
<!-- doc/src/sgml/pgstatstatements.sgml --> |
| 2 |
|
| 3 |
<sect1 id="pgstatstatements" xreflabel="pg_stat_statements"> |
| 4 |
<title>pg_stat_statements — 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> |