SQL Query Planner Visualized: EXPLAIN, Index Scans & Join Strategies

SQL describes the result you want, not the exact procedure for producing it. The database planner considers alternative scans, join orders, and join algorithms, estimates their work, and chooses a plan before execution.

The visualizer above is an educational approximation, not PostgreSQL's planner. Change table size, selectivity, and indexes to see why a plausible plan can shift from sequential scans and a hash join to index scans and a nested loop.

The example query

SELECT orders.*
FROM orders
JOIN users ON users.id = orders.user_id
WHERE users.country = 'CZ';

The database might scan users, estimate how many rows match country = 'CZ', and then choose how to find each user's orders. The cheapest approach depends on data distribution, table size, indexes, cached pages, and available memory—not only SQL syntax.

Statistics, estimates, and selectivity

Selectivity describes how narrowly a predicate filters rows. If 0.2% of users match, an index may avoid reading most table pages. If 80% match, walking the index and then fetching scattered heap pages can cost more than one sequential pass.

The planner does not inspect every row before planning. It uses statistics such as row counts, distinct values, common values, histograms, and null fractions. Stale or insufficient statistics can produce bad row estimates, which then distort join and scan choices.

Sequential, index, and bitmap scans

A sequential scan reads the table's pages and tests rows. It is not inherently bad: for a small table or a query returning much of a large table, sequential access can be efficient.

An index scan navigates an index to matching entries and fetches table rows. It excels when the predicate is selective and the index order or coverage helps.

A bitmap scan is a useful middle ground in PostgreSQL. Index matches first build a bitmap of heap pages, then the executor visits those pages in a more organized order. It can outperform individual index lookups when more than a tiny fraction matches.

An index may exist but remain unused because the predicate returns too many rows, statistics predict low selectivity, the expression does not match the index, type conversion prevents a useful condition, or a different plan is simply estimated cheaper.

Nested loop join

A nested loop takes rows from an outer input and probes the inner input for each one. With few outer rows and an index on the inner join key, this can be excellent. Without that index—or with far more outer rows than estimated—it can repeat expensive work many times.

Hash join

A hash join builds an in-memory hash table from one input and probes it with the other. It often suits equality joins over larger unsorted inputs. If the hash exceeds available working memory, batching and disk I/O can change performance.

Merge join

A merge join advances through two inputs ordered by the join keys. It can be attractive when indexes already provide the order or when sorted inputs will be reused. If sorting both large sides is required only for this join, the sort cost may make another strategy cheaper.

Cost is not measured time

PostgreSQL plan output contains values such as cost=0.43..182.17. These are planner cost units, based on configurable relative estimates for page access and CPU work. They are not milliseconds.

Nested Loop  (cost=0.72..182.17 rows=120 width=64)
  -> Index Scan using users_country_idx on users
       Index Cond: (country = 'CZ')
  -> Index Scan using orders_user_id_idx on orders
       Index Cond: (user_id = users.id)

The first cost is startup cost; the second is estimated total cost if the node runs to completion. Costs are most useful for comparing alternatives within the same planner configuration.

Reading EXPLAIN ANALYZE

EXPLAIN plans without running the statement. EXPLAIN ANALYZE executes it and adds measured timing, row counts, and loop counts. Be careful with writes: wrapping a modifying statement in EXPLAIN ANALYZE still performs it unless you explicitly use a transaction and roll it back.

EXPLAIN (ANALYZE, BUFFERS)
SELECT orders.*
FROM orders
JOIN users ON users.id = orders.user_id
WHERE users.country = 'CZ';

Read from the deepest nodes upward. Compare estimated rows with actual rows × loops. A large mismatch near the bottom often cascades into the wrong join choice above. Buffer information helps distinguish CPU work from page reads, while repeated measurements and production-shaped data prevent conclusions from one warm-cache run.

A practical tuning loop

1. Capture the slow query and representative parameters.
2. Run EXPLAIN ANALYZE safely on production-shaped data.
3. Find the first large estimate error or unexpectedly repeated work.
4. Check statistics and whether predicates match useful indexes.
5. Change one thing, measure again, and account for write and storage cost.

Continue with Database Indexes and B-Trees for the storage structure underneath index access, SQL Transactions for concurrent correctness, and Big O Notation for the asymptotic vocabulary—while remembering that real query performance also depends on constants, I/O, caching, and data distribution.

Key takeaways

• SQL specifies results; the planner selects an execution strategy.
• Statistics and cardinality estimates influence every downstream choice.
• Sequential scans can be correct even when an index exists.
• Nested loops, hash joins, and merge joins suit different input shapes.
• Planner cost is not milliseconds; actual time comes from execution.
• Diagnose estimate errors before forcing a plan.