PostgreSQL EXPLAIN ANALYZE: Read Plans and Choose Useful Indexes

Know what you are measuring

EXPLAIN shows a proposed plan. Adding ANALYZE runs the statement and records execution statistics. Estimated cost uses planner units; actual time uses milliseconds. BUFFERS adds block activity, including cache hits and reads. These values describe different things, so a cost of 200 is not a prediction of 200 milliseconds.

Because ANALYZE executes the statement, start this exercise in a disposable database. An analyzed UPDATE still updates rows; the word EXPLAIN does not make a write read-only.

• PostgreSQL 17: EXPLAIN

Reproduce a latest-jobs query

Our original example selects the latest twenty ready jobs for one tenant. The fixture contains 100,000 rows and spreads ready jobs across tenants. Run the setup once in an empty PostgreSQL 17 database. EXPLAIN shows the plan, not query results: also run the SELECT without EXPLAIN to save the identifiers. The first three are 99041, 98041, and 97041.

The query has two equality filters and a descending order. Notice that id breaks timestamp ties. Returning the right twenty rows matters alongside speed.

CREATE TABLE jobs (
  id bigint PRIMARY KEY,
  tenant_id integer NOT NULL,
  state text NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO jobs
SELECT g, (g % 100) + 1,
       CASE WHEN (g / 100) % 10 = 0 THEN 'ready' ELSE 'done' END,
       TIMESTAMPTZ '2026-01-01 00:00:00+00' + g * INTERVAL '1 second'
FROM generate_series(1, 100000) AS g;

ANALYZE jobs;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 42 AND state = 'ready'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Look for work that does not survive the query

Trace rows from the scan toward the top of the plan. Rows Removed by Filter shows discarded rows. A Sort before Limit can reveal work needed before the first page is available. Compare estimated and actual row counts to identify a possible statistics problem. When a node runs repeatedly, its reported rows and times are per-loop averages; account for loops instead of reading them as totals.

Do not add parent and child times as independent costs: parent execution includes work below it. Look for a specific expensive path, not the most alarming-looking number.

• PostgreSQL 17: Using EXPLAIN

Match the index to filtering and ordering

For this query, try a B-tree whose first keys are tenant_id and state, followed by created_at and id in the requested order. The equality conditions narrow the leading keys; the remaining keys describe the ordered slice. This is a hypothesis for this access pattern, not a rule to index every WHERE column.

In our isolated PostgreSQL 17.9 check, the fixture changed from a sequential scan plus sort to an index-only scan under Limit, preserving the same twenty rows. Exact timings and the selected scan depend on the environment and table state. An index-only scan is not guaranteed.

CREATE INDEX jobs_tenant_state_order_idx
ON jobs (tenant_id, state, created_at DESC, id DESC);

ANALYZE jobs;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 42 AND state = 'ready'
ORDER BY created_at DESC, id DESC
LIMIT 20;

• PostgreSQL 17: Multicolumn Indexes

Choose the next check from the evidence

An index name in the plan is not a success criterion. The following decisions help distinguish avoidable work from a sensible scan. Each points to a check rather than an automatic configuration change.

Test the workload, not one lucky run

Refresh statistics after loading the fixture. Repeat measurements and note cache conditions. Then test tenants with different row counts and queries returning larger portions of the table. PostgreSQL's index-usage guidance emphasizes realistic data: an artificial distribution only establishes what works for that distribution.

• Check identical rows and ordering before and after the change.
• Compare discarded rows, sorting, buffers, and execution time.
• Measure index size and the effect on inserts and updates.
• Explain which observed bottleneck the index addresses.

• PostgreSQL 17: Examining Index Usage
• RecallDeck: PostgreSQL internals and optimization preparation

Quick answers

Frequently asked questions