pg_stat_statements Query Builder

pg_stat_statements is the fastest way to find out what a PostgreSQL server actually spends its time on, but the column names have moved twice: PostgreSQL 13 renamed total_time to total_exec_time when it split planning from execution, and PostgreSQL 17 renamed blk_read_time to shared_blk_read_time. That is why the query you copied from a blog post errors with "column does not exist". Pick your major version and what you are hunting for, and this builder writes the Top-N query with the right column names, the percentage-of-total column, a cache hit ratio, the filters that keep BEGIN and COMMIT out of the results, plus the extension setup and the reset commands. It all runs in your browser.

PostgreSQL versionRank byRowsMin calls

Database filterRole filter

Hide BEGIN / COMMIT / SET noiseTruncate query text to 120 charsAdd percent-of-total column

Your query

-- Top 20 statements by Total time (find the real load)
-- Column names below are the PostgreSQL 17+ spelling.
SELECT
  d.datname AS database,
  r.rolname AS role,
  s.calls,
  round(s.total_exec_time::numeric, 1) AS total_ms,
  round(s.mean_exec_time::numeric, 2) AS mean_ms,
  round(s.max_exec_time::numeric, 2) AS max_ms,
  round(s.stddev_exec_time::numeric, 2) AS stddev_ms,
  s.rows,
  round(100.0 * s.total_exec_time / nullif(sum(s.total_exec_time) OVER (), 0), 1) AS pct_total,
  round(100.0 * s.shared_blks_hit / nullif(s.shared_blks_hit + s.shared_blks_read, 0), 1) AS cache_hit_pct,
  s.shared_blks_read,
  left(regexp_replace(s.query, '\s+', ' ', 'g'), 120) AS query
FROM pg_stat_statements s
LEFT JOIN pg_database d ON d.oid = s.dbid
LEFT JOIN pg_roles r    ON r.oid = s.userid
WHERE s.calls >= 10
  AND s.query !~* '^(BEGIN|COMMIT|ROLLBACK|SET|SHOW|DEALLOCATE|DISCARD|COPY|VACUUM|ANALYZE)'
  AND d.datname IS NOT NULL
ORDER BY s.total_exec_time DESC
LIMIT 20;

Enable the extension

-- 1. pg_stat_statements must be preloaded. In postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000        -- statements tracked before eviction
pg_stat_statements.track = top        -- 'all' also tracks nested statements
pg_stat_statements.track_utility = off
track_io_timing = on                  -- needed for the read/write time columns

-- 2. Restart PostgreSQL (shared_preload_libraries needs a full restart),
--    then create the extension once per database you want to inspect:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 3. Confirm it is live and see how far back the numbers go:
SELECT * FROM pg_stat_statements_info;   -- PostgreSQL 14+
SELECT count(*) AS statements FROM pg_stat_statements;

-- Non-superusers need pg_read_all_stats to see other users' queries:
-- GRANT pg_read_all_stats TO your_monitoring_role;

Related queries

-- Time spent waiting on disk, not on CPU (needs track_io_timing = on):
SELECT
  round(s.shared_blk_read_time::numeric, 1)  AS read_ms,
  round(s.shared_blk_write_time::numeric, 1) AS write_ms,
  round(s.total_exec_time::numeric, 1) AS total_ms,
  s.calls,
  left(regexp_replace(s.query, '\s+', ' ', 'g'), 100) AS query
FROM pg_stat_statements s
ORDER BY s.shared_blk_read_time + s.shared_blk_write_time DESC
LIMIT 20;

-- Rollup per database: where is the time actually going?
SELECT d.datname,
       sum(s.calls) AS calls,
       round(sum(s.total_exec_time)::numeric / 1000, 1) AS total_seconds
FROM pg_stat_statements s
JOIN pg_database d ON d.oid = s.dbid
GROUP BY 1 ORDER BY 3 DESC;

-- Read the full text of one statement you truncated above:
SELECT query FROM pg_stat_statements WHERE queryid = 123456789;

-- Reset before a benchmark, so the numbers cover a known window:
SELECT pg_stat_statements_reset();
-- Or reset a single statement instead of everything (PostgreSQL 12+):
-- SELECT pg_stat_statements_reset(userid, dbid, queryid)
--   FROM pg_stat_statements WHERE queryid = 123456789;

How to read the numbers

PostgreSQL 13 renamed total_time to total_exec_time and mean_time to mean_exec_time when it split out planning time. Queries copied from older blog posts fail on modern servers with "column total_time does not exist".

PostgreSQL 17 renamed blk_read_time and blk_write_time to shared_blk_read_time and shared_blk_write_time, and added separate local_ and temp_ variants. The IO query uses the new names.

Sort by total time, not mean time, when you want impact. A 4 ms query called two million times an hour costs far more than a 3-second report that runs twice a day - mean time finds the second one and hides the first.

The numbers are cumulative since the last reset or server start, so a top-N list on a long-running server is a history lesson. Reset, wait a representative interval, then read - pg_stat_statements_info.stats_reset (PostgreSQL 14+) tells you when the window opened.

Statements are normalised: constants are replaced with $1, $2, so every query of the same shape aggregates into one row. That is what makes the list useful, but it also means one row can hide a fast plan and a slow plan for different parameter values. Check the max_ms and stddev_ms columns - a large gap between mean and max is the signal.

If pg_stat_statements.max is too low, rare statements are evicted and their history is lost. pg_stat_statements_info.dealloc counting up means you should raise it.

The view does not store plans. Once this list names a statement, run EXPLAIN (ANALYZE, BUFFERS) on it to find out why it is slow.

Do more than pg_stat_statements query builder — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Select your PostgreSQL major version so the generated query uses the column names your server actually has.
Choose what you are looking for - total time for load, mean time for a slow statement, temp blocks for queries spilling to disk - and set the row limit and minimum call count.
Run the setup block once if the extension is not installed yet, then run the generated query and EXPLAIN the statements it puts at the top.

Frequently asked questions

Why does my pg_stat_statements query fail with "column total_time does not exist"?

Because you are on PostgreSQL 13 or newer and the query was written for 12 or older. Version 13 separated planning time from execution time, so total_time became total_exec_time, mean_time became mean_exec_time, min_time and max_time and stddev_time gained the same _exec_ infix, and new total_plan_time and mean_plan_time columns appeared. Version 17 made a second rename in the IO columns: blk_read_time and blk_write_time became shared_blk_read_time and shared_blk_write_time, with separate local_blk_ and temp_blk_ variants alongside them. Neither change is backward compatible, which is why monitoring scripts and blog snippets break silently across upgrades. Select your major version above and the generated SQL uses the correct spelling.

How do I enable pg_stat_statements?

It ships with PostgreSQL as a contrib module but is not active by default, because it needs shared memory allocated at startup. Add pg_stat_statements to shared_preload_libraries in postgresql.conf and restart the server - a reload is not enough for this parameter. Then run CREATE EXTENSION pg_stat_statements once in each database where you want to query the view. Set track_io_timing = on as well if you want the read and write time columns to hold anything other than zero. On managed services the steps differ slightly: on Amazon RDS and Aurora you add it to shared_preload_libraries in the parameter group and reboot, on Azure Database for PostgreSQL and Cloud SQL it is a server flag, and on several platforms the extension is already enabled. Non-superusers need membership in pg_read_all_stats to see statements from other users; without it they only see their own.

Should I sort by total_exec_time or mean_exec_time?

Sort by total_exec_time when you want to reduce load, and by mean_exec_time when you are chasing a specific slow statement a user complained about. The distinction matters more than it sounds: a 4 ms query executed two million times an hour consumes far more of the server than a 3-second report that runs twice a day, but a mean-time sort puts the report on top and buries the query that is actually saturating your CPU. A good habit is to read the total-time list first, then look at the max and stddev columns on those rows. A statement whose mean is 5 ms but whose max is 9 seconds is not a slow query - it is a query with an unstable plan or a locking problem, and the fix is different. Once you have the statement text, EXPLAIN (ANALYZE, BUFFERS) tells you why. Chat2DB can run these monitoring queries and the EXPLAIN side by side against the same connection: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.