Module 3 · Query execution

Episode 20 Access Paths

9 min · SQL · Postgres

Title card of Episode 20: Access Paths

▶ This video is coming to YouTube soon.

Why does Postgres use an index for 2 rows, a bitmap heap scan for 833, and a full sequential scan for 49,352, all on the same column? This episode animates each access path, counts random and sequential page reads at the real table's size, shows how physical order (pg_stats.correlation) changes everything, and builds zone maps, then proves it all with EXPLAIN and a BRIN index in Postgres.

Chapters

  1. 0:00Intro
  2. 0:12One column, three plans
  3. 0:37Seq scan: every page, in order
  4. 1:22Index scan: one jump per row
  5. 2:07Bitmap scan: sort the jumps first
  6. 3:02The code: a bitmap heap scan
  7. 3:32Selectivity picks the path
  8. 4:28Correlation: when index order is file order
  9. 5:10Zone maps: skip whole block ranges
  10. 6:04Proof: one column, three plans
  11. 6:55Proof: correlation
  12. 7:15Proof: zone maps are BRIN
  13. 8:02Trade-offs
  14. 8:39Recap
  15. 9:08Next: External Merge Sort