Rules of Indexing in PostgreSQL
summary
Covers concrete index choices and measured wins: using B-Tree INCLUDE columns turns a 0.15s nested-loop into a 0.05s index-only scan; adding an ORDER BY column removes an 18ms sort; specifying NULLS FIRST saves ~9ms (40% faster); a partial index cuts work by a third and drops removed rows from 698 to 4; hash beats B-Tree in one example (0.47s vs 0.60s) with much smaller size; GIN trigram hits 8ms on full-text while HNSW vector queries run ~107ms versus a tree-style scan index at 57ms (build 35s vs 2s, 293MB vs 12MB).
AI-written summary · about
chapters (13)
- 0:00PostgreSQL indexing rules
- 1:46PostgreSQL index types
- 3:09B-tree indexes default
- 4:53B-tree sort and NULLs
- 8:49B-tree included columns
- 14:36Filtered indexes
- 17:10Function-based indexes
- 17:53Hash indexes
- 21:29GIN indexes
- 25:39BRIN indexes
- 28:29Bloom indexes
- 31:06Vector indexes
- 36:07Summary and Q&A
related talks
45mA Deep Dive into PostgreSQL Indexing
46mA Deep Dive Into PostgreSQL Indexing
29mPostgres Index types and where to find them
52mA Deep Dive into PostgreSQL Indexing
49mFlexible Indexing with Postgres
01h 00mFlexible Indexing With Postgres
48mB-tree Indexes in Databases Illustrated by IndexedDB and PostgreSQL
33mHow to Make Your Backend Roar