Somewhere in Houston, a query that used to run in 40 milliseconds now takes 40 seconds — and the dashboard the whole team depends on is timing out. Before you buy bigger hardware, tune shared_buffers, or rewrite anything, you need to see what the planner actually decided to do. That is what EXPLAIN is for: it reveals PostgreSQL’s execution plan, node by node, so you can stop guessing and fix the real bottleneck.

EXPLAIN work is having a moment. AWS published a guide to using EXPLAIN plans to improve query performance in Aurora DSQL, and at the other end of the spectrum, OpenAI explained how it keeps PostgreSQL fast at the scale of 800 million ChatGPT users. The skill in the middle — reading a plan quickly — is the highest-leverage thing any developer or DBA can learn for a slow database.

Start With EXPLAIN, Not a Guess

The most common performance mistake is changing things before looking at the plan. “Let’s bump work_mem” or “let’s add an index on every column” are guesses. EXPLAIN replaces guesses with evidence. It shows the query tree: which tables are scanned, in what order, which join method is used, and what the planner estimates each step will cost.

Run it on the slow query first, always. You get the fastest win-to-effort ratio in all of PostgreSQL tuning.

How to Read a Plan: Bottom-Up, Right-to-Left

Execution plans read like a tree: the innermost nodes run first. Work from the bottom of the output upward, and within each line, read the operation, then the cost, then the row estimate. The key columns on each node are:

• cost — the planner’s estimated expense; the first number is startup cost, the second is total cost.
• rows — the planner’s estimate of rows this step will produce.
• actual time / rows — real measurements, only present when you use ANALYZE.
• loops — how many times the node executed; multiply actual time by loops to get the true cost.

The planner’s estimates matter even when you have ANALYZE output. When estimates and actuals diverge badly, the planner will make bad decisions downstream — and that mismatch is your clue that statistics are stale or an expression in the WHERE clause is hiding the indexed column.

The Missing Index, Caught on the First Read

Here is a classic. An orders table with 4.8 million rows, and a report that filters by customer and date:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, status, total
FROM orders
WHERE customer_id = 4271
  AND created_at > now() - interval '90 days';

The plan says it all in one line:

Seq Scan on orders  (cost=0.00..184321.90 rows=41 width=32)
                     (actual time=0.041..412.887 rows=38 loops=1)
  Filter: ((customer_id = 4271) AND (created_at > (now() - '90 days'::interval)))
  Rows Removed by Filter: 4821134
Planning Time: 0.214 ms
Execution Time: 413.002 ms

Read it like this: the planner estimated 41 rows but scanned all 4.8 million to find them — 4,821,134 rows removed by the filter. That “Rows Removed by Filter” number is the smoking gun. The fix is a composite index that matches the predicate:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

Re-run the same EXPLAIN (ANALYZE, BUFFERS) and the Seq Scan becomes an Index Scan with execution time in the low single-digit milliseconds. One index, one query, no hardware purchased.

Five Red Flags to Hunt For

• Seq Scan on a large table. Not always wrong — but on a multi-million-row table with a selective filter, it usually means a missing or unusable index.
• Estimate/actual mismatch. If the planner thinks 41 rows and finds 38, fine. If it thinks 41 and finds 400,000, investigate stale statistics or a non-sargable predicate.
• Nested Loop with huge inner loops. A nested loop that runs 1 million times with a seq scan inside is a join-order or index problem.
• Sort on a big result set. A sort node over hundreds of thousands of rows is often a missing index that could serve the ORDER BY directly.
• Rows Removed by Filter is large. The scan is doing far more work than the result needs — your index or partitioning strategy is off.

Read the Buffers, Not Just the Times

Execution time is noisy; buffer reads are not. Add BUFFERS to see how much of the work hit shared buffers versus the OS cache versus disk:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ... ;

Look at shared hit versus shared read. If every run of a “hot” query shows thousands of shared read blocks, you have a caching problem — either shared_buffers is too small for the working set, or the query is touching far more data than it should. A query that reads 10,000 buffers to return 38 rows is a query that will fall over as the table grows.

When the Plan Looks Fine But the App Is Still Slow

Sometimes the plan is clean and the query is still slow in production. Then the problem is usually not this query — it is the aggregate load. That is where pg_stat_statements comes in: it ranks every query by total time, calls, and rows, so you can find the top offenders instead of the loudest complaint. And when the slow query only misbehaves at 3 p.m., enable auto_explain with a log threshold so production captures the plan automatically — the plan from the actual run, not the one you reproduced locally.

Teams running PostgreSQL at extreme scale treat plan reading as a core skill. OpenAI’s write-up on scaling PostgreSQL to power 800 million ChatGPT users makes the same point from the top down: measure first, tune second, and let the planner’s own output guide every change. That discipline is what separates databases that degrade gracefully from databases that need a “fire drill” every quarter.

For Houston teams running energy, logistics, and healthcare workloads, the pattern is the same whether the database is on-premises, in RDS, or in Aurora DSQL: the plan is the truth, and EXPLAIN is the fastest way to hear it. Start with the slowest query in pg_stat_statements, read its plan bottom-up, and fix the one red flag that jumps out. That is the whole job — and it takes minutes, not weeks.