Chapter 49. SQL Statistics
PPEM allows viewing execution statistics of SQL statements. This information is provided by the pg_stat_statements and pgpro_stats extensions. For SQL statistics collection to work correctly, one of these extensions must be installed and configured in the instance.
To view SQL statistics:
In the navigation panel, go to Infrastructure → Instances.
Click the name of the instance.
In the navigation panel, go to SQL statistics.
The table with the following columns will be displayed:
Query ID: The non-unique hash code of the statement. It can be the same as that of other statements executed in other databases or on behalf of other users.
This column includes additional information:
Query plan ID: The non-unique hash code of the query plan. It can be the same as that of other plans for statements executed in other databases or on behalf of other users.
This column is displayed only for the Postgres Pro Enterprise edition.
Top level: The statement execution level.
Possible values:
Yes: The statement is executed at the top level.
No: The statement is nested in a procedure or function.
To display this information, set the
pg_stat_statements.trackorpgpro_stats.trackconfiguration parameter totop.
Database: The instance database where the statement was executed.
User: The name of the user who executed the statement.
Calls: The total number of times the statement was executed.
This column includes additional information:
Rows: The total number of rows retrieved or affected by the statement.
Total execution time, ms: The total time spent executing the statement, in milliseconds.
This column includes additional information:
Max: The maximum time spent executing the statement.
Min: The minimum time spent executing the statement.
Mean: The mean time spent executing the statement.
Stddv: The standard deviation of time spent executing the statement.
Total planning time, ms: The total time spent planning the statement, in milliseconds.
To display this information, set the
pg_stat_statements.track_planningorpgpro_stats.track_planningconfiguration parameter toon. Otherwise,0is displayed.This column includes additional information:
Max: The maximum time spent planning the statement.
Min: The minimum time spent planning the statement.
Mean: The mean time spent planning the statement.
Stddv: The standard deviation of time spent planning the statement.
Blocks time, ms: The total time the statement spent reading and writing data file blocks, in milliseconds.
To display this information, enable the
track_io_timingconfiguration parameter. Otherwise,0is displayed.This column includes additional information:
Read: The time spent reading blocks.
Write: The time spent writing blocks.
Temp blocks, pc.: The total number of blocks affected by the statement when working with temporary files.
This column includes additional information:
Read: The number of read blocks.
Write: The number of written blocks.
WAL bytes, B: The total amount of WAL bytes generated during the statement execution.
This column includes additional information:
Records, pc.: The total number of WAL records generated during the statement execution.
FPI, pc.: The total number of WAL full-page images generated during the statement execution.
Shared blocks • Hits, pc.: The total number of shared block cache hits by the statement.
This column includes additional information:
Read: The total number of shared blocks read by the statement.
Dirtied: The total number of shared blocks dirtied by the statement.
Write: The total number of shared blocks written by the statement.
Local blocks • Hits, pc.: The total number of local block cache hits by the statement.
This column includes additional information:
Read: The total number of local blocks read by the statement.
Dirtied: The total number of local blocks dirtied by the statement.
Write: The total number of local blocks written by the statement.
To view the information about a statement, click
next to it.
The displayed statistics are requested via the agent working with the instance. For this reason, the speed at which statistics are retrieved depends on two factors:
network connectivity between the manager and the agent that works with the instance
the volume of transferred data that can also indirectly affect the instance performance
Given the cumulative nature of statistics, the resulting statistics snapshot can differ over time from the actual instance statistics.
Below the statistics, Query and Query plan blocks are displayed. You can perform the following actions in the top-right corner of these blocks:
To copy the query or its plan, click
.
To visualize the query plan, click
.