Statements
Overview
If Statement Tracking is enabled, the Statements dashboard shows a number of the top statements collected from the server, sorted and limited by fields specified in the Agent Properties. The bar graph can be customized using the fields above it to show top statements by several categories. The graphs below show workload, statement counts, and system utilization metrics (if host is being monitored), in order to allow the user to correlate potentially problematic statements with impact on the system. Below, a table lists all collected statements that have been called in the selected time period. Many of these fields are hidden by default for space considerations, but can be made visible by using the customizer at the top right of the table. A default search filter is set in the top-right section of the table to exclude calls made to system databases, usually internal processes or monitoring queries. This can be cleared or modified to show different results in the table. In the action panel on the right pane, the PostgreSQL Agent Selector allows you to switch between servers without leaving the page.
Data
Statements Table
Statement
- User – Name of the user that executed the statement.
- User ID – ID of the user that executed the statement.
- DB – Name of the database in which the statement was executed.
- DB ID – ID of the database in which the statement was executed.
- Query – Text of a representative statement (up to track_activity_query_size bytes). Statements with the same operations plan may have slight differences in the original text, but are represented by a single statement.
- Calls – Number of times the statement has been executed.
- Rows - Total number of rows retrieved or affected by the query.
- Average – Average time spent executing the statement per call.
- Total – Total time spent executing statements.
Memory Blocks
- Write Time - Total time the statement spent writing blocks (if track_io_timing is enabled, otherwise zero).
- Read Time - Total time the statement spent reading blocks (if track_io_timing is enabled, otherwise zero).
- Read Time per Query - Time the statement spent reading blocks per query.
- Read Time per Query - Time the statement spent reading blocks per row retrieved/affected.
Shared Blocks
- Hit Pct – Percentage of shared block cache hits of total blocks read.
- Read – Number of shared blocks read.
- Hit – Number of shared block cache hits.
- Written – Number of shared blocks written.
- Dirtied – Number of shared blocks dirtied.
Local Blocks
- Read – Number of local blocks read.
- Hit – Number of local block cache hits.
- Written – Number of local blocks written.
- Dirtied – Number of local blocks dirtied.
Temp Blocks
- Read – Number of temp blocks read.
- Written – Number of temp blocks written.
Actions
Statements Graph
- Bar Section (dwell) – Popup of selected statement details.
- Bar Section (drilldown) – Navigates to the Statement Details page.
Statements Table
- DB (drilldown) – Navigates to the Databases dashboard with DB selected.
- Query (drilldown) - Shows the full query text in a formatted, colored state for better readability.
- Other metrics (drilldown or dwell) – Shows a popup time plot for the selected metric.
Statement Details
Overview
The Statement Details page provides a more in-depth look at the operations and history of a particular statement as well as its performance on other monitored servers, if the same queryid has been found. This page also features the Explain Plan Request form, which can be used to request an explain plan for the statement if it has been enabled in the agent properties and the Foglight user has the PostgreSQL Administrator role.
Data
Statement Summary
- Agent – Agent monitoring the PostgreSQL Server.
- Server – The host:port of the PostgreSQL server.
- Query ID – An internally-generated hash calculation. For versions below 9.4, where queryid is not available from the server, a hash code has been generated by the agent based solely on a concatenation of the database name and query text o User – Name of the first user that executed the statement.
- Database – Name of the database in which the statement was executed. o Calls – Number of times the statement has been executed.
- Average Time – Average time spent executing the statement per call.
- Total Time – Total time spent executing statements.
- Avg Rows – Average number of rows retrieved or affected by the query.
- Query – Text of a representative statement (up to track_activity_query_size bytes). Statements with the same operations plan may have slight differences in the original text, but are represented by a single statement.
Statements On Other Servers
- This table shows performance of the same query on other monitored PostgreSQL servers on which it may be performed, for comparison to the selected server.
In order to match the queries across servers, this table relies on the queryid which is not guaranteed to be the same across servers.
Per PostgreSQL documentation (https://www.postgresql.org/docs/current/static/pgstatstatements.html):
“As a rule of thumb, queryid values can be assumed to be stable and comparable only so long as the underlying server version and catalog metadata details stay exactly the same. Two servers participating in replication based on physical WAL replay can be expected to have identical queryid values for the same query. However, logical replication schemes do not promise to keep replicas identical in all relevant details, so queryid will not be a useful identifier for accumulating costs across a set of logical replicas. If in doubt, direct testing is recommended.”
Other Graphs
- Represent historical views of metrics listed on the Statements page table.
Actions
Statements On Other Servers Table
Name (drilldown) – Switches the page to represent the same statement as performed on the selected server.