The pg_stat_statements extension
Track planning and execution statistics for all SQL statements
pg_stat_statements extension provides a detailed statistical view of SQL statement execution within a Postgres database. It tracks information such as execution counts, total and average execution times, and more, helping database administrators and developers analyze and optimize SQL query performance.
This guide covers:
pg_stat_statements is an open-source extension for Postgres that can be installed on any Neon project using the instructions below.
The version of
pg_stat_statements available on Neon depends on the version of Postgres you select for your Neon project.
- Postgres 14 -
- Postgres 15 -
- Postgres 16 -
You can enable the extension by running the following
CREATE EXTENSION statement in the Neon SQL Editor or from a client such as
psql that is connected to Neon.
This section provides
pg_stat_statements usage examples.
Query the pg_stat_statements view
The main interface is the
pg_stat_statements view, which contains one row per distinct database query, showing various statistics.
The view contains details like those shown below:
For a complete list of
pg_stat_statements columns and descriptions, see The pg_stat_statements View.
Let's explore some example usage patterns.
Find the most frequently executed queries
The most frequently run queries are often critical paths and optimization candidates.
This query retrieves details about the most frequently executed queries, ordered by the number of calls. Only the top 10 rows are returned (
Monitor slow queries
A high average runtime can indicate an inefficient query.
The query below uses the
mean_exec_time (average execution time per call), and
calls columns. The condition
WHERE mean_exec_time > 1 filters out queries with an average execution time greater than 1 unit (you may adjust this threshold as needed).
This query returns the following results:
This query retrieves the top 10 queries with the highest average execution time, focusing on queries run more than 500 times, for the current user.
This query returns the 10 longest-running queries for the current user, focusing on those executed over 500 times and with some cache usage. It orders queries by frequency and cache efficiency to highlight potential areas for optimization.
This query retrieves the top 10 longest-running queries (in terms of mean execution time), focusing on queries executed more than 500 times, for the current user.
Find queries that return many rows
To identify queries that return a lot of rows, you can select the
rows columns, representing the SQL statement and the number of rows returned by each statement, respectively.
This query returns results similar to the following:
Find the most time-consuming queries
The following query returns details about the most time-consuming queries, ordered by execution time.
When executed, the
pg_stat_statements_reset() function resets the accumulated statistical data, such as execution times and counts for SQL statements, to zero. It's particularly useful in scenarios where you want to start fresh with collecting performance statistics.
In Neon, only neon_superuser roles have the privilege required to execute this function. The default role created with a Neon project and roles created in the Neon Console, CLI, and API are granted membership in the
Last updated on