Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Database

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 NameColumn TypeDescription
useridoid (references pg_authid.oid)OID of user who executed the statement
dbidoid (references pg_database.oid)OID of database in which the statement was executed
toplevelboolTrue if the query was executed as a top-level statement (always true if pg_stat_statements.track is set to top)
queryidbigintHash code to identify identical normalized queries.
querytextText of a representative statement
plansbigintNumber of times the statement was planned (if pg_stat_statements.track_planning is enabled, otherwise zero)
total_plan_timedouble precisionTotal time spent planning the statement, in milliseconds (if pg_stat_statements.track_planning is enabled, otherwise zero)
min_plan_timedouble precisionMinimum 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

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. 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.


_16
select
_16
calls,
_16
mean_exec_time,
_16
max_exec_time,
_16
total_exec_time,
_16
stddev_exec_time,
_16
query
_16
from
_16
pg_stat_statements
_16
where
_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
_16
order by
_16
calls desc

From the results, we can make an informed decision about which queries to optimize or index.

Resources