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.
-- Enable the "pg_stat_statements" extension
create extension pg_stat_statements with schema extensions;
-- Disable the "pg_stat_statements" extension
drop extension if exists pg_stat_statements;
Even though the SQL code is create extension, this is the equivalent of "enabling the extension".
To disable an extension you can call drop extension.
It's good practice to create the extension within a separate schema (like extensions) to keep the public schema clean.
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.
select
calls,
mean_exec_time,
max_exec_time,
total_exec_time,
stddev_exec_time,
query
from
pg_stat_statements
where
calls > 50 -- at least 50 calls
and mean_exec_time > 2.0 -- averaging at least 2ms/call
and total_exec_time > 60000 -- at least one minute total server time spent
and query ilike '%user_in_organization%' -- filter to queries that touch the user_in_organization table
order by
calls desc
From the results, we can make an informed decision about which queries to optimize or index.
Resources
- Official pg_stat_statements documentation