Query Optimization: Reading an EXPLAIN Plan

A slow SQL statement does not explain itself. It may read too many pages, choose a join suited only to small inputs, spill data to disk, or repeat a cheap-looking operation thousands of times. PostgreSQL’s EXPLAIN exposes those decisions; EXPLAIN ANALYZE runs the statement and places measurements beside estimates.

The plan is evidence, not a prescription, and a sequential scan is not automatically a bug. The skill is connecting the first important estimate error or expensive data movement to schema, statistics, indexing, or SQL.

Start with a concrete workload #

Consider a multi-tenant commerce database. The report below finds the 20 customers with the most paid line-item revenue during the last 30 days.

CREATE TABLE customers (
    id bigint PRIMARY KEY,
    tenant_id bigint NOT NULL,
    name text NOT NULL,
    UNIQUE (tenant_id, id)
);

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    tenant_id bigint NOT NULL,
    customer_id bigint NOT NULL,
    status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    created_at timestamptz NOT NULL,
    FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)
);

CREATE TABLE order_items (
    order_id bigint NOT NULL REFERENCES orders (id),
    product_id bigint NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price_cents integer NOT NULL CHECK (unit_price_cents >= 0),
    PRIMARY KEY (order_id, product_id)
);

SELECT c.id,
       c.name,
       SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
FROM orders AS o
JOIN customers AS c
  ON c.tenant_id = o.tenant_id
 AND c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.tenant_id = 42
  AND o.status = 'paid'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.name
ORDER BY revenue_cents DESC
LIMIT 20;

Use EXPLAIN (ANALYZE, BUFFERS, SETTINGS) in a controlled environment. ANALYZE means “execute and measure,” not “refresh table statistics” in this syntax. A shortened result might look like this:

Limit  (cost=18432.11..18432.16 rows=20 width=48)
       (actual time=1841.802..1841.809 rows=20 loops=1)
  Buffers: shared hit=94112 read=68744, temp read=1512 written=2634
  -> Sort  (cost=18432.11..18434.73 rows=1048 width=48)
           (actual time=1841.800..1841.803 rows=20 loops=1)
       Sort Key: (sum((oi.quantity * oi.unit_price_cents))) DESC
       Sort Method: top-N heapsort  Memory: 31kB
       -> HashAggregate  (cost=18393.95..18404.43 rows=1048 width=48)
                         (actual time=1782.401..1837.522 rows=18317 loops=1)
            Batches: 5  Memory Usage: 8241kB  Disk Usage: 12184kB
            -> Hash Join  (cost=7214.40..17798.31 rows=79419 width=24)
                          (actual time=113.051..1588.729 rows=642811 loops=1)
                 Hash Cond: (oi.order_id = o.id)
                 -> Seq Scan on order_items oi
                       (cost=0.00..8932.00 rows=1800000 width=20)
                       (actual time=0.018..512.346 rows=1800000 loops=1)
                 -> Hash
                       (cost=7188.15..7188.15 rows=2100 width=20)
                       (actual time=112.771..112.772 rows=48732 loops=1)
                      -> Seq Scan on orders o
                           (cost=0.00..7188.15 rows=2100 width=20)
                           (actual time=0.022..101.335 rows=48732 loops=1)
                           Filter: ((tenant_id = 42) AND (status = 'paid')
                             AND (created_at >= (CURRENT_DATE - '30 days'::interval)))
                           Rows Removed by Filter: 351268
Planning Time: 1.284 ms
Execution Time: 1844.126 ms

The omitted customer join is inexpensive. The important story remains: PostgreSQL expected 2,100 qualifying orders but found 48,732, then predicted 79,419 joined items but produced 642,811. That error distorted choices above it.

Read the tree and its numbers correctly #

Plans are trees printed with indentation. Read from the deepest nodes upward because children produce rows consumed by parents. In the example, PostgreSQL scans orders and order_items, builds a hash from qualifying orders, joins line items, aggregates by customer, sorts only the best 20 groups, then applies Limit.

Each metric answers a different question:

Cost combines estimated page I/O and CPU. It ranks candidates; it does not predict latency. Actual times are milliseconds, but parent time includes descendants. Find jumps in time, rows, buffers, or temporary I/O instead of summing the tree.

Rows and loops belong together. actual rows=13 loops=48732 means about 633,516 rows total. Even 0.020 ms per loop becomes expensive when repeated thousands of times. Inspect per-worker detail in parallel plans.

The most valuable ratio is often estimate accuracy:

A factor near one is healthy. A factor of 23 at orders is a strong lead because it appears low in the tree and propagates upward. Handle zero according to the actual scale.

Recognize scans, joins, sorts, and aggregates #

Node names describe algorithms, not verdicts. Their suitability depends on cardinality, ordering, selectivity, memory, and data locality.

An index-only scan still visits the heap unless the visibility map marks pages all-visible; frequent updates can yield many Heap Fetches. A sequential scan may also be correct for a tenant owning half a table, where an index would cause many random heap visits.

In the baseline, the final sort is healthy: top-N heapsort keeps only 20 candidates and uses 31 kB. The aggregate is not: five batches and 12 MB of disk indicate a spill. The full order_items scan is also costly because only line items belonging to 48,732 recent orders are needed. These observations are more actionable than the presence of the word Seq Scan alone.

Repair the first bad assumption #

The lowest consequential problem is the estimate for filtered orders. Inspection shows tenant 42 is much larger than an average tenant and converts far more orders to paid. Default single-column statistics cannot fully represent that correlation. First refresh statistics after confirming autovacuum is healthy:

ANALYZE orders;

CREATE STATISTICS orders_tenant_status_mcv (mcv, dependencies)
ON tenant_id, status
FROM orders;

ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE orders;

Extended statistics model combinations such as (42, 'paid'); the larger target samples skew better. Statistics improve decisions but not access cost, so add indexes matching the equality filter, range, and join lookup:

CREATE INDEX CONCURRENTLY orders_paid_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, customer_id)
WHERE status = 'paid';

CREATE INDEX CONCURRENTLY order_items_order_id_cover_idx
ON order_items (order_id)
INCLUDE (quantity, unit_price_cents);

Column order matters. Equality on tenant_id comes before the range on created_at; columns needed after the search are payload under INCLUDE. The partial predicate keeps cancelled and pending orders out, but PostgreSQL can use it only when it can prove the query implies status = 'paid'. The second index supports repeated line-item lookups and may become index-only when visibility permits.

After creation, the central portion of a new plan may look like this:

HashAggregate  (cost=36291.20..36462.01 rows=17081 width=48)
               (actual time=381.410..407.885 rows=18317 loops=1)
  Batches: 1  Memory Usage: 7185kB
  -> Nested Loop  (cost=0.85..31469.80 rows=642853 width=24)
                   (actual time=0.071..286.336 rows=642811 loops=1)
       -> Index Only Scan using orders_paid_tenant_created_idx on orders o
             (cost=0.43..2241.54 rows=47920 width=20)
             (actual time=0.047..19.831 rows=48732 loops=1)
             Index Cond: ((tenant_id = 42)
               AND (created_at >= (CURRENT_DATE - '30 days'::interval)))
             Heap Fetches: 126
       -> Index Only Scan using order_items_order_id_cover_idx on order_items oi
             (cost=0.42..0.48 rows=13 width=20)
             (actual time=0.003..0.004 rows=13 loops=48732)
             Index Cond: (order_id = o.id)
             Heap Fetches: 811
Execution Time: 414.902 ms

The planner now predicts both inputs closely. The nested loop is reasonable because its inner operation is a narrow, ordered index lookup. Runtime falls from about 1.84 seconds to 415 ms, and the aggregate no longer spills. This is a workload-specific win, not proof that nested loops or covering indexes are universally better.

SQL shape can also remove unnecessary work. A common report joins line items only to test their existence, then adds DISTINCT to erase duplicates. Express the intent as a semi-join instead:

-- Avoid: join multiplication followed by duplicate removal.
SELECT DISTINCT o.id
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.tenant_id = $1 AND oi.product_id = $2;

-- Prefer when only existence matters.
SELECT o.id
FROM orders AS o
WHERE o.tenant_id = $1
  AND EXISTS (
      SELECT 1
      FROM order_items AS oi
      WHERE oi.order_id = o.id AND oi.product_id = $2
  );

The executor may stop after the first match. Measure it: semantic clarity creates an opportunity, not a guarantee.

Account for statistics, spills, and parameters #

Cardinality estimation is difficult with correlated columns, skewed values, expressions, or stale statistics. Check pg_stat_user_tables.last_analyze, row counts, and pg_stats. Extended statistics model dependencies and common combinations; an expression index may help lower(email) = $1.

Spills are visible as sort methods such as external merge, hash Batches above one, or temporary blocks in BUFFERS. Raising work_mem globally is dangerous: it applies per memory-consuming plan node, per worker, and per concurrent query. A 64 MB setting can multiply into gigabytes. Prefer reducing rows early, improving estimates, or adding useful order. If a known reporting transaction still needs more memory, use a bounded local setting and load-test concurrency:

BEGIN;
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT /* report */ ...;
ROLLBACK;

Parameters add another dimension. Tenant 7 may have 40 recent orders while tenant 42 has 48,732. PostgreSQL may replace a prepared statement’s custom plans with a generic plan whose average cost looks competitive, yet performs poorly for an outlier.

Compare representative values with EXPLAIN (ANALYZE, BUFFERS) EXECUTE report(7) and report(42). plan_cache_mode = force_custom_plan or force_generic_plan is useful as a diagnostic experiment, not a first-choice permanent fix. Durable options include better statistics, an access path that works over the distribution, separating analytically distinct workloads, or carefully choosing whether the application prepares that statement.

Use a production-safe optimization loop #

A disciplined production loop is safer:

1. Find important queries from traces, database monitoring, or pg_stat_statements; rank by total time, tail latency, call count, and user impact.
2. Save the exact SQL shape, PostgreSQL version, relevant settings, schema, row counts, and representative parameter sets. Compare warm and cold-cache behavior when it matters.
3. Run plain EXPLAIN first. Use EXPLAIN ANALYZE on a staging clone, a suitable replica, or production only with strict limits and operational approval. Prefer TIMING OFF when per-node clock overhead distorts very fast queries.
4. Read bottom-up. Mark the first major estimate error, row explosion, buffer jump, repeated loop, or spill. Form one hypothesis.
5. Change one variable: statistics, one index, or one SQL rewrite. Build production indexes with CONCURRENTLY, while monitoring its longer duration and extra I/O.
6. Re-run with small, typical, and worst-case parameters. Compare execution time, buffers, temporary I/O, planning time, write overhead, and plan stability.
7. Deploy gradually, observe p50/p95/p99 and system load, then remove redundant indexes only after proving they are unused and accounting for replicas and periodic jobs.

Some anti-patterns repeatedly waste effort: disabling sequential scans to force a desired picture, adding an index for every predicate, testing only one parameter, trusting estimated cost as latency, increasing work_mem globally, wrapping indexed columns in functions without expression indexes, and rewriting a readable query before locating the expensive node. Hints and planner toggles can test a hypothesis, but they freeze a choice around today’s distribution rather than repairing the information or access path.

Indexes consume storage, amplify writes, add vacuum work, and can prolong replication. An hourly 400 ms report may not justify slowing a write-heavy table all day. Minimize total workload cost within latency and correctness requirements.

Takeaways #

• Read a plan from the leaves upward and follow the rows as each parent consumes its children.
• Keep estimates separate from measurements: cost is not time, and rows must be interpreted with loops.
• Look for the earliest consequential cardinality error, spill, row explosion, or repeated access rather than declaring every sequential scan bad.
• Choose indexes from predicates, ordering, joins, and workload economics; validate column order, partial predicates, visibility, and write cost.
• Treat skew and parameter sensitivity as normal production properties. Test small, typical, and outlier values.
• Optimize through a controlled loop: observe, hypothesize, change one thing, measure buffers and latency, then monitor after deployment.

An EXPLAIN plan becomes useful when it stops looking like a wall of indentation and starts reading like a data-flow trace. The optimizer made a decision from the information it had. Your job is to find where that information or the available access paths diverge from reality, then improve the smallest thing that changes the outcome.