A Deep Dive into PostgreSQL Indexing
summary
Explains when an index actually speeds queries and when it hurts, shows using pg_stat_statements to find candidates, and contrasts index types with concrete tradeoffs: default B‑Tree for ranges and equality, Hash for equality only, BRIN for time‑range blocks (example: full index 214 MB vs partial index 240 KB), GIN/GiST for JSON/arrays and spatial data, and index‑only scans when the index contains all needed columns. Covers partial index benefits, index-only scan behavior, that partitioned tables get per‑partition index files, and warns about the concurrent CREATE INDEX bug in 14.0–14.3 (fixed 14.4).
AI-written summary · about
chapters (19)
- 0:00Introduction to PostgreSQL indexing
- 10:13Heap vs index concept
- 14:25Concurrent index creation
- 16:31Expression indexes explained
- 20:29Partial indexes and their use
- 23:44B-tree indexes deep dive
- 27:18Index-only scans
- 29:50Hash indexes overview
- 32:18BRIN indexes for ranges
- 35:28GIN indexes for documents
- 38:43Index types summary
- 40:12Duplicate index detection
- 41:46Index size and performance
- 45:01Monitoring indexes
- 45:44Partitioned table indexes
- 47:15Unique indexes and constraints
- 48:44Index file locations
- 50:11Time series and BRIN
- 50:52Q&A session
related talks
46mA Deep Dive Into PostgreSQL Indexing
45mA Deep Dive into PostgreSQL Indexing
53mAdvanced Indexing Techniques in PostgreSQL
29mPostgres Index types and where to find them
47mThe Whole Truth About Indexes in PostgreSQL
49mFlexible Indexing with Postgres
01h 00mFlexible Indexing With Postgres
37mRules of Indexing in PostgreSQL