Postgres Index types and where to find them
summary
Demo shows which Postgres index actually matters: B-tree for comparisons, GIN for arrays and full‑text, GiST for overlapping/geometry, BRIN for fast scans of huge, correlated tables. Examples include 31ms → 6ms by adding an index, a partial index shrinking from 13MB to 350KB and speeding queries tenfold, and GIN cutting 160ms → 41ms. Uses Django 2.2’s contrib.postgres indexes and raw CREATE INDEX, with concrete query plans and measured timings.
AI-written summary · about
chapters (35)
- 0:00Intro
- 0:41About me
- 2:03Overview
- 2:42Crocodile example
- 3:19Constraints
- 4:00Optimizing queries
- 4:41Creating indexes
- 5:25Creating unique indexes
- 6:00Creating partial indexes
- 6:58Why partial indexes are great
- 7:42Partial unique indexes
- 8:13Create unique index
- 9:04Multicolumn indexes
- 9:53Reuse multicolumn indexes
- 11:14Put most columns first
- 12:16Btrees
- 13:36Page
- 14:03High key
- 14:41Items
- 15:03Summary
- 15:28Btree
- 16:05Jin
- 17:29Postgres in Django
- 18:13Postgres vs Beetree
- 19:43Index can
- 20:31Gin
- 20:59Just Index
- 21:32Just Index vs Geometry Index
- 23:05Create Just Index
- 23:18Array Ranges
- 24:22Brain Indexes
- 25:09Brain Index
- 26:09Deletes
- 26:43Conclusion
- 27:11Questions
related talks
53mAdvanced Indexing Techniques in PostgreSQL
46mA Deep Dive Into PostgreSQL Indexing
49mFlexible Indexing with Postgres
37mRules of Indexing in PostgreSQL
52mA Deep Dive into PostgreSQL Indexing
01h 00mFlexible Indexing With Postgres
45mA Deep Dive into PostgreSQL Indexing
47mThe Whole Truth About Indexes in PostgreSQL