How to Find Slow Queries in Postgres: pg_stat_statements and EXPLAIN (2026)

This guide is for anyone who has been told "the database is slow" and needs to turn that into a specific query and a specific fix. By the end you will have pg_stat_statements collecting statistics, a query that ranks your workload by total time, the habit of reading EXPLAIN ANALYZE output from the bottom up, a safe way to add the index that fixes the plan, and auto_explain logging slow plans in production so you do not have to reproduce them. Commands match the PostgreSQL 18 documentation (current minor 18.6); the plan output shown was captured on a throwaway PostgreSQL 16.8 instance on my laptop with two million generated rows, and I point out where 18 prints things differently. You need superuser or equivalent access to change postgresql.conf (or your managed provider's parameter settings) and to restart once. For what to do with memory and autovacuum after the queries are fixed, the companion post is PostgreSQL Performance Tuning (2026): What Matters First in Postgres 18.

Step 1: Enable pg_stat_statements

pg_stat_statements records planning and execution statistics for every normalised statement the server runs. The PostgreSQL 18 docs list three requirements: it must be in shared_preload_libraries (which needs a restart), compute_query_id must be auto or on, and you run CREATE EXTENSION in each database where you want to query the view.

# postgresql.conf (PostgreSQL 18)
shared_preload_libraries = 'pg_stat_statements'   # restart required
compute_query_id = on
pg_stat_statements.max = 10000      # default 5000 statements
pg_stat_statements.track = top      # default; 'all' includes statements inside functions
track_io_timing = on                # fills the *_blk_read_time columns

# Restart and create the extension (self-managed install)
sudo systemctl restart postgresql
psql -d appdb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
psql -d appdb -Atc "SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_statements';"

On a managed service such as RDS or Cloud SQL you set the same two parameters in the provider's parameter group or flags, reboot, then run the CREATE EXTENSION. If the view errors with pg_stat_statements must be loaded via "shared_preload_libraries", the restart has not happened yet.

track_io_timing is optional but worth it. It adds a clock read per I/O, which the docs say can be expensive on some platforms; the bundled pg_test_timing utility tells you whether yours is one of them.

Step 2: Rank queries by total execution time

Sort by total_exec_time, not mean_exec_time. A 2 ms query called four million times a day costs more than a 10 second report that runs once, and it is usually easier to fix.

-- Top 10 statements by total execution time since the last reset
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_total,
  rows,
  shared_blks_hit,
  shared_blks_read,
  temp_blks_written,
  left(query, 80)                                          AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;

Run on my test database after a handful of queries (output trimmed to four columns), the relevant rows looked like this:

 calls | total_ms | mean_ms | shared_blks_read | query
-------+----------+---------+------------------+----------------------------------------------------------
     2 |    236.7 |  118.36 |            32742 | SELECT count(*) FROM orders WHERE status = $1 AND total > $2
     1 |      0.0 |    0.05 |               23 | SELECT id FROM orders WHERE customer_id = $1 ORDER BY create

Note the $1 and $2. Two queries with different literals were folded into one entry with calls = 2, which is what makes the ranking meaningful. To see the real parameter values you need logs or auto_explain (Step 7).

Three other columns tell you what kind of problem you have before you look at a plan: a high shared_blks_read relative to rows means the query reads far more data than it returns (usually a missing index), nonzero temp_blks_written means sorts or hashes spilled to disk (work_mem), and a large rows with a high mean_exec_time may just be a query returning too much data.

Step 3: Run EXPLAIN ANALYZE safely

EXPLAIN shows the plan the optimiser chose with its estimates. EXPLAIN ANALYZE actually runs the statement and adds real timings and row counts. That second part matters: EXPLAIN ANALYZE on an UPDATE or DELETE really modifies data, so wrap it in a transaction you roll back.

-- Read-only query: safe as is
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

-- Write query: run inside a transaction and roll it back
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'shipped' WHERE customer_id = 4242;
ROLLBACK;

On PostgreSQL 18 you can drop the BUFFERS option, because the release notes made it automatic for EXPLAIN ANALYZE. I still type it so the same command works on 16 and 17. Copy the literal values from a real slow call rather than inventing them: a query that is slow for a customer with a million orders may be fast for one with five.

Step 4: Read the plan from the bottom up

Here is the real plan for the query above before any index existed (PostgreSQL 16.8, two million rows):

Limit  (cost=41388.09..41388.14 rows=20 width=22) (actual time=62.483..62.484 rows=20 loops=1)
  Buffers: shared hit=12277 read=4110 written=32
  ->  Sort  (cost=41388.09..41388.19 rows=41 width=22) (actual time=62.482..62.483 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 26kB
        Buffers: shared hit=12277 read=4110 written=32
        ->  Seq Scan on orders  (cost=0.00..41387.00 rows=41 width=22) (actual time=4.685..62.452 rows=48 loops=1)
              Filter: (customer_id = 4242)
              Rows Removed by Filter: 1999952
              Buffers: shared hit=12277 read=4110 written=32
Planning Time: 0.494 ms
Execution Time: 62.496 ms

Read the innermost node first, then work outwards. What to look at, in order:

• Node type. Seq Scan reads every page of the table. That is correct for small tables or queries that return a large share of rows, and wrong when Rows Removed by Filter is huge compared to rows returned. Here 1,999,952 rows were thrown away to find 48.
• Buffers. shared hit pages came from shared buffers, read came from the kernel or disk. 12,277 plus 4,110 is about 16,400 pages of 8kB, roughly 128MB touched for 20 rows. Buffers are a better measure of work than time, because time depends on what was cached when you ran it.
• Estimated versus actual rows. The planner guessed rows=41 and got 48, which is close. When those numbers differ by 10x or more, the plan was chosen on bad information, and Step 6 applies.
• Loops. In a Nested Loop, the inner node's actual time and rows are per loop. An inner index scan showing actual time=0.050 with loops=200000 is ten seconds in total. Nested loops are excellent when the outer side is small and the inner side is an index lookup, and terrible when the planner underestimated the outer side.
• Sort Method. quicksort or top-N heapsort with a memory figure stayed in RAM. external merge Disk: ...kB spilled because the sort exceeded work_mem.

On PostgreSQL 18 the same plan shows row counts with two decimals (for example rows=20.00), prints buffers without being asked, and adds an Index Searches line to index scans, as described in the 18 release announcement. The reading order does not change. If a plan is long, paste it into a visualiser such as explain.depesz.com, but learn to read the text first.

Step 5: Add the right index, concurrently

The query filters on customer_id and sorts on created_at, so a composite index in that order lets Postgres jump to the customer and read rows already sorted, stopping after 20. On a live table, build it with CONCURRENTLY so writes are not blocked:

-- Builds without taking a lock that blocks INSERT/UPDATE/DELETE.
-- Cannot run inside a transaction block.
CREATE INDEX CONCURRENTLY orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

-- A failed concurrent build leaves an INVALID index behind: check for it.
SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;

The plan afterwards, same query, same data:

Limit  (cost=0.43..82.73 rows=20 width=22) (actual time=0.028..0.092 rows=20 loops=1)
  Buffers: shared hit=7 read=16 written=16
  ->  Index Scan using orders_customer_created_idx on orders  (cost=0.43..169.14 rows=41 width=22) (actual time=0.027..0.090 rows=20 loops=1)
        Index Cond: (customer_id = 4242)
        Buffers: shared hit=7 read=16 written=16
Planning Time: 0.132 ms
Execution Time: 0.098 ms

62.5 ms became 0.098 ms, and pages touched went from about 16,400 to 23. The Sort node disappeared because the index already returns rows in the requested order.

Rules I follow when choosing the index:

• Equality columns first, then the range or sort column. (customer_id, created_at) serves WHERE customer_id = ? ORDER BY created_at; (created_at, customer_id) mostly does not, although 18's skip scan can rescue some leading-column mismatches when that column has few distinct values.
• Use INCLUDE (total) if you want an index-only scan and the extra width is acceptable.
• Use a partial index (WHERE status = 'refunded') when queries always filter on a rare value.
• Use GIN for jsonb @>, arrays and full-text search, and BRIN for huge append-only tables queried by time range. The index types docs cover the operators each supports.
• If the index exists but is not used, check the data type (a bigint column compared to a numeric parameter), functions on the column (lower(email) needs an expression index) and leading wildcards (LIKE '%foo').

Step 6: Fix bad row estimates with ANALYZE and extended statistics

When estimated and actual rows are far apart, fix the statistics before you reach for planner hints. Start by refreshing them, then look at correlated columns.

-- 1. Stale statistics after a bulk load are the most common cause
ANALYZE orders;

-- 2. More detailed histograms for one skewed column (default target is 100)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 3. Columns that are correlated (city and postcode, tenant and status):
--    the planner assumes independence unless you tell it otherwise
CREATE STATISTICS orders_cust_status (dependencies, ndistinct, mcv)
  ON customer_id, status FROM orders;
ANALYZE orders;

CREATE STATISTICS is documented in the planner statistics chapter. After each change, rerun the EXPLAIN (ANALYZE, BUFFERS) from Step 3 and compare estimated and actual rows. If they now agree and the plan is still slow, the problem is genuinely the plan shape or a missing index, not the estimate.

Step 7: Log slow plans in production with auto_explain

Some queries are only slow at 3 a.m. with production data and parameters. auto_explain logs the plan of any statement that runs longer than a threshold, so you capture it when it happens.

# postgresql.conf (PostgreSQL 18); restart needed to change shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'   # default -1 disables logging
auto_explain.log_analyze = on             # actual rows and times (adds overhead)
auto_explain.log_buffers = on             # requires log_analyze
auto_explain.log_timing = off             # skip per-node timing, keep row counts
auto_explain.log_format = 'json'          # easier to feed into a log pipeline
auto_explain.sample_rate = 0.1            # explain 10% of statements
auto_explain.log_nested_statements = off  # default; on includes SQL inside functions

The docs warn that log_analyze instruments every statement, not just the slow ones, and can have an extremely negative effect on performance. That is why I turn off log_timing and sample. To try it in one session without a restart, a superuser can run LOAD 'auto_explain'; and then SET auto_explain.log_min_duration = 0;.

If you only need the SQL text and duration, log_min_duration_statement = 500ms is cheaper and logs the statement with its parameters.

Step 8: Verify the fix and reset the baseline

Do not trust a single fast EXPLAIN run, because the second run is often fast simply because the pages are now cached. Compare buffers, then let real traffic confirm it.

-- Reset everything (superuser by default; others need GRANT EXECUTE)
SELECT pg_stat_statements_reset();

-- Or reset just one statement, by queryid from Step 2
SELECT pg_stat_statements_reset(0, 0, 1234567890123456789);

-- Confirm the new index is being chosen in production
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders';

After an hour of normal traffic, rerun the Step 2 query. The statement should have dropped down the list and shared_blks_read per call should be a fraction of what it was. If a new index never shows any idx_scan, drop it: it costs writes and vacuum time for nothing.

Troubleshooting

Symptom	Likely cause	Fix
ERROR: pg_stat_statements must be loaded via "shared_preload_libraries"	Library added but server not restarted, or CREATE EXTENSION run before restart	Add it to shared_preload_libraries, restart, then CREATE EXTENSION pg_stat_statements
pg_stat_statements view is empty or never grows	compute_query_id = off	Set compute_query_id = on (or auto) and reload
query column shows <insufficient privilege>	Role is not the statement owner and lacks pg_read_all_stats	GRANT pg_read_all_stats TO monitoring_role;
Plan uses Seq Scan although an index exists	Type mismatch, function on the column, low selectivity, or stale statistics	Match types, add an expression index, run ANALYZE; test with SET enable_seqscan = off in one session only
Estimated rows 1, actual rows 100,000	Stale or missing statistics, or correlated columns	ANALYZE, raise SET STATISTICS, add CREATE STATISTICS (dependencies, mcv)
Sort Method: external merge Disk: ...	Sort exceeded work_mem	Raise work_mem for that role or with SET LOCAL, or add an index that returns rows pre-sorted
Nested Loop with huge loops on the inner side	Outer row count underestimated	Fix the estimate (Step 6); add an index on the inner join key
CREATE INDEX CONCURRENTLY failed, index listed as INVALID	Build interrupted, deadlock, or unique violation	DROP INDEX CONCURRENTLY the invalid index and rebuild it
auto_explain logs nothing	log_min_duration still -1, or module not loaded in that session	Set auto_explain.log_min_duration, check SHOW shared_preload_libraries
Query fast in psql, slow from the application	Different parameter values, or a generic plan for a prepared statement	Capture parameters with auto_explain; compare with plan_cache_mode = force_custom_plan in a test session

FAQ

How do I find the slowest queries in PostgreSQL?

Enable pg_stat_statements, then sort the view by total_exec_time descending. That shows the statements consuming the most database time overall, which is usually where the biggest wins are. Sort by mean_exec_time only when you are chasing a specific user-facing latency problem.

Is it safe to run EXPLAIN ANALYZE in production?

For a SELECT, it runs the query once, so it costs whatever that query costs. For INSERT, UPDATE or DELETE it really changes data, so wrap it in BEGIN and ROLLBACK. Avoid running it on a query that you already know takes minutes on a busy primary; use a replica or plain EXPLAIN instead.

What does Rows Removed by Filter mean?

It is the number of rows a node read and then discarded because they failed the WHERE condition. A large number relative to the rows returned is the classic sign of a missing or unusable index. In the example above, two million rows were read to return 48.

Does pg_stat_statements slow down Postgres?

The overhead is generally small, and it is enabled by default on many managed services. pg_stat_statements.track_planning is off by default because the docs say it can add noticeable overhead, and track_io_timing can be costly on platforms with slow clocks, which pg_test_timing will reveal.

In production, the first thing I look at is the top five rows of the Step 2 query and their shared_blks_read per call, because that points at missing indexes faster than any plan visualiser. I keep pg_stat_statements and a sampled auto_explain on everywhere, review the list after every significant release, and treat each new index as a change with its own review, since it is cheap to add and easy to forget. Official references: pg_stat_statements, Using EXPLAIN, auto_explain and CREATE INDEX.

Related: How to Run Zero-Downtime Postgres Migrations: Step by Step (2026), How to Tune pgvector Performance: HNSW, ef_search and Quantization (2026) and How to Fix Postgres Too Many Clients.