Find slow PostgreSQL queries with pg_stat_statements

pg_stat_statements is a PostgreSQL extension that keeps one row of running totals for every distinct query your database has executed: how many times it ran, how long it took in total, how many rows it returned and how many buffers it touched. You sort that view by total time, take the top five, and you have the list of queries worth your attention. It is the single most useful view for performance work, because it answers the question that guessing cannot: which queries actually cost this server the most. Switching it on needs a library loaded at server start, one CREATE EXTENSION, and about a megabyte of shared memory. This guide covers the setup, every column worth reading, the queries to run, and how to pass what you find to EXPLAIN.

What pg_stat_statements records, and what it does not#

Every time a statement finishes, PostgreSQL strips out its literal values, hashes what remains into a queryid, and adds the timing and I/O figures to that entry. Two requests that differ only in their parameters share a row:

SELECT * FROM orders WHERE customer_id = 42;SELECT * FROM orders WHERE customer_id = 9001;

Both become one entry, stored as SELECT * FROM orders WHERE customer_id = $1. That is query normalisation, and it is the reason the view is useful: the application's thousand variations collapse into the handful of query shapes your code really contains.

What you get is a cumulative picture, not a history. The counters start at zero when the extension first loads and grow until you reset them. That tells you which queries dominate over a long period, but not when a query got slow or at what hour. If you need that, you take snapshots of the view on a schedule (covered further down), or you use the log-based tools in the section on complements.

Three limits are worth knowing before you rely on it:

• It does not store parameters. You see $1, never the value that was slow. For the specific value, use the log (log_min_duration_statement) or auto_explain.
• It counts execution time, not waiting time in your application. A query that runs for 3 ms but sits in a connection pool queue for 400 ms looks fast here. See connection pools and limits for that side.
• It keeps a bounded number of entries. The default is 5,000 distinct statements; when the table is full, the least-used entries are evicted. Most small applications never reach that, but one that builds SQL with varying shapes can.

Check whether it is already installed#

Do this before you change anything. On many hosts, the extension's files are present but not enabled, or it is already enabled and nobody told you. Connect with psql and run:

SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name = 'pg_stat_statements';SELECT extname, extversion FROM pg_extension;SHOW shared_preload_libraries;

Read the three results together:

The last row is the one that surprises people. CREATE EXTENSION can succeed without the library loaded, and then selecting from the view fails with pg_stat_statements must be loaded via "shared_preload_libraries". The extension is per database, but once it is collecting, the view in any database shows statements from all databases on the instance, with a dbid column to tell them apart.

Whether you can change shared_preload_libraries depends entirely on where your database runs. On a server you administer yourself, you can. On a hosted database it may be set for you, may be changeable only by the provider, or may not be exposed at all. We do not state anywhere that RE:NODE's PostgreSQL plans allow this, so the safe route is the check above: if the extension is not there, open a ticket from the panel and ask support whether it can be enabled on your instance. Do not assume either answer. Everything after the setup section works on any host where the view exists.

Enabling it on a server you control#

If you run PostgreSQL yourself, or your provider lets you edit the configuration, there are three steps. The library has to be loaded when the server starts, because it allocates shared memory for its table.

First, add it to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max = 5000pg_stat_statements.track = topcompute_query_id = autotrack_io_timing = on

If the line already lists other libraries, keep them and separate with commas. Overwriting an existing shared_preload_libraries is a classic way to turn off something else you needed. The equivalent without editing the file by hand is ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';, which writes to postgresql.auto.conf.

Second, restart the server. A reload (SELECT pg_reload_conf();) is not enough for this setting. Plan it for a quiet moment; the restart drops every connection, and PostgreSQL tuning for small servers lists which other settings share that requirement, so you can batch them into one restart.

Third, create the extension in the database you will query from:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

That needs a role allowed to create extensions; on a self-managed server it is a superuser. Confirm it works:

SELECT count(*) FROM pg_stat_statements;

A number, even a small one, means it is collecting. If you get the "must be loaded" error, the restart did not pick up the setting, so run SHOW shared_preload_libraries; to see what the server actually loaded.

After a major version upgrade, run ALTER EXTENSION pg_stat_statements UPDATE; in each database that has it. The view's columns change between versions and the old definition keeps the old columns until you do.

The settings that matter#

Four parameters control the extension. Only max needs a restart.

top is right for nearly everyone. Switch to all only when a slow stored function is the suspect and you want to see which statement inside it is responsible; the cost is more entries and more noise. pg_stat_statements.track_planning also exists and defaults to off, because measuring planning time adds overhead on busy servers. Turn it on briefly if you suspect planning time is the problem, for example with very wide partitioned tables.

Two settings outside the extension decide how useful the numbers are:

• `compute_query_id` (PostgreSQL 14 and later) defaults to auto, which turns query identifiers on when pg_stat_statements is loaded. Leave it. The same identifier then appears in pg_stat_activity.query_id and in EXPLAIN (VERBOSE), which lets you join a live query to its history.
• `track_io_timing` defaults to off. With it off, the block read and write time columns stay at zero, and you cannot tell whether a query is slow because of disk. On modern hardware the overhead is small; pg_test_timing measures it for your machine. Turn it on if you can.

A final note on memory: the table is small. Five thousand entries is a few megabytes at most, and the query texts live in a file on disk. You will not notice it on a 1 GB server.

The columns, and the version trap#

Run \d pg_stat_statements in psql to see the exact list for your version. The ones you will use daily:

And the I/O columns, which say whether the time went into memory or disk:

A few columns deserve interpretation. shared_blks_read is not necessarily disk: it is blocks that were not in PostgreSQL's own buffers, and the operating system cache may still have served them. A high number still means the query touches more data than fits in shared_buffers, which is the signal to look at the plan. temp_blks_written above zero means a sort or hash ran out of work_mem and wrote to disk, which is a different fix from a missing index. On PostgreSQL 17 and later, the I/O timing columns were renamed with a shared_ prefix, for example shared_blk_read_time; the same \d check tells you which you have.

The three top-N queries#

Everything above exists to run these. The same view, sorted three ways, answers three different questions.

By total time: where the server spends its day

SELECT queryid,       calls,       round(total_exec_time::numeric, 1)                      AS total_ms,       round(mean_exec_time::numeric, 2)                       AS mean_ms,       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,       rows,       left(query, 80)                                         AS queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;

This is the query to run first and the one to run most often. The pct column shows each statement's share of all execution time; on a typical application three or four entries add up to most of it. A query that takes 4 ms but runs two million times a day, at 8,000 seconds in total, costs more than a nine-second report that runs once a night. It is also often the easier one to fix, because it usually needs one index or one fewer round trip.

By mean time: the individually slow queries

SELECT queryid,       calls,       round(mean_exec_time::numeric, 2)   AS mean_ms,       round(stddev_exec_time::numeric, 2) AS stddev_ms,       left(query, 80)                     AS queryFROM pg_stat_statementsWHERE calls >= 20ORDER BY mean_exec_time DESCLIMIT 10;

The calls >= 20 filter matters. Without it, a one-off CREATE INDEX or a manual VACUUM tops the list and teaches you nothing. This ordering finds the queries a user feels: the report page, the export, the search with the leading wildcard. Look at stddev_ms next to the mean. A mean of 50 ms with a standard deviation of 400 ms is not a slow query, it is a query with two behaviours, often because the planner picks different plans depending on the parameter. That one is a job for auto_explain, below.

By calls: the chatty queries

SELECT queryid,       calls,       round(mean_exec_time::numeric, 3) AS mean_ms,       rows / calls                      AS rows_per_call,       left(query, 80)                   AS queryFROM pg_stat_statementsORDER BY calls DESCLIMIT 10;

A query with millions of calls and a tiny mean is the signature of an N+1 pattern: the application fetches a list, then runs one query per item. No index fixes that; a join or a batched WHERE id = ANY($1) does. The rows_per_call column helps: a statement returning exactly one row, called a million times, is looking things up one at a time.

By cache misses and temp files

Two more orderings are worth keeping as saved snippets. The first finds queries that read the most data, regardless of how fast it returned:

SELECT queryid,       shared_blks_hit,       shared_blks_read,       round(100.0 * shared_blks_hit             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,       left(query, 70) AS queryFROM pg_stat_statementsORDER BY shared_blks_read DESCLIMIT 10;

The second, ORDER BY temp_blks_written DESC, lists the queries spilling sorts to disk. Neither is urgent on its own. A hit percentage in the high nineties is normal; a frequently-called statement at 70 percent is churning your cache and evicting data that other queries need.

Measuring a window, and resetting#

Because the numbers are cumulative, last month's behaviour is mixed in with today's. To measure a specific period, such as the hour after a deploy, clear the counters, wait, then read:

SELECT pg_stat_statements_reset();

On PostgreSQL 12 and later the function also accepts userid, dbid and queryid arguments, so you can clear a single statement after fixing it and see whether it behaves. By default only a superuser may call it, and it is not reversible, so record what you need first. A cheap way to keep history without extra tooling is a snapshot table:

CREATE TABLE stmt_snapshot ASSELECT now() AS taken_at, queryid, calls, total_exec_time, rows, queryFROM pg_stat_statements;-- later: INSERT INTO stmt_snapshot SELECT now(), queryid, calls, ... FROM pg_stat_statements;

Subtract one snapshot's calls and total_exec_time from the next to get the cost between them. Run it from a scheduler such as cron or pg_cron hourly and you have a week of trend data. Keep the table small by pruning old rows, and the query text only needs to be stored once if you keep it in a separate table by queryid.

If the server crashes or the process is killed, pg_stat_statements.save does not help: the counters are lost, because they are only written on a clean shutdown. Treat the view as a recent-activity window, not a permanent record.

From a queryid to a fix: EXPLAIN (ANALYZE, BUFFERS)#

The view tells you which query to look at. It cannot tell you why it is slow. Copy the normalised text, put in a realistic value for each $1, and run it under EXPLAIN with the options that expose I/O:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT * FROM ordersWHERE customer_id = 42 AND status = 'open'ORDER BY created_at DESCLIMIT 20;

ANALYZE runs the statement for real, so wrap anything that writes in a transaction you roll back: BEGIN; EXPLAIN (ANALYZE, BUFFERS) UPDATE ...; ROLLBACK;. BUFFERS adds the Buffers: shared hit=... read=... lines, which you can compare to the shared_blks_hit and shared_blks_read columns from the view. If the view says this query reads 40,000 blocks per call, the plan should show a node that explains it, usually a sequential scan or a sort. The full vocabulary is in EXPLAIN ANALYZE: reading Postgres query plans; this post only gets you to the right query.

Use values that match production. A plan for customer_id = 1 may differ from one for the customer with two million rows, and the statistics-driven planner will choose differently. The usual fixes follow from what the plan shows:

• A sequential scan on a large table with a selective filter points to a missing index; PostgreSQL indexes: types, order and cost covers column order and partial indexes.
• A very high Rows Removed by Filter means the index exists but does not match the filter.
• An estimate far from the actual row count means stale statistics: run ANALYZE tablename;.
• A table that has grown to many times its needed size, with vacuum behind, makes every scan slower; see Postgres vacuum, bloat and autovacuum.

After the change, reset that one queryid (or take a snapshot first), let traffic run, and compare mean_exec_time. A fix you have not measured in pg_stat_statements afterwards is a guess.

Live queries: pg_stat_activity#

pg_stat_statements is history. For what is happening this second, such as a hung page or a connection count that jumps, use pg_stat_activity:

SELECT pid,       usename,       state,       wait_event_type,       wait_event,       now() - query_start AS runtime,       left(query, 80)     AS queryFROM pg_stat_activityWHERE state <> 'idle'  AND pid <> pg_backend_pid()ORDER BY runtime DESC;

The state column is the first thing to read. active is running now; idle in transaction means a client opened a transaction and is doing nothing, which holds locks and stops vacuum from cleaning up. A long list of idle in transaction sessions is an application bug, not a database one. wait_event_type of Lock means the query is queued behind another, and pg_blocking_pids(pid) returns who is holding it.

To stop a runaway, SELECT pg_cancel_backend(pid); cancels the current query and keeps the connection; pg_terminate_backend(pid) kills the whole session. Try the first one first. From PostgreSQL 14, pg_stat_activity.query_id matches pg_stat_statements.queryid, so you can see how a query running right now has behaved historically:

SELECT a.pid, now() - a.query_start AS runtime, s.calls, s.mean_exec_timeFROM pg_stat_activity aJOIN pg_stat_statements s ON s.queryid = a.query_idWHERE a.state = 'active';

Note that pg_stat_activity.query is truncated at track_activity_query_size, 1024 bytes by default. A generated query with a huge IN list will be cut off there, and the setting needs a restart to raise.

Complements: log_min_duration_statement and auto_explain#

The statistics view is aggregated, so it hides individual events. Two tools fill that gap, and they cover each other's weaknesses.

`log_min_duration_statement` writes every statement slower than a threshold to the server log, including the actual parameter values:

ALTER SYSTEM SET log_min_duration_statement = '500ms';SELECT pg_reload_conf();

This takes a reload, not a restart. It costs nothing when nothing is slow, and it answers the question the view cannot: which exact value made it slow, and at what time. Use 0 only briefly, since it logs everything. If the log is on a host you can only read through a console, a moderate threshold such as 500 ms or 1 s keeps it readable.

`auto_explain` goes one step further and logs the plan itself for slow statements, which catches the plan that only goes wrong occasionally. It is loaded the same way as pg_stat_statements, in shared_preload_libraries (or session_preload_libraries), and configured like this:

shared_preload_libraries = 'pg_stat_statements,auto_explain'auto_explain.log_min_duration = '1s'auto_explain.log_analyze = offauto_explain.log_buffers = offauto_explain.log_nested_statements = off

Keep log_analyze off unless you need actual timings: turning it on instruments every statement, including fast ones, and can slow a busy database noticeably. A superuser can also run LOAD 'auto_explain'; and set the parameters for one session to test without a restart. The decision rule: pg_stat_statements tells you what to look at, log_min_duration_statement tells you when and with what values, auto_explain tells you what plan was chosen. MySQL users will recognise the middle one from the MySQL slow query log.

A note on keeping this healthy as a habit: pick the top five by total time once a week, and the top five by mean time with calls >= 20. If the same names appear a month later, the fix did not land. Monitoring that tells you something is about making numbers like these something you actually look at.

Privileges: who can see what#

By default a role sees the full text and statistics for its own statements only. For other users' rows, pg_stat_statements shows the stats but replaces the text with <insufficient privilege> and hides the queryid column. Superusers see everything. For everyone else, the clean solution is the built-in pg_read_all_stats role, available from PostgreSQL 10:

GRANT pg_read_all_stats TO monitoring_user;

It grants read access to the statistics views, including pg_stat_statements and other sessions' queries in pg_stat_activity, without granting data access, so it is the right grant for a dashboard or an engineer who should not read table contents. Resetting is separate: pg_stat_statements_reset() is executable only by a superuser unless you GRANT EXECUTE on it, and CREATE EXTENSION also needs sufficient rights. PostgreSQL roles and permissions covers how to build such a role properly.

Query text reveals your schema and workload even with constants removed, so treat access to the view accordingly and think before handing it to third-party tools.

Using it on a hosted database#

On a managed or hosted PostgreSQL service, the setup section may not apply to you. Three situations are common: the extension is already enabled and you only need the queries above; the extension is available and only needs CREATE EXTENSION, which a normal database owner may or may not be allowed to run; or the server-level configuration is the provider's and you cannot touch it.

For RE:NODE's PostgreSQL line, which starts from $6 a month, the documented facts are that credentials are generated per server and you connect on the plan's host and port. Nothing there states whether shared_preload_libraries is editable or which extensions are available. So the practical order is:

1. Connect with psql and run the three checks from the earlier section.
2. If pg_stat_statements is installed and the view answers, use it as described.
3. If it is not, ask support through a ticket from the panel, naming the extension and what you want it for. Whether it can be enabled on your instance is theirs to say.
4. In the meantime, pg_stat_activity and the cumulative views such as pg_stat_user_tables work without any setup, and EXPLAIN (ANALYZE, BUFFERS) needs nothing at all.

If you can get a log, a threshold on log_min_duration_statement is the next most useful thing. If you can get neither, run the slow-looking queries from your own code under EXPLAIN in a transaction you roll back, and add timing around them in the application. It is slower than the view, but it finds the same queries.

FAQ#

Does pg_stat_statements slow the database down?

Slightly. The cost is a small, fixed overhead per statement for hashing and updating shared counters, which is hard to measure on most workloads. The heavier settings are track_planning and auto_explain.log_analyze, so leave those off until you need them. Running pg_stat_statements in production permanently is normal practice.

Why does my query text show $1 instead of the value?

Because the extension normalises statements so that variations of the same query share one row. The values are deliberately not kept. To find the value that was slow, set log_min_duration_statement, or use auto_explain. Applications that send values inline, rather than as bind parameters, still get normalised; the stripping happens inside the server.

Why is the view empty, or erroring, after CREATE EXTENSION?

Either the library is not in shared_preload_libraries, or the server was not restarted after you added it. SHOW shared_preload_libraries; shows what is loaded now. The error message pg_stat_statements must be loaded via "shared_preload_libraries" means exactly that. An empty view on a loaded extension just means nothing has run since the last reset.

Should I sort by total time or mean time?

Start with total time, because it tells you what costs the server most overall and where a fix pays back. Use mean time, filtered with calls >= 20, when the complaint is that one specific page feels slow. They answer different questions and you will look at both.

Does it work on MySQL, MongoDB or SQL Server?

No, it is PostgreSQL only. MySQL has the slow query log and the performance_schema digest tables; SQL Server has Query Store, covered in SQL Server Query Store and slow queries. The idea is the same everywhere: aggregate by normalised statement, sort by total cost, fix the top of the list.

How do I know which queryid belongs to which table?

Search the text: WHERE query ILIKE '%orders%'. The queryid is a hash of the parsed statement and carries no table information of its own. For every statement against one table over a period, filter on the text, sum total_exec_time, and compare with the table's scan counters in pg_stat_user_tables.