2.3Query Execution Plans

The statistics from the previous sections were the material the planner uses to build an execution plan. This section is about reading the resulting plan itself. EXPLAIN sits at the core of the Gold exam—accurately reading which join strategy, which scan type, and in what order a query executes is directly tied to diagnosing performance problems.

2.3.1Reading EXPLAIN and EXPLAIN ANALYZE

• EXPLAIN shows the plan the planner chose, as a tree, without actually running the query. Each node carries cost=startup cost..total cost (the planner's estimate, in abstract cost units), rows=estimated row count, and width=average row width—estimates only, with no actual measurements.
• EXPLAIN ANALYZE actually runs the query and additionally reports each node's actual time=start..end (in milliseconds), actual rows=measured row count, and loops=how many times this node executed. Its greatest value is that it lets you compare estimate against reality—a large gap between rows (estimated) and actual rows (measured) hints that statistics are stale or inaccurate. Running it against DML with side effects (INSERT/UPDATE/DELETE) actually changes data, so wrap it in a transaction and ROLLBACK, or restrict its use to read-only queries, when you only want to inspect the plan.
• The plan tree executes from the innermost (most deeply indented) node outward, with results flowing up to enclosing nodes. Leaf nodes like Seq Scan fetch actual data, intermediate nodes like Hash Join combine them, and top-level nodes like Limit or Sort do final shaping—the basic reading order is bottom to top.

2.3.2Join strategies, scan types, window functions, and parallel query

• Join strategies: Nested Loop (scans the inner side once per outer row; favorable for small tables, or when the inner side has an effective index); Hash Join (builds an in-memory hash table from one side—usually the smaller—and scans the other once to match; favorable for equi-joins with large volumes); Merge Join (both sides are sorted by the join key, then merged in one pass; chosen when both are already sorted, or when paying the sort cost is still worthwhile).
• Scan types: Seq Scan (reads the whole table sequentially from the start; the default when many rows match or no index helps); Index Scan (looks up matching rows via an index, accessing the heap for each one; favorable when few rows match); Bitmap Scan (Bitmap Index Scan builds a bitmap of matching blocks from the index, then Bitmap Heap Scan reads just those blocks in one pass; chosen when the matching row count is intermediate—too many for Index Scan to stay efficient, but not enough to justify Seq Scan—reducing random I/O by batching heap access at the block level).
• Window functions use OVER (PARTITION BY ... ORDER BY ...) to attach a computed value to each row without collapsing rows via aggregation (ROW_NUMBER(), RANK(), running sums, etc.). In the plan they appear as a dedicated WindowAgg node, which typically requires the rows to be sorted by the PARTITION BY/ORDER BY columns beforehand.
• Parallel query is the mechanism for splitting large Seq Scans or aggregations across multiple background workers. It is governed by max_worker_processes (the total ceiling on background workers usable server-wide) and max_parallel_workers_per_gather (the ceiling on parallel workers a single Gather node can use), and appears in the plan as a Gather node (which collects each worker's results) alongside Parallel Seq Scan.

Let us practice reading real EXPLAIN ANALYZE output. Suppose a typical query joins orders (millions of rows) to customers (tens of thousands) on customer_id, filtered by WHERE customers.region = 'tokyo'. If the top-level line reads Hash Join (cost=1250.00..48000.00 rows=15000 width=64) (actual time=12.500..350.200 rows=14800 loops=1), the planner chose a Hash Join, and the estimated 15000 rows nearly matches the measured 14800—statistics look healthy. If the child nodes below it are Hash (cost=800.00..800.00 rows=5000 width=32) (actual time=8.000..8.000 rows=4820 loops=1) → Seq Scan on customers (cost=0.00..800.00 rows=5000 width=32) (actual time=0.010..6.500 rows=4820 loops=1) Filter: (region = 'tokyo'::text), you can read that the smaller customers side is filtered first, then hashed (the Hash node), while the other child, Seq Scan on orders (cost=0.00..42000.00 rows=3000000 width=40) (actual time=0.020..180.000 rows=3000000 loops=1), scans all of orders to match against it. If instead rows=15000 (estimated) diverges from actual rows=140000 (measured) by an order of magnitude or more, suspect that statistics on region are stale, or that there is an unregistered correlation between customers.region and some column on the orders side—consider re-running ANALYZE customers; or adding extended statistics. Also, for the same query, if an index idx_customers_region exists on region and the matching rows are a tiny fraction of the table (say, under 1%), the planner is more likely to choose Index Scan using idx_customers_region on customers instead of Seq Scan on customers—or, for an intermediate row count, Bitmap Heap Scan on customers plus Bitmap Index Scan on idx_customers_region. The key point in reading a plan is that the scan strategy changes with the estimated row count, even for the same table and the same condition. For analytical queries involving aggregation (e.g., computing cumulative sales by region), a WindowAgg node appears, and you check whether the plan includes a preceding Sort (on the PARTITION BY/ORDER BY columns). When aggregating across the entire large orders table, seeing a Parallel Seq Scan on orders beneath a Gather node means parallel query kicked in and work was split across multiple workers, with max_parallel_workers_per_gather acting as the ceiling on how many workers that Gather node actually used.

2.3.3Section summary

• EXPLAIN = estimates only; EXPLAIN ANALYZE = executes and also reports actual measurements (mind DML side effects). Read plans from the bottom (inner) upward
• Join strategy (Nested Loop/Hash Join/Merge Join) and scan type (Seq/Index/Bitmap) are independent axes. WindowAgg = window functions; Gather = where parallel query results converge (controlled by max_worker_processes/max_parallel_workers_per_gather)