A Deep Dive Into PostgreSQL Indexing
summary
Shows concrete numbers and techniques: a point query drops from about 26 seconds to ~2.4 ms once an index exists, btree is the default, hash supports only equality, BRIN wins when physical locality correlates with values, and GIN handles composite/full‑text data. Demonstrations include per‑table index files and a BRIN index that cuts index size from megabytes to kilobytes. Covers index-only scan speedups, when to use an expression index or a partial index, and the tradeoff of concurrent index creation versus locking and build time.
AI-written summary · about
chapters (28)
- 0:00Introduction
- 0:17Ibrar Ahmad Introduction
- 0:36Index vs Book
- 3:39PostgreSQL Architecture
- 5:27Example
- 7:08Pages
- 10:07Why Index
- 12:04Index File
- 14:12Expression Index
- 19:32Special Index
- 20:21Partial Index
- 23:24Index Methods
- 23:57Btr Index
- 25:26Btree
- 28:23Index scan
- 29:55Index only scan
- 31:48Order doesnt matter
- 33:20Hash Index
- 35:16Hash Index Example
- 35:46Block Range Index
- 37:12Faster Execution
- 37:37Physical Storage
- 38:50Log
- 40:41Gen Index Example
- 41:05Index Framework
- 41:52B3 Hash
- 43:02Never been used
- 44:15Hints
related talks
45mA Deep Dive into PostgreSQL Indexing
53mAdvanced Indexing Techniques in PostgreSQL
52mA Deep Dive into PostgreSQL Indexing
37mRules of Indexing in PostgreSQL
01h 00mFlexible Indexing With Postgres
29mPostgres Index types and where to find them
37mRules of Indexing in PostgreSQL
49mFlexible Indexing with Postgres