A single slow query can cascade through your entire application. It holds connections, stalls other transactions, and drives up your cloud bill. When that moment arrives, EXPLAIN ANALYZE is the single most important diagnostic tool you have.

What Is EXPLAIN ANALYZE?

PostgreSQL ships with two related commands:

EXPLAIN displays the query plan the planner intends to use. It shows estimated cost, expected row counts, and chosen access methods without running the query.

EXPLAIN ANALYZE does everything EXPLAIN does, then executes the query for real. The output includes actual runtimes, actual row counts, and loop counts for every node.

Safety note

• SELECT queries are safe (results are discarded).
• INSERT, UPDATE, DELETE will modify data unless wrapped in a transaction:

BEGIN;
EXPLAIN ANALYZE
  UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

Useful format options

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

• BUFFERS adds I/O behavior (shared/local buffer hits and reads).
• FORMAT JSON is useful for feeding plans into visualization tools.

How to Read a Query Plan

A query plan is a tree. Execution starts at the leaf nodes (deepest indentation) and flows upward to the root.

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

Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=72)
  (actual time=0.027..0.029 rows=1 loops=1)
  Index Cond: (email = 'alice@example.com'::text)
Planning Time: 0.085 ms
Execution Time: 0.052 ms

When actual rows diverge significantly from estimated rows, run ANALYZE <table> to refresh statistics.

Common Node Types

Scan nodes (data access)

• Seq Scan: Reads every row. Fine for small tables. Red flag on large tables with selective filters.
• Index Scan: Uses B-tree index, then fetches heap tuple. Fast for selective queries.
• Index Only Scan: Index contains all needed columns. No heap fetch. Fastest scan type.
• Bitmap Index Scan + Bitmap Heap Scan: Builds bitmap of matching pages from index. Common for moderate selectivity.

Join nodes

• Nested Loop: For each outer row, scans inner set. Good when outer is small and inner has an index. Bad when both are large.
• Hash Join: Builds hash table from smaller relation, probes with larger. Good for equi-joins.
• Merge Join: Both inputs must be sorted on join key. Efficient for large pre-sorted sets.

Other operations

• Sort: Watch for external merge (sort spilled to disk).
• HashAggregate / GroupAggregate: Used for GROUP BY.
• Limit: Stops after N rows.

Real-World Examples

Example 1: Full table scan

orders table with 5 million rows:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 8821;

Seq Scan on orders  (cost=0.00..125432.00 rows=47 width=96)
  (actual time=892.113..1543.207 rows=52 loops=1)
  Filter: (customer_id = 8821)
  Rows Removed by Filter: 4999948
Planning Time: 0.091 ms
Execution Time: 1543.289 ms

PostgreSQL scanned all 5M rows to find 52. Fix:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

After:

Index Scan using idx_orders_customer_id on orders  (cost=0.43..196.12 rows=47 width=96)
  (actual time=0.031..0.187 rows=52 loops=1)
  Index Cond: (customer_id = 8821)
Execution Time: 0.214 ms

From 1,543 ms to 0.2 ms. Over 7,000x improvement.

Example 2: Bad join strategy

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, li.product_id, li.quantity
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.status = 'pending'
  AND o.created_at > '2026-01-01';

Nested Loop  (actual time=0.045..4231.882 rows=2287 loops=1)
  ->  Seq Scan on orders o  (actual time=0.031..3412.009 rows=2287 loops=1)
        Filter: ((status = 'pending') AND (created_at > '2026-01-01'))
        Rows Removed by Filter: 4997713
  ->  Index Scan using idx_line_items_order_id on line_items li
        (actual time=0.008..0.011 rows=3 loops=2287)
Execution Time: 4232.104 ms

The bottleneck is the Seq Scan on orders. Fix with a composite index:

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

From 4.2 seconds to 19 milliseconds. 224x improvement.

Example 3: Row estimate mismatch

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.plan_type = 'enterprise'
GROUP BY u.name;

Planner estimated 55 enterprise users and 109,800 orders. Reality: 8 users, 847 orders. The Hash Join scanned all 5M orders unnecessarily.

Fix:

ANALYZE users;
ANALYZE orders;

After refreshing statistics, PostgreSQL chose a Nested Loop with index scans. Execution time dropped to under 5 ms.

Example 4: Sort without supporting index

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events ORDER BY created_at DESC LIMIT 100;

Even though we only need 100 rows, PostgreSQL scans and sorts the entire 10M-row table. Fix:

CREATE INDEX idx_events_created_at_desc ON events (created_at DESC);

Execution time drops to under 1 ms.

How to Spot Performance Problems

After reviewing hundreds of query plans, these are the patterns to watch for:

1. Seq Scan on large tables with selective filters

If Rows Removed by Filter is orders of magnitude larger than rows returned, you need an index.

2. Actual rows far from estimated rows

Mismatches above 10x mean the planner may choose the wrong join strategy. Run ANALYZE or use CREATE STATISTICS for correlated columns.

3. Nested Loop with high loop counts and no inner index

Thousands of iterations against an inner Seq Scan is a combinatorial explosion. Add an index on the inner table's join column.

4. Sort spilling to disk

Sort Method: external merge Disk: ... means the sort exceeded work_mem. Increase work_mem for the session or add a supporting index.

5. Excessive buffer reads

With BUFFERS enabled, high read= counts (vs hit=) indicate cold cache or excessive I/O.

Optimization Checklist

When investigating a slow query:

• [ ] Run with EXPLAIN (ANALYZE, BUFFERS)
• [ ] Check for Seq Scans on large tables
• [ ] Compare estimated vs. actual rows (>10x mismatch = stale stats)
• [ ] Look at loop counts in Nested Loops
• [ ] Check sort method (disk = problem)
• [ ] Review join order
• [ ] Use a visual tool for complex plans (15+ nodes)
• [ ] After fixing, run EXPLAIN ANALYZE again to confirm

Beyond EXPLAIN ANALYZE

For ongoing performance monitoring:

• pg_stat_statements: Tracks cumulative query stats across all executions.
• auto_explain: Logs plans for queries exceeding a time threshold.
• Regular ANALYZE runs: Keep autovacuum configured to refresh table statistics.

I'm building QueryDeck, a native macOS database client with visual EXPLAIN ANALYZE. Currently in early access.