Postgres query tuning in production

TL;DR — A production query plan contains around twenty numbers, and four of them reliably predict whether a query will hurt you: buffer counts, estimated rows against actual rows, Rows Removed by Filter, and loops. Several of the most-quoted numbers predict nothing. cost= is in arbitrary planner units, a single execution time measures your cache state as much as your query, and a Seq Scan is often the correct plan. The hardest part of production tuning is not reading the plan. It is capturing the plan from the execution that was actually slow.

---

Why the staging plan is a different plan

Most database performance work fails at the first step, which is measuring the wrong thing. A query gets reported as slow, someone runs EXPLAIN ANALYZE against a staging copy, the plan comes back in 40 ms, and the ticket is closed as not reproducible. Two days later the same query takes down checkout.

A query plan is not a property of the SQL. It is the outcome of two things that only exist at runtime: the statistics the planner used to choose a strategy, and the state of the machine when that strategy executed. Staging has a different data distribution, a warm and largely empty cache, no concurrent sessions competing for parallel workers, and a vacuum history that bears no relationship to production. The plan you read there is a plan for a different query.

This article covers how we read a plan captured from a live system, and which of its numbers we act on.

Assumed context: PostgreSQL 14 or later, a table large enough that plan choice matters (roughly ten million rows and up), and access to pg_stat_statements and auto_explain. Everything here applies to managed Postgres as well as self-hosted, though some providers restrict shared_preload_libraries and expose these modules through their own configuration panel instead.

Capture the plan that actually hurt

Two safety notes before anything else.

EXPLAIN ANALYZE executes the statement. On a SELECT that costs you the query's real resources against production. On an INSERT, UPDATE, DELETE or MERGE it writes, and it commits. Wrap those:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL) UPDATE orders SET status = 'cancelled' WHERE ...;
ROLLBACK;

Set a session statement_timeout first. The query you are investigating is slow by definition, which makes it exactly the query you do not want running unbounded on a production connection while you read the output.

Finding which query to investigate is a separate job from reading the plan, and pg_stat_statements does it. Sort by total_exec_time, not by mean_exec_time. The statement costing you the most is usually a 12 ms query running four million times an hour rather than the nine-second report that runs twice a day. Then look at stddev_exec_time. A high standard deviation against a low mean is the signature of a query that is fine on a warm cache and terrible on a cold one, or of a parameterised query whose plan depends on which value happens to get passed.

Getting the plan from the slow execution rather than the fast one you can reproduce by hand is what auto_explain is for:

# postgresql.conf
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 0.05

Per-node timing instrumentation is not free, so sample rather than capturing everything, and measure the overhead on your own hardware before rolling it out. auto_explain.sample_rate accepts a fraction of statements per session; 0.05 is a conservative starting point, not a recommendation. On systems where timing is expensive, setting auto_explain.log_timing = off still gives you row counts and buffer counts, which is most of what you need.

Since PostgreSQL 18, BUFFERS is enabled by default with ANALYZE. Ask for it explicitly anyway, so the same command behaves identically on every version you still support.

A plan worth reading

The plan below is a constructed example rather than a captured log, but the shape is one we see regularly. An operations dashboard lists the fifty most recent pending orders from the last week, against a table holding roughly 40 million rows.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.placed_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.placed_at >= now() - interval '7 days'
ORDER BY o.placed_at DESC
LIMIT 50;

Limit  (cost=0.85..824.11 rows=50 width=44) (actual time=2841.115..2841.207 rows=50 loops=1)
  Buffers: shared hit=118422 read=96110
  ->  Nested Loop  (cost=0.85..217184.02 rows=13176 width=44) (actual time=2841.113..2841.198 rows=50 loops=1)
        Buffers: shared hit=118422 read=96110
        ->  Index Scan Backward using orders_placed_at_idx on orders o
              (cost=0.43..198334.77 rows=13176 width=24) (actual time=2841.088..2841.121 rows=50 loops=1)
              Index Cond: (placed_at >= (now() - '7 days'::interval))
              Filter: (status = 'pending'::text)
              Rows Removed by Filter: 2411907
              Buffers: shared hit=118322 read=96060
        ->  Index Scan using customers_pkey on customers c
              (cost=0.42..1.43 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
              Buffers: shared hit=100 read=50
Planning Time: 0.214 ms
Execution Time: 2841.266 ms

Three numbers carry the diagnosis:

The planner's reasoning is visible in the costs. It expected 13,176 matching rows across the week, and it had an index that already returns rows in placed_at DESC order, so it assumed it could walk backwards, collect fifty pending rows quickly and stop. That is why the Limit is priced at 824 while the full scan underneath it is priced at 217,184.

The assumption was that pending orders are distributed evenly across the seven-day window. In production they are not. The queue had been drained an hour earlier, so the only rows still pending were old ones, sitting at the far end of a backward scan. Postgres read 2.4 million index entries and their heap tuples before it found fifty rows it could keep.

The fix is not a query rewrite. It is telling the index about the predicate that actually does the filtering:

CREATE INDEX CONCURRENTLY orders_pending_recent_idx
  ON orders (placed_at DESC)
  WHERE status = 'pending';

Same query, same plan shape, fifty index entries read instead of 2.4 million.

The four numbers that predict slowness

Buffers, not milliseconds

Execution time measures one run against one cache state. Run the same query twice and the second is faster. Run it at 3 a.m. and it is faster still. Buffer counts are close to stable across runs, which makes them the only figure in the plan you can safely compare between environments, before and after a change, or between two candidate indexes.

The useful derived figure is buffers per returned row. A plan touching 214,000 buffers to return 50 rows is structurally wrong regardless of what the clock said, because the moment that data falls out of cache the query becomes 1.7 GB of I/O.

shared hit came from shared buffers and shared read did not, though a read may still have been served by the operating system page cache rather than by disk. Treat read as an upper bound on physical I/O rather than a measurement of it.

A query that is fast because the data happens to be in memory is not a fast query. It is a slow query with good luck, and production eventually runs out of luck.

Estimated rows against actual rows

This is the most diagnostic comparison in the plan, because estimates are the input to every decision the planner made. Get a row count wrong by two orders of magnitude and you get the wrong join strategy, which is how a plan goes from linear to quadratic.

Read the plan bottom-up and find the first node where estimate and actual diverge by more than roughly 10x. Errors propagate upward, so the topmost wrong node is usually a symptom and the lowest one is the cause.

Two cautions when reading. Actual rows on the inner side of a nested loop is the average per loop, so the total is rows × loops. And a node under a LIMIT can show a low actual simply because execution stopped early, which is not a misestimate at all.

Misestimates usually trace back to one of three causes:

• Stale statistics. Fixed by ANALYZE, and by checking whether autovacuum is keeping up on that table.
• Insufficient granularity on a skewed column. Fixed by raising default_statistics_target for that specific column rather than globally.
• Correlated columns. The planner multiplies two selectivities as though the columns were independent, and on genuinely correlated data it lands orders of magnitude low. CREATE STATISTICS with the ndistinct and dependencies kinds exists for this and is materially underused.

Rows Removed by Filter

This counts tuples that were fetched and then discarded. It is the clearest signal that your index does not match your query, and it matters as much on an index scan as on a sequential scan.

Distinguish the three predicate lines:

• Index Cond is work the index did. It is cheap.
• Filter is work done after a tuple was already fetched. It is expensive.
• Recheck Cond on a bitmap scan means the bitmap went lossy and Postgres had to re-examine whole pages.

A large Rows Removed by Filter sitting under an Index Cond means the index got you to the right neighbourhood and you then paid full price to walk it. That is the case for a composite or partial index.

Loops

Every per-node figure on the inner side of a nested loop is a per-loop average, already rounded. A node showing actual time=0.8..0.9 rows=3 loops=94000 is not a fast node. It is 75 seconds of work across 282,000 rows. Misreading this is the most common way an obviously expensive plan gets declared clean.

Three more that only appear under load

Sort Method and Batches. A line reading Sort Method: external merge Disk: 82304kB means the sort exceeded work_mem and spilled to disk. On hash nodes the equivalent is Batches greater than 1. Note that work_mem is allocated per node per parallel worker, not per query, so a plan with four sorts and two workers can claim several times what you think you configured. This is why raising work_mem globally to fix one report is a reliable way to cause an out-of-memory event at peak.

Heap Fetches on an index-only scan. A non-trivial number here means the visibility map is stale, which means autovacuum is behind on that table. The scan is index-only in name and doing heap I/O in practice. This is close to invisible in staging, where data was loaded and vacuumed recently, and it is a common cause of a query degrading over weeks with no deployment to blame.

Workers Planned against Workers Launched. If the plan was costed as parallel and fewer workers launched because max_parallel_workers was exhausted by concurrent queries, you get a plan priced for parallelism executing serially. It only happens under contention, which is when it hurts most.

Numbers that predict nothing

cost= is in arbitrary planner units, not milliseconds, and it is only meaningful for comparing candidate plans for the same query on the same configuration. It is an input to the planner's decision, not a measurement of anything. Tuning until the cost number drops optimises the model rather than the query.

A single execution time tells you about cache state as much as about the query. Compare warm runs to warm runs, and treat any before-and-after measurement taken seconds apart as suspect.

Seq Scan is not a defect. Reading a small table sequentially is correct, and so is reading a large one when the query genuinely needs a third of it, since random index access to fetch 30% of a table is slower than a sequential read. The signal is never the node type. It is the ratio of rows examined to rows returned, which is why Rows Removed by Filter is on the list above and Seq Scan is not.

Planning Time is usually noise, with two exceptions worth knowing: heavily partitioned tables, and tables carrying a large number of indexes. If planning time is a meaningful share of total time, that is its own finding.

One production-only trap

If your application uses prepared statements, the plan you get by running EXPLAIN ANALYZE with literal values may not be the plan the application executes. After several executions Postgres may switch to a generic plan built without knowledge of the parameter values, and on a skewed column that generic plan can be considerably worse than any of the custom ones.

EXPLAIN (GENERIC_PLAN), available from PostgreSQL 16, shows you what that plan looks like without executing it. plan_cache_mode lets you force custom planning for a workload where the difference is costing you real time.

The order we work in

1. Confirm the query is worth the time, using total_exec_time in pg_stat_statements rather than the number in the incident report.
2. Capture a plan from a slow execution via auto_explain, not from a hand-run reproduction.
3. Read buffers before timings, and compute buffers per returned row.
4. Find the lowest node where estimate and actual diverge by more than 10x. That is usually the root cause.
5. Check Rows Removed by Filter at every scan node.
6. Check for spills, heap fetches, and workers planned but not launched.
7. Only then change something, and change one thing: statistics, an index, or query shape. Re-measure in buffers.

The step that matters most is the second one. Almost every query that is hard to tune is hard because the plan being discussed is not the plan that ran during the incident. Capture the real one and most of these queries stop being mysterious.

---

AlgoCore builds and maintains production web systems. If a query in your stack behaves differently under load than it does in staging, we are happy to look at the plan.

---

Sources

• PostgreSQL 18 documentation: auto_explain — configuration parameters, including sample_rate, log_timing and log_nested_statements
• PostgreSQL 18 documentation: pg_stat_statements — available columns, including total_exec_time and stddev_exec_time
• PostgreSQL commit: Enable BUFFERS with EXPLAIN ANALYZE by default — the change landing in the v18 cycle
• depesz: Waiting for PostgreSQL 18 — Enable BUFFERS with EXPLAIN ANALYZE by default — commentary on the same change
• postgres.ai: EXPLAIN ANALYZE or EXPLAIN (ANALYZE, BUFFERS)? — the case for reading buffers rather than timings

Claims not carrying a link above (nested-loop per-loop semantics, work_mem allocation per node, visibility-map behaviour on index-only scans, GENERIC_PLAN availability from PostgreSQL 16) are documented in the PostgreSQL manual under Using EXPLAIN, Resource Consumption and the EXPLAIN command reference. Verify against the version you run.