Part 162: Index Access Paths: Seek, Scan, Bitmap & Index-Only Scan

Decoding how the query optimizer traverses your B-Trees to retrieve rows.

1. The Spectrum of Data Access Paths

From fastest to slowest:

1. Index-Only Scan (Covering): Reads data entirely from the index leaf pages. Never touches the table heap/clustered data!
2. Index Seek (Index Scan in Postgres): Traverses root to leaf node to find the exact starting position, then scans a narrow range.
3. Bitmap Index Scan: Scans index leaves to build a bitmap of page addresses, sorts addresses sequentially, and batches reads from the table heap (prevents random I/O).
4. Index Scan (Full Index Scan): Reads every leaf page of the index from beginning to end.
5. Seq Scan / Table Scan: Reads every data page of the raw table from disk.

2. Execution Plan Signatures

-- 1. Index-Only Scan (Fastest):
EXPLAIN SELECT user_id, status FROM users WHERE status = 'active';
-- Output: Index Only Scan using idx_users_status_inc on users

-- 2. Index Scan + Table Lookup:
EXPLAIN SELECT user_id, full_name, bio FROM users WHERE user_id = 42;
-- Output: Index Scan using users_pkey on users

-- 3. Bitmap Index Scan (PostgreSQL):
EXPLAIN SELECT * FROM orders WHERE customer_id = 999;
-- Output: Bitmap Heap Scan on orders
--           -> Bitmap Index Scan on idx_orders_customer

3. The Tipping Point: Why Did the Optimizer Ignore My Index?

If your query matches more than 5% to 20% of total table rows: - Random I/O lookups to the heap cost more than sequentially scanning the entire table! - The optimizer deliberately chooses a Sequential Table Scan because sequential disk throughput is much faster than random index lookups.

Key Takeaways

• Use covering indexes (CREATE INDEX ... INCLUDE (...)) to achieve zero-heap Index-Only Scans on mission-critical queries.

In Part 163, we explore Query Optimizer Statistics, Histograms & Cardinality Errors!