A Deep Dive into PostgreSQL Indexing
summary
Shows concrete results: adding a B‑tree on id drops a 26,162 ms sequential lookup to ~2.56 ms; create index concurrently doubles build time (12s → 23s) but avoids locks; expression indexes (eg lower(name)) make previously unused indexes become used; partial index shrinks 214 MB to ~240 KB for a hot range; discusses pg_stat_statment, ctid/index‑only scans, and when BRIN, GIN, hash or GiST actually help versus wasting space.
AI-written summary · about
chapters (12)
- 0:00Start
- 6:01PostgreSQL Tables (Heap)
- 7:50Sequential Scan
- 12:57Creating Index (CONCURRENTLY)
- 16:31Expression Index 2/2
- 17:07Partial Index
- 19:51B-Tree Index
- 23:50Index Only Scans
- 27:13Hash Index
- 38:21Duplicate Indexes
- 41:13Supported Data Types For A Particular Indexes
- 42:20Unused Indexes
related talks
46mA Deep Dive Into PostgreSQL Indexing
52mA Deep Dive into PostgreSQL Indexing
37mRules of Indexing in PostgreSQL
53mAdvanced Indexing Techniques in PostgreSQL
37mRules of Indexing in PostgreSQL
29mPostgres Index types and where to find them
01h 00mFlexible Indexing With Postgres
52mPostgreSQL Index Strategy Guide