Rules of Indexing in PostgreSQL
summary
Complete walkthrough of PostgreSQL index types with concrete numbers and demos: B‑Tree sort keys cut an example query from 84ms to ~66ms and NULLS FIRST saves ~9ms (~40%); INCLUDE columns turn a 0.15s plan into a 0.05s index‑only scan; a filtered index reduces work by one third and shrinks index size; hash is smaller and faster for single‑column equality (0.047s vs 0.06s); BRIN on sequential data drops a 21MB index to ~48KB and cuts time 144ms→109ms; GIN trigram hits 8–17ms; bloom halves index size; vector: HNSW build 35s/293MB vs tree‑like scan 2s/12MB and query 107ms→57ms, returning 25k vs 997 candidates.
AI-written summary · about
chapters (13)
- 0:00Welcome
- 1:25PostgreSQL index types
- 2:48B-tree default choice
- 5:14Optimizing B-tree sort
- 8:49Including columns in B-tree
- 14:36Filtered indexes
- 17:10Function-based indexes
- 17:53Hash indexes trade-offs
- 21:29GIN for text/JSON
- 25:39BRIN for sequential data
- 28:29Bloom indexes
- 31:06Vector indexes for AI
- 36:07Summary & Q&A
related talks
46mA Deep Dive Into PostgreSQL Indexing
45mA Deep Dive into PostgreSQL Indexing
29mPostgres Index types and where to find them
52mA Deep Dive into PostgreSQL Indexing
01h 00mFlexible Indexing With Postgres
49mFlexible Indexing with Postgres
47mThe Whole Truth About Indexes in PostgreSQL
27mDropping Into B-Trees