Analyze Query Plans with EXPLAIN

Your database was fine yesterday. Today, the same query that used to return in 50ms now takes 4 seconds. The table has 2 million rows, the indexes look right, and you've tried rewriting the query three times — nothing helps. You're not alone: most PostgreSQL performance problems are not bugs, they're plans. The database's query planner makes decisions based on statistics, costs, and heuristics, and when those decisions go wrong, no amount of guessing will fix it. This lesson gives you the surgeon's scalpel: the EXPLAIN command. Once you learn to use EXPLAIN to analyze query plans, you'll stop treating slow queries as mysteries and start treating them as diagnostics — problems you can read and fix.

The problem this lesson solves

Every SQL query you run goes through a hidden pipeline: parse → rewrite → plan → execute. Most developers only see the final result set, never the plan. But the plan is the difference between a fast query and a slow one.

Consider a common scenario: you have a users table with an index on email, and you run:

SELECT * FROM users WHERE email = 'alice@example.com';

If the planner decides to scan the whole table (Seq Scan), that's 2 million rows read. If it uses the index (Index Scan), that's maybe 5 rows. Which one runs? The planner decides — and it sometimes picks wrong.

Why this hurts you now: - You can't fix a query you can't see. - Guessing which part is slow wastes hours. - Statistics are stale, indexes are missing, or the planner just chose a bad join order — but you'll never know without reading the plan.

This lesson gives you the tool to see the execution plan, understand its vocabulary, and know exactly what to correct.

Core concept / mental model

The best mental model: the query planner is a cost-based optimizer. It doesn't know your data; it only knows statistics — row counts, distinct values, histogram buckets — gathered (or guessed) about your tables. For each possible plan, it estimates a cost (an arbitrary unit representing I/O and CPU). It then picks the plan with the lowest estimated cost.

EXPLAIN shows you that plan, along with the cost estimates and the actual execution statistics (when you use ANALYZE). It's like a flight recorder: it tells you what the pilot (planner) decided and why.

Key terms to load into your working vocabulary:

Pro tip: The plan is read inside out. Start at the deepest, most indented node — that's the first thing executed. Work your way up to the top node, which is the final output.

How it works step by step

What happens when you run EXPLAIN?

1. You write EXPLAIN before your query. The planner generates a plan (without executing it) and returns a textual representation of that plan as rows.
2. The output shows nodes with cost, rows, and width estimates. For each node, you'll see something like cost=0.00..35.50 rows=10 width=244. The cost is an estimated number of arbitrary units — the 0.00 is startup cost, 35.50 is total cost. rows is the estimated output rows. width is the estimated average row size in bytes.
3. EXPLAIN ANALYZE actually runs the query. It adds actual timings (actual time=...) and row counts. This shows you the estimated vs actual gap — the most important diagnostic signal.
4. You compare estimates to actuals. If rows=10 estimated but actual rows=10000, the planner's statistics are off — likely missing or stale stats.
5. You interpret the node types and costs to find the bottleneck (see next section).

Reading order rule: Start at the innermost node. That's the first operation. Each parent uses the output of its children. The top node is the final result.

Simple example plan:

EXPLAIN SELECT * FROM users WHERE id = 42;

Output (simplified):

Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=514)
  Index Cond: (id = 42)

Reading: The planner chose an Index Scan on the primary key, estimated a single row, and the condition is id = 42. Good plan.

Hands-on walkthrough

Let's build a realistic scenario and analyze it end-to-end.

Setup: create sample data

-- Create a table and an index
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Insert 1 million rows (takes a few seconds)
INSERT INTO orders (user_id, total)
SELECT (random() * 10000)::int, (random() * 1000)::numeric(10,2)
FROM generate_series(1, 1000000);

-- Create an index on user_id
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Refresh statistics
ANALYZE orders;

Step 1: Run EXPLAIN without ANALYZE

EXPLAIN SELECT * FROM orders WHERE user_id = 500;

Output (your costs may differ slightly):

Bitmap Heap Scan on orders  (cost=62.37..1457.53 rows=989 width=24)
  Recheck Cond: (user_id = 500)
  ->  Bitmap Index Scan on idx_orders_user_id  (cost=0.00..62.12 rows=989 width=0)
        Index Cond: (user_id = 500)

What you see: A two-step plan. First the index finds matching row locations (Bitmp Index Scan), then reads the actual data rows (Bitmap Heap Scan). The planner expects ~989 rows (since user_id ranges from 0 to 10000, each appears ~100 times).

Step 2: Run EXPLAIN ANALYZE to get real numbers

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 500;

Output:

Bitmap Heap Scan on orders  (cost=62.37..1457.53 rows=989 width=24) (actual time=0.019..0.635 rows=1053 loops=1)
  Recheck Cond: (user_id = 500)
  Heap Blocks: exact=108
  ->  Bitmap Index Scan on idx_orders_user_id  (cost=0.00..62.12 rows=989 width=24) (actual time=0.025..0.025 rows=1053 loops=1)
        Index Cond: (user_id = 500)
Planning Time: 0.098 ms
Execution Time: 0.669 ms

Compare: Estimated rows=989, actual rows=1053 — close enough (within 7%). The plan is good. Execution Time: 0.669 ms is fast. No problem here.

Step 3: Simulate a bad plan

Remove the index and re-run:

DROP INDEX idx_orders_user_id;
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 500;

Output:

Seq Scan on orders  (cost=0.00..19853.20 rows=1082 width=24) (actual time=0.012..156.094 rows=1082 loops=1)
  Filter: (user_id = 500)
  Rows Removed by Filter: 998918
Planning Time: 0.069 ms
Execution Time: 156.213 ms

Now the planner chose a Seq Scan — reading all 1 million rows, filtering out 998,918. The execution time jumped to 156 ms — 233 times slower than the indexed plan. The error is obvious: there's no index. The fix is to re-create the index.

Step 4: Use EXPLAIN to compare a join

-- Create a small users table
CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
INSERT INTO users SELECT id, 'user_' || id FROM generate_series(1, 10000) id;

EXPLAIN ANALYZE
SELECT u.name, SUM(o.total) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 500
GROUP BY u.name;

Output (partial):

GroupAggregate  (cost=0.42..1056.98 rows=1 width=40) (actual time=0.154..0.344 rows=1 loops=1)
  ->  Nested Loop  (cost=0.42..1056.98 rows=1053 width=40) (actual time=0.144..0.319 rows=1053 loops=1)
        ->  Index Scan using users_pkey on users u  (cost=0.28..8.29 rows=1 width=22) (actual time=0.018..0.020 rows=1 loops=1)
              Index Cond: (id = 500)
        ->  Bitmap Heap Scan on orders o  (cost=0.14..1042.18 rows=1053 width=18) (actual time=0.124..0.239 rows=1053 loops=1)
              Recheck Cond: (user_id = 500)
              ->  Bitmap Index Scan on idx_orders_user_id  (cost=0.00..0.14 rows=1053 width=0) (actual time=0.118..0.118 rows=1053 loops=1)
                    Index Cond: (user_id = 500)
Planning Time: 0.120 ms
Execution Time: 0.364 ms

The join uses a Nested Loop: for the single user, it looks up their orders via the index. The estimates match actuals well. Execution is fast.

Pro tip: If the planner picks a Nested Loop when the outer table has 100k rows, that's a red flag — each row triggers a lookup, leading to 100k index probes. Other join types like Hash Join would've been better.

Compare options / when to choose what

You have several ways to get plan information, each with its own use case:

When to choose what: - Use plain EXPLAIN to get a fast read of the plan and spot obvious issues (seq scans on big tables). - Use EXPLAIN ANALYZE when you need to confirm actual performance — especially when a query is slow. - Add BUFFERS when you suspect I/O — it shows whether data came from cache (shared hit) or disk (shared read). - Use FORMAT JSON when you want to parse plans programmatically (e.g., in a monitoring script). - Use COSTS OFF is a handy readability tweak but doesn't change the plan.

Alternatives to EXPLAIN: - auto_explain module: logs query plans automatically after a configurable threshold. Great for production — turn it on and catch slow queries as they happen. - pg_stat_statements: tracks query execution statistics — helps identify frequently slow queries, but doesn't show plans. - Visual tools like pgAdmin's graphical explain or pev2 (Postgres Explain Visualizer) render plans as trees — helpful for beginners.

When to use which: For a one-off slow query, EXPLAIN ANALYZE is enough. For ongoing performance issues, set up auto_explain or a monitoring tool.

Troubleshooting & edge cases

1. You can't run EXPLAIN ANALYZE on an INSERT, UPDATE, or DELETE that you don't want to execute.

Solution: Use EXPLAIN (without ANALYZE) to see the modification plan (which nodes are used for scanning), or wrap the statement in a transaction and ROLLBACK:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE total < 0;
ROLLBACK;

2. Slow query but EXPLAIN shows a good plan.

This happens when actual execution differs due to caching. Run EXPLAIN (ANALYZE, BUFFERS) to see if disk reads dominate. If the slow query is in production with cold cache, the plan may be fine — the bottleneck is I/O, not the planner.

3. Estimates are wildly off (rows=100 but actual rows=100000).

Cause: stale statistics. Fix: run ANALYZE on the table (or VACUUM ANALYZE). With extended statistics, you may need CREATE STATISTICS for correlated columns.

4. Planner keeps choosing a Seq Scan even though an index exists.

The planner might think a Seq Scan is cheaper because: - The table is very small. - The condition matches a large percentage of rows (more than ~5-10%). - The index isn't the right one for the condition (e.g., function on column WHERE lower(email) = ... won't use a plain index).

Use SET enable_seqscan = off; to see the planner's alternate plan — but only for analysis, not in production.

5. The Sort node is too expensive.

Solution: Add an index that matches the sort order (e.g., CREATE INDEX ON orders (created_at)) so the planner can use an Index Scan to avoid sorting.

6. Nested Loop with hundreds of loops.

Watch for loops=10000 in the inner node. That indicates a poor join strategy. Check whether the planner has correct statistics on the join columns; ANALYZE often helps.

7. EXPLAIN ANALYZE is slower than your actual query.

This happens because it runs the query (and any side effects). For large UPDATEs, that's expected. Use plain EXPLAIN to check the plan without running.

What you learned & what's next

You now have the essential skill of use EXPLAIN to analyze query plans. Specifically, you learned: - The problem: slow queries often stem from poor plans, not syntax errors. - The mental model: the planner uses cost-based optimization with statistics. - How it works: reading plans inside-out, comparing estimates to actuals. - Hands-on: you ran EXPLAIN, EXPLAIN ANALYZE, and BUFFERS on real data, spotting a missing index and a join plan. - Compare options: you know which EXPLAIN variants to use and when. - Troubleshooting: you can diagnose stale stats, forced Seq Scans, and wrong join types.

Armed with this, you can now approach any slow query methodically. In the next lesson, we'll build on this foundation and explore indexing strategies — how to choose and maintain indexes that make the planner look good. You'll learn about B-tree, GIN, and partial indexes, and how to use EXPLAIN to validate their effectiveness.

Now go run EXPLAIN ANALYZE on your trickiest query and see what it's really doing.