Reading EXPLAIN ANALYZE

Your query is slow. You add an index. It's still slow. You add another. Now writes are slow too, and you still don't know what the original problem was. Sound familiar? The fix is to stop guessing and ask the database directly: "How exactly did you plan to run this, and how did it actually go?" That's what EXPLAIN and EXPLAIN ANALYZE do. They print the query's execution plan — the step-by-step strategy the optimizer chose — and once you can read one, most performance mysteries solve themselves in about a minute.

EXPLAIN vs. EXPLAIN ANALYZE

These two look alike but behave very differently:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;           -- shows the PLAN only
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;   -- RUNS the query, shows plan + reality

• EXPLAIN shows what the planner intends to do and its estimates. The query is not executed.
• EXPLAIN ANALYZE actually runs the query and adds what really happened: real timings and real row counts.

That second one has a warning label. Because it really executes, EXPLAIN ANALYZE on an UPDATE, DELETE, or INSERT really changes your data. Wrap those in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;

The gap between estimated and actual is where most of the value lives — we'll come back to it.

Anatomy of a Plan

Here's a plan from PostgreSQL for a query against a one-million-row orders table. (The numbers are illustrative, but the shape is exactly what you'll see.)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';

 Seq Scan on orders  (cost=0.00..25346.00 rows=48 width=44) (actual time=0.031..118.402 rows=52 loops=1)
   Filter: ((customer_id = 42) AND (status = 'shipped'::text))
   Rows Removed by Filter: 999948
   Buffers: shared hit=3 read=10343
 Planning Time: 0.110 ms
 Execution Time: 118.470 ms

Read it as a tree, from the most-indented line inward and upward — here there's just one node. Break down that first line:

And the sneaky detail that trips everyone up: actual time and rows are averages per loop. If a node shows actual time=0.5 rows=10 loops=1000, it really did about 500 ms of work and returned about 10,000 rows. Always multiply by loops.

Now the two lines that tell the story: Rows Removed by Filter: 999948. The database read a million rows, threw away 999,948 of them, and kept 52. That is the signature of a missing index.

Fix It, Then Read the Plan Again

CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

 Index Scan using idx_orders_cust_status on orders  (cost=0.42..195.60 rows=48 width=44) (actual time=0.025..0.210 rows=52 loops=1)
   Index Cond: ((customer_id = 42) AND (status = 'shipped'::text))
   Buffers: shared hit=57 read=3
 Planning Time: 0.190 ms
 Execution Time: 0.250 ms

From about 118 ms to a quarter of a millisecond, and the plan itself tells you why: Index Cond replaced Filter, and the buffer line shows 60 pages touched instead of over 10,000. (If you want the full story on how indexes work and how to design them, see our Database Indexing Deep Dive.)

The Scan Types You'll See Most

A common surprise: Postgres will choose a Seq Scan even when a perfectly good index exists — if the planner estimates that you need a large fraction of the table, reading it straight through really is cheaper than bouncing between index and table. A Seq Scan is not automatically a bug. A Seq Scan that discards 99.99% of what it reads is.

How Tables Get Joined

When a query joins tables, each join node picks one of three strategies:

• Nested Loop — for each row from the outer table, look up matches in the inner one. Superb when the outer side is small and the inner side has an index; catastrophic when both sides are big.
• Hash Join — build an in-memory hash table from one side, then stream the other side past it. The workhorse for joining large, unsorted sets.
• Merge Join — walk two already-sorted inputs in lockstep. Excellent when both sides come pre-sorted from indexes.

Which one the planner picks is not the problem. Whether it picked the right one for the real data is — and that brings us to the single most useful habit in reading plans.

The Big One: Estimated vs. Actual Rows

Look at this fragment:

 Nested Loop  (cost=0.85..42.10 rows=1 width=88) (actual time=0.040..4820.553 rows=48213 loops=1)

The planner expected 1 row. It got 48,213. That's off by a factor of nearly fifty thousand, and everything above it in the plan was chosen based on the belief that this step would be tiny. Nested Loop is a perfect choice for one row and a disaster for 48,000 — hence the nearly five seconds.

When estimates and actuals disagree by a lot, the planner is working from bad information, and the usual fixes are:

1. Refresh statistics. Run ANALYZE orders; — Postgres decides using sampled statistics about your data, and stale ones produce bad plans. (Autovacuum normally does this, but a bulk load can outrun it.)
2. Teach it about correlated columns. The planner assumes columns are independent. If city = 'Provo' and state = 'UT' always travel together, it multiplies their selectivities and badly under-counts. CREATE STATISTICS lets you declare the relationship so the estimate improves.
3. Raise the statistics target on a skewed column (ALTER TABLE ... ALTER COLUMN ... SET STATISTICS) so the planner sees more of its distribution.

Other Red Flags to Scan For

• Sort Method: external merge Disk: 25000kB — the sort didn't fit in work_mem and spilled to disk. Slow. Raise work_mem for that session, or add an index that delivers rows already sorted.
• Hash Batches: 8 (anything above 1) — the hash table spilled too. Same culprit.
• Heap Blocks: lossy on a Bitmap Heap Scan — the bitmap outgrew memory and became less precise, forcing extra rechecks.
• Huge loops numbers on an inner node — the classic N+1 pattern in query form. Multiply and see how much it's really costing you.
• A function wrapped around an indexed column (WHERE lower(email) = ...) showing a Seq Scan — the plain index can't be used. Index the expression instead.

Use BUFFERS

EXPLAIN tells you how long something took; BUFFERS tells you how much data it touched, which is often the more honest measure — time varies with whatever else the server is doing, but pages read is stable.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

shared hit means the page was already in Postgres's cache; shared read means it had to be fetched from the operating system or disk. In PostgreSQL 18 and newer, BUFFERS is on by default whenever you use ANALYZE; on older versions you have to ask for it explicitly.

Two more options worth knowing:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;   -- machine-readable, great for tooling
EXPLAIN (ANALYZE, VERBOSE) SELECT ...;                -- adds output columns and more detail

Plans can run to hundreds of lines for complex queries. Free visualizers such as explain.depesz.com and explain.dalibo.com will color-code the slowest nodes for you. (Plans reveal your table and column names, so think before pasting one from a production system into a public website.)

What About MySQL and MariaDB?

The idea is identical, but the output looks different. Classic MySQL EXPLAIN returns a table instead of a tree:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

The columns to watch:

Newer MySQL (8.0.18 and later) also supports EXPLAIN ANALYZE, which actually runs the query and gives you a tree like Postgres does, along with EXPLAIN FORMAT=TREE. MariaDB takes a different route: its ANALYZE SELECT ... statement executes the query and reports real numbers next to the estimates.

A Four-Step Habit

Next time something is slow, don't reach for another index. Instead:

1. Run EXPLAIN (ANALYZE, BUFFERS) on the real query with realistic data.
2. Find the slowest node — remember to multiply by loops.
3. Compare estimated and actual rows on that node. A big gap means fix the statistics first.
4. Only then decide whether you need an index, a rewritten query, or a configuration change — and run EXPLAIN ANALYZE again to prove the fix worked.

The plan isn't a mystery. It's the database explaining itself in plain sight — you just have to learn to read it.