PostgreSQL EXPLAIN ANALYZE: Read and Fix Slow Query Plans

A slow PostgreSQL query needs an explanation before it needs another index. EXPLAIN ANALYZE shows what the executor actually did: which rows it visited, how often a node ran, and where work accumulated. The useful question is whether that work matches the result your application needs.

This walkthrough uses a small, disposable orders table to compare plans before and after one index. You will learn to separate estimates from measurements, spot repeated work, and choose the next experiment without treating every sequential scan as a failure.

EXPLAIN ANALYZE Executes the Statement

EXPLAIN displays the selected plan without executing the statement. Adding ANALYZE runs it. An explained UPDATE changes rows, and a SELECT can call functions with side effects. Start with plain EXPLAIN when the cost or behavior is uncertain. See the EXPLAIN command reference.

Build a Small, Disposable Query Lab

Run the SQL blocks in order in one session, ending with the cleanup below. The fixture contains 50,000 orders across 100 tenants, with unique creation times. It deliberately starts without indexes. No application table is changed.

BEGIN;
SET LOCAL statement_timeout = '10s';
SET LOCAL lock_timeout = '1s';

CREATE TEMP TABLE plan_lab_orders (
  id integer NOT NULL,
  tenant_id integer NOT NULL,
  created_at timestamp NOT NULL,
  status text NOT NULL
) ON COMMIT DROP;

INSERT INTO plan_lab_orders
SELECT n,
       1 + (n % 100),
       timestamp '2026-01-01' + n * interval '1 minute',
       CASE WHEN n % 4 = 0 THEN 'open' ELSE 'closed' END
FROM generate_series(1, 50000) AS series(n);

ANALYZE plan_lab_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM plan_lab_orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 20;

Temporary tables belong to the session. Autovacuum cannot analyze them, so the explicit ANALYZE matters. The timeouts bound individual statements and lock waits; if one fires, issue ROLLBACK and restart the lab. They are example limits, not universal production settings.

Expect a scan that finds the tenant's 500 rows, a sort, and a limit. Exact plans depend on PostgreSQL version, settings, and statistics. Save your output. The schematic below describes the expected shape and contains no benchmark measurements:

Illustrative plan shape, not captured output:
Limit: return the newest 20 matching rows
  Sort: order the tenant's matching rows by created_at DESC
    Seq Scan: inspect orders and filter tenant_id = 42

Read Estimates, Actual Rows, and Loops Together

Start at the leaves to understand where rows come from, then follow them toward the result. Use the PostgreSQL plan-reading guide to interpret each field:

For an illustrative nested-loop child with rows=3 loops=2000, roughly 6,000 rows were emitted across executions. A per-loop duration of 0.05 ms represents roughly 100 ms across those loops. Parent timings include child work, so adding every node's time double-counts it. Parallel execution needs additional care because worker activity overlaps.

Compare estimated and actual rows at the same node. A child under LIMIT may stop early, so a lower actual count does not automatically mean a bad estimate. Investigate the first substantial unexplained mismatch, then check its downstream effects.

Change the Access Path and Compare Work

The query fixes one tenant and asks for its newest orders. A composite B-tree index can provide that access path and order:

CREATE INDEX plan_lab_orders_tenant_created_idx
ON plan_lab_orders (tenant_id, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM plan_lab_orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 20;

Look for an index scan beneath the limit, with no separate sort. The leading tenant equality narrows the index region, and timestamp order lets execution stop after 20 matches. This is the combination described in PostgreSQL's multicolumn index and index ordering documentation.

Record the node shape, rows visited, buffer activity, and execution time for both versions. Repeat each measurement under comparable conditions; index creation itself can warm caches. This lab demonstrates a mechanism, not a promised speedup. It also returns id, which is absent from the index, so heap access remains necessary.

A sequential scan can be the right choice for a small table or a query returning much of it. Avoid forcing index usage to make a plan look better. An extra index also adds storage and write maintenance. The existing guide to composite and covering indexes provides the broader design context.

Read Buffers Without Inventing Disk Measurements

BUFFERS reports block activity. A shared hit found a regular-table or index block in PostgreSQL's shared buffers. A shared read needed a read into them, but the operating system may have served it from its cache. It is not proof of a physical storage read.

This temporary-table lab normally reports local buffers. temp read and temp written describe working files for operations such as sorts and hashes, not the temporary table's ordinary blocks. Counts include repeated accesses, and parent counts include children. Do not sum the tree or multiply buffer counts by loops. The BUFFERS option documentation defines these categories.

Cache state changes timing. Compare several runs and retain the first run separately. PostgreSQL also relies on the operating system cache, so restarting a connection does not create a cold-cache test. Never clear a production cache just to make an experiment look controlled.

Fix Bad Estimates Before Adding More Indexes

Stale statistics, skewed values, and correlated columns can mislead the planner. Begin with a targeted ANALYZE table_name after substantial data changes. This collects statistics; it does not rewrite the query. On persistent tables, check why automatic analysis did not keep up. See the ANALYZE reference.

Then inspect pg_stats for common values and distribution. A tenant with half the table may need a different plan from one with ten rows. Raising a column's statistics target can improve sampling detail at additional analysis cost. For related predicates, independent column statistics may still miss their relationship. PostgreSQL documents these tradeoffs under planner statistics.

Our fixture intentionally correlates tenant and status: every order for tenant 41 is open. Compare the scan's estimated and actual rows in this optional experiment before and after adding dependency statistics:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM plan_lab_orders
WHERE tenant_id = 41 AND status = 'open';

CREATE STATISTICS pg_temp.plan_lab_tenant_status (dependencies)
ON tenant_id, status FROM plan_lab_orders;
ANALYZE plan_lab_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM plan_lab_orders
WHERE tenant_id = 41 AND status = 'open';

Extended statistics improve selected estimates; they create no row lookup structure and do not solve every join-estimation problem. Choose the statistics kind for the observed mismatch, and verify whether the estimate actually improves.

Check Sorts and Spills Before Raising Memory

A sort reporting external merge and disk usage has spilled working data. Check whether an index can supply the order, filters can reduce input earlier, or unnecessary wide columns can be removed. More memory is only one possible change.

work_mem applies to individual operations, with concurrent sessions and workers multiplying demand; it is not one fixed allowance per server. Test a scoped change under realistic concurrency before changing the global setting. A fast isolated query can still create unacceptable memory pressure.

Finish the disposable lab with:

ROLLBACK;

A Production Investigation Checklist

1. Capture the real input. Keep SQL, parameter values and types, schema, PostgreSQL version, and representative data distribution. A convenient test tenant may hide skew.
2. Set execution boundaries. Inspect the estimated plan first. Choose a controlled environment and appropriate statement and lock timeouts before collecting actual execution evidence.
3. Identify one cause. Connect excess work to a specific scan, cardinality error, repeated lookup, or spill. Preserve query semantics while testing.
4. Validate the change. Compare work and latency across representative parameters. For production index rollout, follow the safe database migration workflow.
5. Measure the request again. Query execution is only part of latency. Pool waits, network transfer, and application processing need separate measurements; review connection pooling when database execution is fast but requests still wait.

The output should support a concrete statement: which work was unnecessary, what changed, and whether the application improved. Keep the before-and-after plans with that explanation so the next engineer can assess the decision.