SQL EXPLAIN and Query Planning Made Approachable

What and Why

SQL is declarative: you describe the result, and the database picks how to get it. That “how” is the query plan. Two queries that return the same rows can differ in runtime by four orders of magnitude depending on which plan the optimizer chose. EXPLAIN shows the plan; EXPLAIN ANALYZE runs the query and shows actual timings.

If you cannot read a plan, you tune queries by trial and error. If you can, you spot the missing index, the bad estimate, or the join order that needs a hint, in seconds.

Mental Model

A plan is a tree. Leaves are scans of tables or indexes. Inner nodes are operations: joins, sorts, aggregates, limits. Rows flow upward; each node consumes rows from its children and produces rows for its parent.

Limit (10)
'-- Sort (by created_at desc)
      '-- Hash Join
            |-- Seq Scan on orders
            '-- Hash
                  '-- Index Scan on customers (pk)

The optimizer enumerates possible plans, estimates the cost of each using table statistics, and picks the cheapest. Estimates rely on row counts, column histograms, and index metadata gathered by ANALYZE (Postgres) or equivalent stats jobs in other engines.

Hands-on Example

Set up two tables in Postgres:

CREATE TABLE customers (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  name  TEXT
);

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id),
  total       NUMERIC,
  created_at  TIMESTAMPTZ
);

Insert a million orders and a hundred thousand customers (use generate_series). Then explain a query:

EXPLAIN
SELECT c.email, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= now() - interval '30 days'
GROUP BY c.email
ORDER BY spent DESC
LIMIT 10;

Output (abbreviated):

Limit  (cost=12345..12350 rows=10 width=40)
  ->  Sort  (cost=12345..12360 rows=6000 width=40)
        Sort Key: (sum(o.total)) DESC
        ->  HashAggregate  (cost=11000..11500 rows=6000 width=40)
              Group Key: c.email
              ->  Hash Join  (cost=200..10000 rows=80000 width=20)
                    Hash Cond: (o.customer_id = c.id)
                    ->  Seq Scan on orders o  (cost=0..9000 rows=80000 width=16)
                          Filter: (created_at >= now() - '30 days'::interval)
                    ->  Hash
                          ->  Seq Scan on customers c  (cost=0..150 rows=100000 width=20)

Read bottom-up. The leaves are sequential scans. The Hash Join builds a hash on customers, probes with orders. The HashAggregate groups, the Sort orders, the Limit cuts to ten.

Now run with actual timings:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

You will see lines like actual time=12.3..45.6 rows=78912 loops=1. Compare planner estimates to actuals. If the planner expected 6,000 rows and saw 800,000, statistics are stale or correlated columns are confusing it.

Add a useful index:

CREATE INDEX idx_orders_created_customer
  ON orders (created_at DESC, customer_id);

Re-run EXPLAIN ANALYZE. The Seq Scan on orders should turn into an Index Scan or Bitmap Index Scan, and total runtime should drop sharply.

Common scan and join types:

• Seq Scan reads the whole table. Fine for small tables or queries that touch most rows.
• Index Scan walks the index in order. Best when you need rows in that order or only a few rows.
• Index Only Scan returns columns directly from the index without visiting the table. Great for covering indexes.
• Bitmap Index Scan plus Bitmap Heap Scan builds a bitmap of matching rows then fetches them in physical order. Good for mid-size result sets.
• Nested Loop joins by scanning the inner side once per outer row. Best when the outer side is tiny.
• Hash Join builds a hash on the smaller side. Best for medium-large unsorted joins.
• Merge Join walks both sides in sort order. Best when both inputs are already sorted by the join key.

Common Pitfalls

Trusting EXPLAIN without ANALYZE. Estimates can be wildly wrong; only ANALYZE shows what actually happened. (Wrap writes in a transaction and ROLLBACK if you do not want side effects.)

Adding indexes without measuring. Each index costs write performance and disk space, and many queries already have a fine plan. Profile first, then index.

Ignoring rows= mismatches. A 100x ratio between planned and actual rows is the loudest signal that statistics are off. Run ANALYZE table_name or revisit your sampling settings.

Wrapping indexed columns in functions. WHERE lower(email) = 'a@b.com' cannot use a plain index on email. Create an expression index or normalize at write time.

Using SELECT * and wondering why an index-only scan never kicks in. Listing only the columns you need lets the planner use a covering index.

Practical Tips

In Postgres, EXPLAIN (ANALYZE, BUFFERS, VERBOSE) adds I/O counts and full column lists. Paste the output into a plan visualizer for color-coded trees.

Use pg_stat_statements (Postgres) or the query store (SQL Server) to find which queries are worth tuning. Optimize the top of the list, not the loudest user complaint.

Composite indexes help only if your WHERE matches the leading columns. (a, b) helps WHERE a = ? AND b = ? and WHERE a = ?, but not WHERE b = ? alone.

Beware functions like now() and random() in WHERE: they can prevent caching of plans across executions. Parameterize when possible.

LIMIT plus ORDER BY on an indexed column is one of the biggest wins. The planner can walk the index and stop early.

Wrap-up

EXPLAIN is the database telling you exactly how it will work. Learn to read the tree bottom up, watch for sequential scans on big tables, and compare estimated to actual rows. Index thoughtfully, write queries that let the planner use what you built, and most “the database is slow” tickets become five-minute fixes.