How to Find and Fix Slow PostgreSQL Queries: EXPLAIN ANALYZE, pg_stat_statements and auto_explain Step by Step (2026)
Most teams chasing PostgreSQL slow queries start in the wrong place. They grab the query a developer complained about, run EXPLAIN without ANALYZE, add an index, and move on. Two weeks later the same server is still hot, because the query that actually burned the CPU was a 4 ms lookup called two million times a day, not the 3-second report. In 2026 this matters more than it used to. PostgreSQL 18 changed EXPLAIN ANALYZE defaults, PostgreSQL 19 is at release candidate with new plan-cache counters in pg_stat_statements, and managed services make it easy to throw hardware at a problem that is really a bad row estimate. This guide gives you a repeatable eight-step method: rank by total cost, capture the real plan, read it node by node, classify the failure, apply the narrowest fix, and prove it worked. Every step ships with runnable SQL and a reproducible test table, and the plans shown are real output from that table.
What this covers: setting up pg_stat_statements and auto_explain, the triage query, EXPLAIN (ANALYZE, BUFFERS) reading, plan node types, row-estimate errors and extended statistics, work_mem spills, composite, partial and covering indexes, visibility-map heap fetches, generic versus custom plans, failure modes and a prevention checklist.
Context and Background
PostgreSQL has a cost-based planner. For every statement it enumerates candidate plans, estimates how many rows each step will produce, converts that into an abstract cost using constants such as seq_page_cost (default 1.0) and random_page_cost (default 4.0), and executes the cheapest one. The planner is usually right. When it is wrong, the cause is almost always one of a small number of things: it estimated the wrong row count, it had no access path better than reading the whole table, it ran out of memory for a sort or hash, or it reused a plan that was built for different parameter values.
That narrow list is good news. It means slow-query work is a diagnosis problem, not a tuning lottery. The tools for the diagnosis ship with PostgreSQL itself. EXPLAIN shows the plan, and its ANALYZE option runs the statement and reports what actually happened. The pg_stat_statements extension aggregates execution statistics for every normalized query, so you can rank by total cost rather than by whoever shouted loudest. The auto_explain module logs the plan of any statement that crosses a duration threshold, which captures the plan that ran in production with production parameters.
The versions matter. As of the 12 November 2026 minor releases, PostgreSQL 18.7 is the current stable branch and PostgreSQL 19 is at RC 2, not yet generally available. PostgreSQL 17 added the SERIALIZE and MEMORY options to EXPLAIN. PostgreSQL 18 made EXPLAIN ANALYZE include buffer statistics automatically and added parallel-worker and wal_buffers_full columns to pg_stat_statements. The PostgreSQL 19 documentation adds generic_plan_calls and custom_plan_calls columns, which make plan-cache problems visible from the statistics view for the first time.
This post sits next to two related guides on the site. Lock waits look like slow queries in a dashboard but have a different fix, covered in our guide to fixing PostgreSQL and MySQL deadlocks with lock ordering and retries. Version-specific planner and partitioning changes are covered in the PostgreSQL 19 release features and upgrade guide. Here the focus is narrower and deeper: one slow statement at a time, from discovery to verified fix.
The Method for PostgreSQL Slow Queries: Rank, Capture, Read, Fix, Verify
To find and fix PostgreSQL slow queries, rank statements by total_exec_time in pg_stat_statements, capture real plans with auto_explain, run EXPLAIN (ANALYZE, BUFFERS) on the worst one, compare estimated and actual rows node by node, classify the cause (missing index, bad estimate, memory spill, plan cache), apply the narrowest fix, then reset and re-measure.

Figure 1: The triage loop. Rank by total time, capture the real plan, classify where the time goes, apply one narrow fix, and re-measure.
The diagram reads top to bottom. The first three boxes are discovery: pg_stat_statements tells you which normalized statement costs the most in aggregate, and the auto_explain log gives you a concrete instance of it with real parameter values. The fourth box is measurement. The diamond is the important part: almost every slow plan falls into one of four symptom classes, and each class has a different fix. The loop closes by resetting statistics and checking that the same query now costs less in aggregate, not just in one manual test.
Rank by total time, not by the slowest single run
The slowest query is rarely the most expensive one. Total cost to the server is calls multiplied by mean time. A dashboard query that takes 3 seconds and runs 200 times a day costs 600 seconds. An ORM lookup that takes 4 ms and runs 2 million times a day costs 8,000 seconds, more than thirteen times as much, and it never shows up in a “slow query” log with a one-second threshold. That is why the first sort key is total_exec_time, and why mean_exec_time alone is misleading.
Buffer counters refine the ranking. shared_blks_hit counts 8 kB pages found in shared buffers, and shared_blks_read counts pages that had to come from the operating system, which may be page cache or disk. A query with a large shared_blks_read is I/O-bound and will get worse as data grows. A query with a large temp_blks_written is spilling sorts or hashes to temporary files. These counters tell you which class of problem to expect before you ever open a plan.
Measure the plan that actually ran
EXPLAIN without ANALYZE shows estimates only. That is useful for checking whether an index is even considered, but it cannot show a misestimate, because it has nothing to compare against. EXPLAIN ANALYZE executes the statement and prints, for every plan node, the estimated rows next to the actual rows, the actual time, and the number of loops. With BUFFERS it also prints how many pages each node touched. The gap between estimated and actual rows is the single most diagnostic number in PostgreSQL query optimization.
There is a catch. A plan you produce by hand in psql may not be the plan the application got. Applications typically use prepared statements through their driver, and prepared statements can switch to a generic plan after five executions, as described later. Your session settings may also differ. This is why auto_explain matters: it records the plan from inside the application’s session, with its parameters and its settings.
One fix per symptom, applied narrowly
Each symptom class maps to a fix with a different blast radius. A missing index affects one table’s write cost. A statistics fix affects every query that touches those columns, usually for the better. A work_mem change made globally affects every session’s memory ceiling and can cause out-of-memory kills. A plan_cache_mode change made globally makes every prepared statement replan on every call. The method’s rule is simple: apply the fix at the narrowest scope that removes the symptom, whether that is one index, one statistics object, one role, one function, or one transaction.
Step-by-Step: Finding and Fixing a Slow Query
The steps below are a complete workflow. Steps 1 and 2 are one-time setup. Steps 3 to 8 are the loop you run for each statement.
- Enable pg_stat_statements and auto_explain in
shared_preload_librariesand restart. - Build a reproducible test table so you can try fixes without touching production.
- Rank statements by total execution time with a triage query.
- Capture the production plan from the auto_explain log, including parameters.
- Run
EXPLAIN (ANALYZE, BUFFERS)safely, inside a rolled-back transaction for writes. - Read the plan bottom-up and find the node where estimates and actuals diverge or time accumulates.
- Apply the narrowest fix: index, statistics, memory, or plan-cache setting.
- Reset statistics and verify that aggregate cost fell, then watch for regressions.
Step 1: Enable pg_stat_statements and auto_explain
Both modules hook into the executor, and pg_stat_statements needs shared memory, so it must be listed in shared_preload_libraries. That requires a server restart, which is why you should do it before you have an incident. Query identifiers must also be computed; this happens automatically when compute_query_id is auto (the default) or on.
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
compute_query_id = on
track_io_timing = on # fills *_blk_read_time columns and I/O Timings in plans
pg_stat_statements.max = 10000 # default 5000; least-executed entries are evicted
pg_stat_statements.track = top # 'all' also tracks statements inside functions
auto_explain.log_min_duration = '500ms' # default -1 means disabled
auto_explain.log_analyze = on # actual rows; costs overhead, see below
auto_explain.log_buffers = on
auto_explain.log_timing = off # keep row counts, skip per-node clock reads
auto_explain.log_format = 'json' # easier to parse; text is easier to read
auto_explain.sample_rate = 0.1 # explain 10% of statements per session
auto_explain.log_parameter_max_length = 512
-- After the restart, in each database you want to query:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Three of these settings deserve a closer look. track_io_timing makes PostgreSQL read the clock around every block read and write. On most modern Linux systems that is cheap, but the documentation recommends checking your platform with the pg_test_timing utility before enabling it. pg_stat_statements.max defaults to 5,000 entries. When the table is full, the least-executed entries are evicted and the eviction count appears in pg_stat_statements_info.dealloc. If that counter climbs fast, raise max, because frequent eviction makes the totals unreliable.
auto_explain.log_analyze is the expensive one. The documentation is explicit that when it is on, per-node timing instrumentation runs for every statement, whether or not the statement ends up logged, and that this “can have an extremely negative impact on performance.” Turning off log_timing keeps actual row counts, which is what you need to see misestimates, while removing most of the clock overhead. Sampling with sample_rate reduces the instrumented fraction further. A common production pattern is log_analyze = on, log_timing = off, sample_rate between 0.01 and 0.1, and a log_min_duration set just above your latency objective. Treat those values as starting points to test against your own workload, not as measured recommendations.
Step 2: Build a reproducible test table
You need a place to try fixes where you can drop indexes and change settings freely. The script below builds a 2-million-row orders table with four deliberate traps: no index on the lookup column, two correlated columns, a rare status value, and enough rows to make sorts spill under the default 4 MB work_mem.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id int NOT NULL,
region_id int NOT NULL,
country_id int NOT NULL, -- each country belongs to exactly one region
status text NOT NULL, -- 'shipped' 90%, 'cancelled' ~10%, 'pending' 0.1%
amount numeric(10,2) NOT NULL,
created_at timestamptz NOT NULL,
note text
);
INSERT INTO orders (customer_id, region_id, country_id, status, amount, created_at, note)
SELECT (random() * 99999)::int + 1,
r,
r * 10 + (random() * 9)::int,
CASE WHEN g % 1000 = 0 THEN 'pending'
WHEN g % 10 = 0 THEN 'cancelled'
ELSE 'shipped' END,
round((random() * 500)::numeric, 2),
timestamptz '2026-01-01' + (g || ' seconds')::interval * 6,
repeat('x', 60)
FROM generate_series(1, 2000000) AS g,
LATERAL (SELECT g % 50 AS r) AS s;
VACUUM ANALYZE orders;
The country_id column is functionally dependent on region_id: country 73 exists only in region 7. The planner does not know that by default, which is exactly the trap we want. On the test machine the heap came out at 269 MB.
A note on the numbers in the rest of this post. Every plan below is real output from this script, run on PostgreSQL 16.15 in a small 2-vCPU sandbox with the default 128 MB shared_buffers. Timings are single runs and are illustrative, not a benchmark. Your absolute numbers will differ; the ratios and the plan shapes are the point. PostgreSQL 18 and later print the same node types, with buffers included automatically under ANALYZE and a few extra fields.
Step 3: Rank statements by total execution time
This is the triage query. Run it on the primary, and on each replica separately, because statistics are per server.
SELECT left(query, 80) AS query,
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 / nullif(calls, 0) AS rows_per_call,
shared_blks_hit,
shared_blks_read,
temp_blks_written,
round(100.0 * shared_blks_hit
/ nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;
After resetting statistics and running a handful of queries against the test table without any secondary indexes, the top of the list looked like this (abridged):
| query (normalized) | calls | total_ms | mean_ms | shared_blks_hit | shared_blks_read | temp_blks_written | hit_pct |
|---|---|---|---|---|---|---|---|
SELECT customer_id, amount FROM orders WHERE status=$1 ORDER ... |
1 | 960.7 | 960.65 | 14,375 | 21,958 | 7,081 | 39.6 |
SELECT id, created_at, amount FROM orders WHERE customer_id ... |
5 | 447.0 | 89.41 | 70,375 | 111,230 | 0 | 38.8 |
SELECT count(*) FROM orders WHERE region_id = $1 AND country ... |
1 | 100.7 | 100.69 | 13,673 | 22,534 | 0 | 37.8 |
SELECT id FROM orders WHERE id = $1 |
2 | 0.1 | 0.05 | 9 | 2 | 0 | 81.8 |
Each column carries a clue. The first row writes temporary blocks, so it is a sort or hash spill. The second row reads roughly 36,000 pages per call to return at most 20 rows; that ratio screams “full table scan for a point lookup.” The third row also reads the whole table to count a few thousand rows. The fourth is a primary-key lookup touching about five pages per call, which is what healthy looks like. Notice that literal values became $1: pg_stat_statements normalizes constants so that the five customer lookups with different IDs collapse into one entry. Since PostgreSQL 18 it also squashes IN lists that differ only in length into one entry, which used to be a major source of noise.
Two more practices make this query trustworthy. First, snapshot the view on a schedule, or call pg_stat_statements_reset() at a known time, because the counters are cumulative since the last reset and a month-old total hides this week’s regression. PostgreSQL 17 added stats_since and minmax_stats_since columns so you can see when each entry started counting. Second, look at stddev_exec_time next to the mean. A mean of 5 ms with a standard deviation of 400 ms is not a 5 ms query; it is a fast query with an occasional catastrophe, often a plan flip or lock wait.
Step 4: Capture the production plan with auto_explain
The triage query tells you which statement to examine but not which parameter values made it slow. The auto_explain log fills that gap. With the settings from Step 1, any sampled statement slower than the threshold writes its plan to the server log. A session-level version is useful for testing, though LOAD and the auto_explain.* settings require superuser privileges:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '50ms';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;
SET auto_explain.log_timing = off;
SELECT id, created_at, amount
FROM orders
WHERE customer_id = 99
ORDER BY created_at DESC
LIMIT 20;
The resulting log entry from the test server, abridged:
LOG: duration: 105.669 ms plan:
Query Text: SELECT id, created_at, amount FROM orders WHERE customer_id = 99 ORDER BY created_at DESC LIMIT 20
Limit (cost=47623.81..47625.68 rows=16 width=22) (actual rows=17 loops=1)
Buffers: shared hit=14459 read=21862
-> Gather Merge (cost=47623.81..47625.68 rows=16 width=22) (actual rows=17 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=46623.79..46623.81 rows=8 width=22) (actual rows=6 loops=3)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on orders (cost=0.00..46623.67 rows=8 width=22) (actual rows=6 loops=3)
Filter: (customer_id = 99)
Rows Removed by Filter: 666661
Buffers: shared hit=14345 read=21862
Because log_timing is off, the per-node lines show actual rows without times. That is enough to see the problem. The total duration is still recorded at the top.

Figure 2: Where the two modules sit. pg_stat_statements accumulates counters per query ID; auto_explain decides at executor start whether to instrument, and logs the plan at executor end if the statement was slow.
The sequence diagram explains two behaviours that confuse people. First, auto_explain must decide whether to instrument a statement before it knows how long it will take. That is why log_analyze costs something on every sampled statement, including the fast ones. Second, pg_stat_statements attaches its counters to the query identifier computed at parse time, so every execution of the same normalized statement lands in the same row, regardless of which plan was used. In PostgreSQL 19 the generic_plan_calls and custom_plan_calls columns finally tell you which kind of plan those executions used.
Step 5: Run EXPLAIN (ANALYZE, BUFFERS) safely
Now reproduce the plan by hand, with the parameters from the log. The full form worth memorising is:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE)
SELECT id, created_at, amount, status
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;
Each option adds one piece of evidence. ANALYZE executes the statement and adds actual rows, actual time and loops. BUFFERS adds page counts per node; since PostgreSQL 18 it is included automatically when ANALYZE is used, but naming it keeps scripts portable to older servers. SETTINGS prints any planner-relevant setting that differs from the built-in default, which catches the classic “it is fast on my machine” case where someone’s session has a different work_mem or random_page_cost. VERBOSE prints output columns and the query identifier, which lets you match the plan to its pg_stat_statements row.
Two newer options are worth knowing. SERIALIZE, added in PostgreSQL 17, measures the cost of converting result rows into the wire format, including de-TOASTing large values. Without it, EXPLAIN ANALYZE discards rows before serialization and can understate the cost of queries that return wide jsonb or text columns. MEMORY, also from PostgreSQL 17, reports how much memory the planner used. WAL reports write-ahead log records, full-page images and bytes for writes. GENERIC_PLAN shows the generic plan for a query containing $1 placeholders, but it cannot be combined with ANALYZE.
ANALYZE really runs the statement. For SELECT the rows are discarded, but for INSERT, UPDATE, DELETE, MERGE or CREATE TABLE AS the side effects happen. The documented pattern is to wrap the statement in a transaction and roll it back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders SET amount = amount * 1.1 WHERE customer_id = 4242;
ROLLBACK;
Rolling back does not undo everything. The rows were locked for the duration, dead tuples were created and must be vacuumed, and sequences advanced. On a busy primary, run these experiments on a replica or a restored copy when the statement is a write. Also remember that EXPLAIN ANALYZE adds instrumentation overhead, so its timings can be higher than the real query, especially for plans with millions of cheap node executions. EXPLAIN (ANALYZE, TIMING OFF) removes per-node clock reads while keeping row counts.
Step 6: Read the plan bottom-up
Here is the hand-run version of the customer lookup before any fix:
Limit (cost=45899.91..45902.01 rows=18 width=30) (actual time=76.524..79.097 rows=17 loops=1)
Buffers: shared hit=14242 read=20355
-> Gather Merge (cost=45899.91..45902.01 rows=18 width=30) (actual time=76.522..79.092 rows=17 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=44899.88..44899.90 rows=9 width=30) (actual time=70.568..70.570 rows=6 loops=3)
Sort Key: created_at DESC
Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on orders (cost=0.00..44899.74 rows=9 width=30) (actual time=7.477..70.498 rows=6 loops=3)
Filter: (customer_id = 4242)
Rows Removed by Filter: 666661
Buffers: shared hit=14129 read=20354
Planning Time: 0.260 ms
Execution Time: 79.142 ms
Read plans from the most indented node outward, because that is where rows originate. Each node shows cost=startup..total in planner units, the estimated rows per loop, and in parentheses the actual time=first_row..last_row in milliseconds per loop, the actual rows per loop, and loops. The per-loop detail is the most common reading mistake. Here the parallel scan ran in three processes (two workers plus the leader), so rows=6 loops=3 means about 18 rows in total, and Rows Removed by Filter: 666661 is also a per-loop average, about 2 million rows discarded in total.
The diagnosis takes four observations. The estimate was right: 9 rows per loop estimated, 6 actual. So statistics are not the problem. The scan touched about 34,600 pages, which is the whole 269 MB table, to return 17 rows. Almost all of the 79 ms was in the scan node, not the sort, because the sort finished 0.07 ms after the scan. And there is no index on customer_id. This is the “too many buffers for rows returned” class from Figure 1. A useful rule of thumb is to divide buffers by rows returned; a point lookup should touch a handful of pages per row, not thousands.
You will meet a small set of node types in almost every plan. A Seq Scan reads every page of the table. An Index Scan walks a B-tree to find matching entries and fetches each heap row, which is efficient for few rows and expensive for many because each fetch is a random read. An Index Only Scan answers from the index alone when every needed column is in it and the heap page is marked all-visible. A Bitmap Index Scan plus Bitmap Heap Scan collects matching row locations first, sorts them in physical order, and then reads each heap page once, which suits medium selectivity. For joins, a Nested Loop runs its inner side once per outer row, which is ideal when the outer side is small and the inner side is indexed. A Hash Join builds a hash table from one side and probes it with the other, which suits large unsorted inputs. A Merge Join walks two inputs already sorted on the join key.
Two more fields matter. Planning Time that rivals execution time points at queries with many joins or partitions; the fix there is a prepared statement or fewer partitions, not an index. And in PostgreSQL 18 and later, index scan nodes report the number of index searches, which shows when the new B-tree skip scan is probing a multi-column index many times.
Step 7: Apply the narrowest fix

Figure 3: Symptom-to-fix decision tree. Walk it from the slowest node of the plan; the first matching symptom is usually the cause.
The decision tree is ordered by how often each cause appears and how cheap the check is. A sequential scan that removes most of the rows it reads is the most common and the easiest to spot. A row-estimate error of ten times or more is next, and it is the cause most often misdiagnosed as a missing index. Spills, heap fetches and nested-loop blow-ups follow. The sub-sections below apply each branch to the test table.
Fix 7a: A composite covering index for the lookup
The customer query filters on customer_id and sorts by created_at DESC. One composite B-tree index serves both, because entries are stored sorted by the first column and then the second, so the planner can read the first 20 matching entries in order and stop. Adding INCLUDE puts payload columns into the leaf pages without making them part of the key.
CREATE INDEX orders_cust_created_idx
ON orders (customer_id, created_at DESC)
INCLUDE (amount, status);
The result on the same query:
Limit (cost=0.43..84.59 rows=20 width=30) (actual time=0.016..0.051 rows=17 loops=1)
Buffers: shared hit=9 read=11
-> Index Scan using orders_cust_created_idx on orders (cost=0.43..88.79 rows=21 width=30) (actual time=0.015..0.049 rows=17 loops=1)
Index Cond: (customer_id = 4242)
Execution Time: 0.072 ms
Execution time fell from 79 ms to 0.07 ms and buffers from about 34,600 to 20. The sort node disappeared, because the index already delivers rows in the requested order. This plan is still an Index Scan, not an Index Only Scan, because the query selects id, which is not in the index. Drop id from the select list and the plan becomes:
Index Only Scan using orders_cust_created_idx on orders (cost=0.43..4.79 rows=21 width=22) (actual time=0.007..0.010 rows=17 loops=1)
Index Cond: (customer_id = 4242)
Heap Fetches: 0
Buffers: shared hit=5
Execution Time: 0.018 ms
Five buffers and zero heap fetches. The trade-off is size: this index is 96 MB, a third of the heap, because it carries four columns for every row. Every insert and every update that changes an indexed or included column also pays to maintain it. Our deep dive on B-tree index internals, page splits and fill factor covers why wide indexes cost more than their size suggests.
Column order in a composite index follows a simple rule. Put equality-filtered columns first, then the range or sort column. An index on (created_at, customer_id) could not serve this query efficiently because the matching customer’s entries would be scattered across the whole date range. PostgreSQL 18’s skip scan softens that rule for some queries without a condition on the leading column, but it works best when the leading column has few distinct values, so do not rely on it to rescue a wrong column order.
Fix 7b: A partial index for a rare value
The query for pending orders filters on a value that appears in 0.1% of rows. Before any index, it read the whole table and took 127 ms to return 2,000 rows. A full index on status would mostly store entries for shipped, which no query wants to look up by index. A partial index stores only the rows you care about:
CREATE INDEX orders_pending_idx
ON orders (created_at)
WHERE status = 'pending';
Index Scan using orders_pending_idx on orders (cost=0.28..137.61 rows=2933 width=18) (actual time=0.043..5.811 rows=2000 loops=1)
Index Cond: (created_at > (now() - '400 days'::interval))
Buffers: shared hit=811 read=1196 written=841
Execution Time: 5.891 ms
The index is 64 kB instead of tens of megabytes, and the query dropped from 127 ms to 5.9 ms. The planner only uses a partial index when it can prove the query’s WHERE clause implies the index predicate, so the query must say status = 'pending' with a literal or a parameter value the planner can see. That last condition matters for prepared statements, as Step 7d shows.
Fix 7c: Correct a row-estimate error with extended statistics
The region-and-country count is a different problem. There was no fast access path, but more importantly the estimate was wrong:
Parallel Seq Scan on orders (cost=0.00..46983.09 rows=32 width=0) (actual time=0.074..84.472 rows=1505 loops=3)
Filter: ((region_id = 7) AND (country_id = 73))
Rows Removed by Filter: 665162
The planner expected 32 rows per loop and got 1,505, about 47 times too few. In this query that error is harmless, because a full scan was the only option. Put the same predicate on the outer side of a join and the planner will choose a nested loop sized for about a hundred rows that then runs four and a half thousand times. That is how most “the query was fine yesterday” regressions happen.
The cause is the independence assumption. Without extra information, the planner multiplies the selectivity of each condition: about 1/50 for region_id = 7 times roughly 1/400 to 1/500 for country_id = 73. But every row with country 73 is in region 7, so the true selectivity is just that of country_id. CREATE STATISTICS teaches the planner about such correlations. It supports three kinds: ndistinct for group counts, dependencies for functional dependencies, and mcv for multi-column most-common-value lists.
CREATE STATISTICS orders_region_country_dep (dependencies)
ON region_id, country_id FROM orders;
ANALYZE orders;
SELECT dependencies FROM pg_stats_ext
WHERE statistics_name = 'orders_region_country_dep';
-- {"4 => 3": 1.000000} (attribute 4 = country_id determines attribute 3 = region_id)
The new estimate was rows=1586 per loop against an actual 1,505. The error dropped from 47 times to about 5%.
The test produced one result worth highlighting, because it contradicts the common advice to “just create all statistics kinds.” Creating the object as (dependencies, mcv), or as (mcv) alone, left the estimate at about 30 to 33 rows per loop. The reason is visible in pg_stats_ext: with the default statistics target of 100, the multi-column MCV list held 100 of the 500 real combinations, and (7, 73) was not among them. Raising the target with ALTER STATISTICS orders_rc_mcv SET STATISTICS 1000 stored all 500 combinations and the estimate rose to 1,970 per loop. The documentation’s own MCV example tells readers to drop the dependency statistics first so the planner uses the MCV list, which hints that the two kinds interact. The practical lesson is to check the estimate after creating statistics rather than assuming. Use dependencies for clean hierarchies like country to region, and mcv with a sufficient target for skewed combinations. Note that extended statistics are not currently used for join selectivity, and that pg_upgrade in PostgreSQL 18 preserves ordinary column statistics but not extended statistics, so run ANALYZE after an upgrade.
Sometimes the fix is simpler. If estimates are wrong on a single column, check last_analyze and last_autoanalyze in pg_stat_user_tables. A table that grew tenfold since its last analyze will have stale row counts and histograms. For a skewed column, ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000 raises the sample size and histogram resolution for that column only, while the default target remains 100.
Fix 7d: Handle work_mem spills honestly
The sort query showed Sort Method: external merge Disk: 12736kB in each of three processes, and temp written=4666 blocks. The default work_mem of 4 MB could not hold 600,000 rows per process, so the sort wrote sorted runs to temporary files and merged them. The standard advice is to raise work_mem. Here is what happened when the test session did exactly that:
| configuration | plan shape | sort method | wall time (illustrative, single runs) |
|---|---|---|---|
default work_mem 4 MB, parallel |
Gather Merge over 3 sorts | external merge, about 12 MB per process | about 0.92 to 0.98 s |
work_mem = 128MB |
single Sort, no parallel workers | quicksort, 119,465 kB | about 1.68 to 1.74 s |
4 MB, max_parallel_workers_per_gather = 0 |
single Sort | external merge | about 1.32 s |
Raising work_mem removed the spill and made the query slower. With 128 MB available, the planner’s cost model judged one in-memory sort cheaper than three parallel sorts plus a merge, and on this two-vCPU sandbox that judgment was wrong. The serial spilled sort was also faster than the serial in-memory one, which is consistent with external sorting being cache-friendly for runs that fit in CPU cache. None of this generalizes as a benchmark; it is one machine. The general lesson is that a spill is a symptom, not a verdict. Measure before and after.
The real fix for this query is to not sort 1.8 million rows. Ask whether the application needs all of them. A LIMIT with an index on (status, amount DESC, customer_id) turns the sort into an ordered index read. If it is a batch export, it may belong on a replica or in an analytics store.
When a spill is the bottleneck and the result set is legitimate, raise work_mem at the narrowest scope. Each sort or hash node in each process may use up to work_mem, and hash nodes may use work_mem times hash_mem_multiplier (default 2.0). A query with four hash joins in three parallel processes can claim up to 24 times work_mem. Multiply by concurrent sessions and a global 256 MB setting can exhaust memory under load. Prefer per-role or per-transaction settings:
ALTER ROLE reporting_user SET work_mem = '128MB';
BEGIN;
SET LOCAL work_mem = '256MB';
-- the one heavy query
COMMIT;
The temp_blks_written column in pg_stat_statements and the log_temp_files setting tell you which statements spill in production, so you can target them instead of guessing.
Fix 7e: Plan-cache flips between generic and custom plans
The final class is the hardest to see in a manual psql session, because it only appears with prepared statements. Most drivers and connection poolers in transaction mode use them, and PL/pgSQL prepares every query in a function automatically.

Figure 4: The plan cache under the default plan_cache_mode of auto. Five custom plans set a baseline; if the generic plan’s estimated cost is close to it, the generic plan is reused for every later call, including calls with unusual parameter values.
The PREPARE documentation describes the rule: the first five executions use custom plans built for the actual parameter values, and their average estimated cost is recorded. Then a generic plan is built, with $1 placeholders instead of values, and if its estimated cost is not much higher than that average, it is used from then on. The generic plan cannot see the parameter, so it estimates selectivity from the column’s average, roughly one over the number of distinct values.
On the test table, with an index on status:
CREATE INDEX orders_status_idx ON orders (status);
PREPARE by_status(text) AS
SELECT count(*), sum(amount) FROM orders WHERE status = $1;
EXPLAIN (ANALYZE) EXECUTE by_status('pending');
-- custom plan: Index Scan using orders_pending_idx, rows=2113 estimated, 2000 actual
-- Execution Time: 3.589 ms
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE) EXECUTE by_status('pending');
-- generic plan: Parallel Seq Scan, Filter: (status = $1), rows=277778 estimated per loop, 667 actual
-- Execution Time: 92.302 ms
The custom plan for 'pending' used the partial index and took 3.6 ms. The generic plan estimated 277,778 rows per loop, about one-third of the table across the processes, because the column has three distinct values. It chose a parallel sequential scan and took 92 ms, roughly 25 times slower. In this demo the generic plan was forced, but under auto the same flip happens on its own when the first five calls happen to use common values. That produces the signature pattern in pg_stat_statements: a low mean, a huge max_exec_time, and a large stddev_exec_time. EXPLAIN EXECUTE shows which plan is in use: a generic plan contains $n symbols, and a custom plan contains the literal values.
Fixes, from narrowest to broadest: set plan_cache_mode = force_custom_plan for the one role or function that runs the skewed query; issue rare values through a separate statement with a literal; or partition the hot value away. Setting force_custom_plan server-wide costs planning time on every call of every prepared statement, which can matter for very short queries.
Step 8: Reset statistics and verify
A fix is not finished until the aggregate numbers move. Save the relevant pg_stat_statements row, deploy the fix, reset the entry, and compare after a representative period:
-- Reset only the entry you fixed (PostgreSQL 12+ accepts userid, dbid, queryid)
SELECT pg_stat_statements_reset(0, 0, <queryid>);
-- After a day of traffic, compare
SELECT calls, mean_exec_time, max_exec_time, stddev_exec_time,
shared_blks_read / nullif(calls, 0) AS reads_per_call,
temp_blks_written
FROM pg_stat_statements
WHERE queryid = <queryid>;
Check three things. Mean and max time fell. Buffers per call fell, which is the most stable metric because it does not depend on cache warmth or load. And nothing else got worse: look for write-heavy statements on the same table whose mean rose after you added an index. Keep the before and after numbers in the change record. They are the best defence when someone later proposes dropping the index.
Trade-offs, Gotchas, and What Goes Wrong
Heap fetches silently undo index-only scans. An index-only scan only skips the heap when the page’s bit in the visibility map says every row on it is visible to all transactions. On the test table, updating 5% of rows (100,000 rows spread across nearly every page) turned the covering-index range query from an index-only scan with zero heap fetches into one with 20,911 heap fetches and 97 ms of execution. After VACUUM, it returned to zero heap fetches and 2.8 ms. Part of the first run’s cost was also setting hint bits on the updated pages. Autovacuum had not run, because by default it triggers after 50 plus 20% of the table’s rows are dead, which is about 400,000 rows here. For large tables with steady updates, lower autovacuum_vacuum_scale_factor per table so the visibility map stays current. PostgreSQL 18 also added autovacuum_vacuum_max_threshold (default 100 million) to cap the trigger on very large tables.
Bloat looks like a slow query. Dead tuples that vacuum cannot remove, because a long-running transaction or an abandoned replication slot holds back the cleanup horizon, make every scan read more pages. If shared_blks_read per call creeps up over weeks with no plan change, check n_dead_tup and the oldest backend_xmin in pg_stat_activity. Rebuilding options are compared in our guide to PostgreSQL 19 REPACK versus pg_repack versus VACUUM FULL, and the MVCC mechanics behind dead tuples are in our explainer on MVCC and transaction isolation levels.
Waiting is not executing. total_exec_time includes time spent waiting for locks, I/O and client network. A statement that is slow only because it waits on a row lock will look slow in every tool in this guide, and no index will fix it. Check wait_event_type in pg_stat_activity during the slowdown before you start tuning.
enable_* settings are a diagnostic, not a fix. SET enable_seqscan = off in a test session is a fine way to ask “what would the index plan cost?” Shipping it to production hides the real cause and breaks the next query that legitimately needs a sequential scan. The documentation describes these settings as a crude method intended for temporary use.
Over-indexing has a write cost. Every index slows inserts, makes updates of indexed columns non-HOT, and adds WAL. Check idx_scan in pg_stat_user_indexes periodically and drop indexes that are never used, after confirming they are not unused only on the primary while replicas rely on them.
Cold cache lies in both directions. The first run after a restart reads from disk; the second reads from cache. Compare buffers, not milliseconds, when testing fixes, and run each test more than once.
Practical Recommendations
Treat PostgreSQL slow queries as an ongoing process, not an incident response. Enable the two modules now, while there is time for a restart. Make the triage query part of a weekly review, and snapshot pg_stat_statements into a history table so that you can diff weeks. Put auto_explain on with sampling and log_timing off, ship the log to whatever system you already use for logs, and alert on new query IDs appearing in the top ten.
When you fix something, choose by symptom and scope. Composite, partial and covering indexes solve access-path problems for one table. Extended statistics solve estimate problems for every query on those columns, at almost no write cost. Memory and plan-cache settings belong on roles, functions or transactions, not on the server. And verify with buffers per call, which is the most honest metric you have.
Prevention checklist:
pg_stat_statementsandauto_explaininshared_preload_libraries, withtrack_io_timingon after checkingpg_test_timing.- Weekly triage ranked by
total_exec_time, reviewingstddev_exec_timeandtemp_blks_written. pg_stat_statements_info.deallocmonitored; raisepg_stat_statements.maxif it climbs.- New migrations reviewed with
EXPLAIN (ANALYZE, BUFFERS)against production-sized data. CREATE STATISTICSfor known correlated column pairs, with estimates checked after creation.- Per-table autovacuum thresholds for large, frequently updated tables.
ANALYZEafter bulk loads and after major-version upgrades.- Unused indexes reviewed quarterly via
pg_stat_user_indexes. - Connection-pool and prepared-statement behaviour known for each application, especially for skewed columns. Our comparison of PgBouncer, PgCat and RDS Proxy transaction pooling covers how poolers handle prepared statements.
Frequently Asked Questions
How do I find slow queries in PostgreSQL?
Install the pg_stat_statements extension by adding it to shared_preload_libraries, restarting, and running CREATE EXTENSION pg_stat_statements. Then query the view ordered by total_exec_time descending to see which normalized statements cost the server the most in aggregate. Add auto_explain with a log_min_duration threshold to capture the actual plans and parameter values of slow executions in the server log. Setting log_min_duration_statement also logs slow statement text, but without plans, so it is a weaker tool on its own.
What is the difference between EXPLAIN and EXPLAIN ANALYZE in PostgreSQL?
EXPLAIN shows the plan the planner would choose, with estimated costs and row counts, without running the statement. EXPLAIN ANALYZE runs the statement and adds actual times, actual row counts and loop counts for every node, so you can compare estimates with reality. Because it executes the statement, wrap data-modifying statements in BEGIN and ROLLBACK. Since PostgreSQL 18, EXPLAIN ANALYZE also includes buffer usage automatically; on older versions add BUFFERS explicitly to see page reads per node.
Does pg_stat_statements slow down PostgreSQL?
It adds a small overhead per statement for hashing and updating shared counters, which most production systems run with permanently. Two options cost more. pg_stat_statements.track_planning records planning time and is off by default because the documentation warns of a noticeable penalty under high concurrency. track_io_timing, which fills the I/O time columns, depends on how cheap your platform’s clock is; check with pg_test_timing. Neither is required for the core triage query in this guide.
Is auto_explain safe to use in production?
Yes, with care. Logging plans without log_analyze is cheap, because it only runs EXPLAIN for statements that already exceeded the threshold. Turning on log_analyze instruments every sampled statement, slow or not, which the documentation says can severely affect performance. Reduce that by setting auto_explain.log_timing = off to keep row counts without per-node clock reads, and by setting auto_explain.sample_rate well below 1. Watch log volume too, because JSON plans for complex queries can be large.
Why is PostgreSQL not using my index?
Usually for one of four reasons. The planner estimates that the condition matches a large fraction of rows, so a sequential scan is genuinely cheaper. The query wraps the column in a function or cast, such as lower(email), which a plain index cannot serve without an expression index. The composite index’s leading column is not constrained by the query. Or statistics are stale or the estimate is wrong. Use EXPLAIN (ANALYZE, BUFFERS) to compare estimated and actual rows, and test with SET enable_seqscan = off in a session only to see the alternative cost.
When should I increase work_mem?
When EXPLAIN ANALYZE shows Sort Method: external merge or a hash node with multiple batches, temp_blks_written in pg_stat_statements is large for that query, and the time is actually spent there. First try to reduce the rows being sorted or hashed. If the volume is legitimate, raise work_mem for the specific role or transaction with SET LOCAL. Remember that each sort and hash node in each parallel process can use the limit, and hash nodes can use twice it by default, so global increases multiply.
Further Reading
- How to fix database deadlocks in PostgreSQL and MySQL with lock ordering and retries — for slow queries that are really lock waits.
- PostgreSQL 19 release features and upgrade guide — planner, partitioning and replication changes to plan for.
- PostgreSQL high availability with Patroni, streaming replication, failover and PITR — where to run heavy diagnostic queries without touching the primary.
- Write-ahead logging explained — what the
WALoption of EXPLAIN is measuring.
References
- PostgreSQL Documentation, EXPLAIN (version 18).
- PostgreSQL Documentation, pg_stat_statements (version 18) and the version 19 page listing
generic_plan_callsandcustom_plan_calls. - PostgreSQL Documentation, auto_explain.
- PostgreSQL Documentation, PREPARE, notes on generic and custom plans, and Query Planning settings for
plan_cache_mode. - PostgreSQL Documentation, CREATE STATISTICS and Multivariate Statistics Examples.
- PostgreSQL Documentation, Index-Only Scans and Covering Indexes.
- PostgreSQL Documentation, Resource Consumption for
work_memandhash_mem_multiplier, and Automatic Vacuuming. - PostgreSQL Release Notes, PostgreSQL 18.0.
By Riju — about
