9.2 Query Execution Plans & Index Optimization

9.2 Query Execution Plans & Index Optimization

[!IMPORTANT] The Cost-Based Optimizer (CBO): PostgreSQL does not execute SQL statements directly. Instead, when a query is submitted, the parser parses the SQL, the rewriter applies view definitions and rewrite rules, and the planner/optimizer explores mathematically viable execution paths. The optimizer calculates estimated disk I/O and CPU costs for each candidate plan and selects the plan with the lowest estimated total cost. EXPLAIN is the window into the optimizer's calculations.

Optimizing database performance requires understanding how PostgreSQL plans to execute a query, verifying whether actual execution matches statistical estimates, and selecting the optimal index type for the underlying data structures.

---

The EXPLAIN Command: Syntax and Options

The EXPLAIN statement exposes the execution plan that PostgreSQL's planner creates for a given SQL query.

EXPLAIN [ ( option [, ...] ) ] statement;

Essential Options in Modern PostgreSQL

• ANALYZE (boolean): Executes the query for real! Without ANALYZE, EXPLAIN only generates estimated costs based on catalog statistics. With ANALYZE, PostgreSQL actually executes the statement, measures the real wall-clock elapsed time spent in each plan node, counts the actual number of rows returned, and displays these runtime statistics alongside the estimates.

  [!WARNING] Because EXPLAIN ANALYZE actually executes the statement, issuing EXPLAIN ANALYZE DELETE FROM orders; will physically delete all records! Always execute data modification statements inside a transaction block with ROLLBACK when profiling (BEGIN; EXPLAIN ANALYZE DELETE ...; ROLLBACK;).

• BUFFERS (boolean): Requires ANALYZE. Displays buffer usage metrics across plan nodes: shared hit (blocks read directly from shared_buffers), read (blocks read from operating system cache or physical disk), dirtied (blocks modified by the query), and written (blocks flushed to disk). Crucial for determining whether a query is I/O-bound or CPU-bound.
• VERBOSE (boolean): Displays additional structural details, including output column projections for each node, schema qualification, and internal work variable aliases.
• COSTS (boolean): Includes estimated startup cost, total cost, row count, and average row width. Defaults to TRUE.
• TIMING (boolean): Measures actual startup time and total elapsed time on each node. Requires ANALYZE; defaults to TRUE. Can be disabled (TIMING FALSE) on platforms with high clock overhead.
• FORMAT { TEXT | JSON | XML | YAML }: Formats the explain plan output. Default is TEXT. Programmatic APM monitoring tools typically consume JSON.

-- Production profiling standard: detailed timing, actual rows, and I/O buffer consumption
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT o.order_id, c.customer_name, o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01' AND o.total_amount > 500.00;

---

Anatomy of an Execution Plan Node

Consider the following output line from an execution plan:

Seq Scan on orders o  (cost=0.00..15240.00 rows=28500 width=24) (actual time=0.042..14.210 rows=27980 loops=1)
  Buffers: shared hit=4210 read=1030
  Filter: ((order_date >= '2026-01-01'::date) AND (total_amount > 500.00))
  Rows Removed by Filter: 172020

Estimated Metrics vs. Actual Metrics

1. cost=0.00..15240.00:
   • Startup Cost (0.00): The estimated work required before the node can output its very first row. For a sequential scan, startup cost is 0.00 because rows are evaluated immediately upon reading the first page. For a sort or hash node, startup cost is high because all input rows must be consumed before any output row can be emitted.
   • Total Cost (15240.00): The estimated total cost to run the node to completion, assuming all matching rows are retrieved. PostgreSQL measures cost in arbitrary units calibrated against seq_page_cost (default 1.0, representing one sequential 8KB page fetch from disk).
2. rows=28500: The optimizer's mathematical estimate of how many rows this node will produce, calculated using statistics in pg_statistic.
3. width=24: The estimated average size in bytes of each row returned by this node.
4. actual time=0.042..14.210: Measured in milliseconds.
   • First number (0.042 ms): Wall-clock time to produce the first output row.
   • Second number (14.210 ms): Total wall-clock time to finish processing all rows for this node.
5. rows=27980: The actual number of rows emitted by this node. Comparing estimated rows (28,500) against actual rows (27,980) shows near-perfect planner accuracy. If estimated rows were 5 and actual rows were 500,000, statistics are stale and ANALYZE must be executed.
6. loops=1: The number of times this node was executed. In nested loops, an inner node may execute thousands of times; in such cases, actual time and rows represent averages per loop, and must be multiplied by loops to obtain total values.

---

Table Access & Scan Methods

PostgreSQL employs four primary physical access path nodes to scan table relations:

+--------------------------------------------------------------------------+
|                       Scan Strategy Comparison                           |
+--------------------------------------------------------------------------+
|  1. Sequential Scan (Seq Scan)                                           |
|     Reads every 8KB heap page from block 0 to high-water mark.           |
|     Optimal for high selectivity (> 10-20% of table rows) or small tables|
|                                                                          |
|  2. Index Scan                                                           |
|     Navigates B-Tree to find tuple pointers (TID: page + offset).         |
|     Visits heap pages for every match to retrieve columns and check MVCC.|
|     Optimal for high selectivity (< 1-5% of table rows). Random I/O.     |
|                                                                          |
|  3. Index Only Scan                                                      |
|     Retrieves all requested columns directly from index leaf pages.      |
|     Checks Visibility Map (_vm) all-visible bits to bypass heap visits.  |
|     Fastest possible read access when all-visible bits are set!          |
|                                                                          |
|  4. Bitmap Index Scan + Bitmap Heap Scan                                 |
|     Phase 1: Index builds memory bitmap of matching page block numbers.  |
|     Phase 2: Heap scan reads pages in physical sequential block order.   |
|     Converts random I/O to sequential I/O; enables BitmapAnd / BitmapOr. |
+--------------------------------------------------------------------------+

1. Sequential Scan (Seq Scan)

• Scans every 8KB disk page of the table in physical storage order.
• Governed cost formula: (disk_pages * seq_page_cost) + (total_tuples * cpu_tuple_cost) + (scanned_tuples * cpu_operator_cost).
• Highly efficient when retrieving a large portion of the table (> 15–20% of rows), or when the table is small enough to fit inside a single disk page (where an index traversal would add unnecessary overhead).

2. Index Scan (Index Scan)

• Traverses the index structure (such as a B-Tree) to locate matching index keys, which contain physical tuple pointers (ItemPointer / TID: block number and page offset).
• For every matching index entry, the engine visits the underlying table heap page on disk to retrieve non-indexed columns and check MVCC row visibility (xmin/xmax).
• Because heap pages are accessed randomly based on key order rather than physical storage order, Index Scans incur significant random I/O (calibrated by random_page_cost, default 4.0 on HDD, typically tuned to 1.1–1.5 on SSD/NVMe).

3. Index Only Scan (Index Only Scan)

• Occurs when all columns requested in the query (SELECT, WHERE, ORDER BY) are present directly within the index itself (e.g., covering indexes created with INCLUDE).
• The Visibility Map (_vm) Requirement: Because indexes in PostgreSQL do not store MVCC transaction visibility headers (xmin/xmax), an index cannot definitively prove on its own whether a tuple is visible to the current transaction snapshot. To avoid visiting the heap for every row, PostgreSQL checks the table's Visibility Map:
  • If the heap page's all-visible bit is set to 1, the engine guarantees that all rows on that page are visible to all active transactions. The engine reads the data purely from the index leaf page, performing zero heap visits!
  • If the all-visible bit is 0, the engine must perform a Heap Fetch to verify visibility. In EXPLAIN ANALYZE, this displays as Heap Fetches: N. High heap fetches indicate the table needs VACUUM to update its Visibility Map.

4. Bitmap Index Scan & Bitmap Heap Scan

• When a query retrieves a moderate percentage of rows (e.g., 5–15%), an Index Scan would cause too much random I/O, while a Sequential Scan would read too many irrelevant blocks. PostgreSQL solves this with a two-step bitmap strategy:
  1. Bitmap Index Scan: Scans the index and constructs a compact bitmap in memory (work_mem). The bitmap marks the physical page numbers (and optionally offsets) that contain matching rows.
  2. Bitmap Heap Scan: Sorts the page numbers into physical disk block order and reads only the flagged heap pages sequentially. This converts erratic random I/O into smooth sequential I/O.
• Multiple Index Combination: The planner can execute multiple Bitmap Index Scans across different indexes simultaneously, combining their in-memory bitmaps using BitmapAnd (intersection for AND conditions) or BitmapOr (union for OR conditions) before visiting the table heap.

---

Join Strategy Nodes

When combining data from two relations, PostgreSQL evaluates three core join algorithms:

---

Specialized Index Types in PostgreSQL

PostgreSQL supports multiple indexing architectures beyond standard B-Trees. Choosing the correct index type drastically improves query efficiency and storage footprint.

-- 1. Standard B-Tree (Default)
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- 2. GIN for JSONB and Array search
CREATE INDEX idx_items_tags ON inventory USING gin (tags);
CREATE INDEX idx_payload_gin ON event_logs USING gin (payload jsonb_path_ops);

-- 3. GiST for Spatial Geometric coordinates
CREATE INDEX idx_locations_geo ON properties USING gist (location_point);

-- 4. BRIN for monotonically sequential append-only time-series data
CREATE INDEX idx_audit_log_created ON audit_log USING brin (created_at) WITH (pages_per_range = 128);

1. B-Tree (B+ Tree)

• Default type: Used when USING is omitted.
• Operators: Handles scalar comparison operators: <, <=, =, >=, >, and BETWEEN.
• Capabilities: Supports multi-column composite keys, prefix pattern matches (LIKE 'abc%'), and natural index sorting (ASC, DESC, NULLS FIRST/LAST).

2. GIN (Generalized Inverted Index)

• Architecture: An inverted index where an index entry maps a single component element (key) to an array of tuple pointers containing that element.
• Use Cases: Composite data types containing multiple values per row: Arrays, JSONB documents (using the containment operator @>), and Full-Text Search (tsvector @@ tsquery).
• Tradeoff: Fast search lookups, but slower build times and write overhead during INSERT/UPDATE operations.

3. GiST (Generalized Search Tree)

• Architecture: A balanced tree template supporting arbitrary indexing schemes based on lossy hierarchical bounding boxes (R-Trees).
• Use Cases: Spatial and geometric data (PostGIS points, polygons, geometric overlap &&), range types (int4range, daterange), and nearest-neighbor distance searches (<->).

4. BRIN (Block Range Index)

• Architecture: Does not store pointers to individual rows. Instead, it divides the table into contiguous physical block ranges (default 128 pages = 1MB) and records only the minimum and maximum values found within each block range.
• Use Cases: Massive multi-gigabyte or terabyte tables where data values are physically correlated with storage order (e.g., append-only audit logs, time-series tables with auto-incrementing IDs or ascending timestamps).
• Advantages: Extremely compact physical footprint—often less than 1% of the size of an equivalent B-Tree index (e.g., 200KB instead of 2GB)—while allowing the optimizer to skip entire block ranges that do not match the query predicate.

---

Exam Tips and Common Pitfalls

• Exam Trap: EXPLAIN vs. EXPLAIN ANALYZE: EXPLAIN alone only estimates query cost based on statistics without running the query. EXPLAIN ANALYZE actually executes the query, returning true elapsed runtimes and row counts.
• Exam Trap: What Requires ANALYZE?: The BUFFERS option cannot be run alone in standard syntax; it requires ANALYZE to measure real buffer cache hits and disk reads during execution (EXPLAIN (ANALYZE, BUFFERS)).
• Exam Trap: Heap Fetches in Index Only Scans: If an exam question asks why an Index Only Scan is visiting table heap pages (Heap Fetches > 0), the answer is always that the table's Visibility Map (_vm) does not have all-visible bits set for those pages, requiring vacuuming.
• Exam Trap: BRIN Prerequisites: BRIN indexes are only effective when physical storage order on disk strongly correlates with the indexed column values (e.g. append-only timestamps). If data is inserted in random order, min/max ranges overlap across all blocks, rendering BRIN useless.