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.

1
select
2
	calls,
3
	mean_exec_time,
4
	max_exec_time,
5
	total_exec_time,
6
	stddev_exec_time,
7
	query
8
from
9
	pg_stat_statements
10
where
11
    calls > 50                   -- at least 50 calls
12
    and mean_exec_time > 2.0     -- averaging at least 2ms/call
13
    and total_exec_time > 60000  -- at least one minute total server time spent
14
    and query ilike '%user_in_organization%' -- filter to queries that touch the user_in_organization table
15
order by
16
	calls desc

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

Resources