Flexible Indexing with Postgres
summary
Indexes beyond btree actually change performance: BRIN (block range) is tiny and suits chronologically ordered huge tables, Postgres 14 adds multi-range BRIN; a 100M-row example shows a 3.4GB table with a 2.1GB btree index so BRIN can be far smaller. GIN for json and full text stores each token once with multiple row pointers (Postgres 13 adds btree deduplication but cannot tokenise multi-valued fields). GiST / SP-GiST handle 2D, range and prefix partitioning, and bitmap scans let the planner merge partial indexes.
AI-written summary · about
chapters (13)
- 0:00Start
- 5:00Outline
- 9:12Expression Indexes
- 13:01Create an Expression Index
- 14:09Partial Indexes: Index Row Control
- 32:41Generalized Inverted Index (GIN)
- 37:10Data Type Support for Index Types
- 37:39Finding Supported Data Types - GIN
- 44:15R-Tree Indexes Bounding Boxes
- 45:26GIST Two-Dimensional Ops
- 47:12Extension Index Support
- 47:44Index Usage Summary
- 48:26Evaluating Index Types
related talks
01h 00mFlexible Indexing With Postgres
53mAdvanced Indexing Techniques in PostgreSQL
29mPostgres Index types and where to find them
44mBRIN improvements and new opclasses
46mA Deep Dive Into PostgreSQL Indexing
52mA Deep Dive into PostgreSQL Indexing
37mRules of Indexing in PostgreSQL
37mRules of Indexing in PostgreSQL