pg_stat_statements: Query Performance Monitoring
pg_stat_statements is a database extension that exposes a view, of the same name, to track statistics about SQL statements executed on the database. The following table shows some of the available statistics and metadata:
| Column Name | Column Type | Description |
|---|---|---|
| userid | oid (references pg_authid.oid) | OID of user who executed the statement |
| dbid | oid (references pg_database.oid) | OID of database in which the statement was executed |
| toplevel | bool | True if the query was executed as a top-level statement (always true if pg_stat_statements.track is set to top) |
| queryid | bigint | Hash code to identify identical normalized queries. |
| query | text | Text of a representative statement |
| plans | bigint | Number of times the statement was planned (if pg_stat_statements.track_planning is enabled, otherwise zero) |
| total_plan_time | double precision | Total time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise zero) |
| min_plan_time | double precision | Minimum time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise zero) |
A full list of statistics is available in the pg_stat_statements docs.
For more information on query optimization, check out the query performance guide.
Enable the extension
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "pg_stat_statements" and enable the extension.
Inspecting activity
A common use for pg_stat_statements is to track down expensive or slow queries. The pg_stat_statements view contains a row for each executed query with statistics inlined. For example, you can leverage the statistics to identify frequently executed and slow queries against a given table.
_16select_16 calls,_16 mean_exec_time,_16 max_exec_time,_16 total_exec_time,_16 stddev_exec_time,_16 query_16from_16 pg_stat_statements_16where_16 calls > 50 -- at least 50 calls_16 and mean_exec_time > 2.0 -- averaging at least 2ms/call_16 and total_exec_time > 60000 -- at least one minute total server time spent_16 and query ilike '%user_in_organization%' -- filter to queries that touch the user_in_organization table_16order by_16 calls desc
From the results, we can make an informed decision about which queries to optimize or index.
Resources
- Official pg_stat_statements documentation