pg_stat_statements: Query Performance Monitoring - Supabase

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

<Tabs scrollable size="small" type="underlined" defaultActiveId="dashboard" queryGroup="database-method"

Go to the Database page in the Dashboard.
Click on Extensions in the sidebar.
Search for "pg_stat_statements" and enable the extension.

</TabPanel> <TabPanel id="sql" label="SQL"> ```sql -- 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.

</TabPanel>
</Tabs>

## 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.
```sql
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