How Postgres could index itself
summary
Claim: Postgres can automatically recommend and create real indexes by combining query collection, planner simulation and hypothetical indexes. Implementation 1 uses PG stat statements + pg_query to extract table/column candidates; implementation 2 streams slow logs into Dexter, creates ephemeral indexes via hypoPG, runs EXPLAIN to measure final cost and picks the best index. Dexter can process days of logs in minutes, creates indexes concurrently, and has surfaced 20+ indexes used in production; current limits: btree only, no partial/expression removal logic, and hosted providers may not support hypoPG.
AI-written summary · about
chapters (15)
- 0:00Start
- 2:39adding indexes to a database
- 3:15calculate the average time that a query is running
- 8:39traverse an index on multiple columns
- 9:32adding an index
- 10:02add a multi column index
- 11:44stores the most common values for every column
- 14:44log all the slow queries in your database
- 17:23explain on the query
- 17:47add hypothetical indexes
- 18:52adding a single column index
- 20:49running some one-off queries on your database
- 24:19remove any unused indexes postgres
- 24:38aggregate the stats across all databases in your cluster
- 33:45replace the log parser
related talks
45mA Deep Dive into PostgreSQL Indexing
02h 01mIndexes in PostgreSQL. How to Decide What to Create
26mBeyond Joins and Indexes
49mAutomating Index Selection Using Constraint Programming
52mA Deep Dive into PostgreSQL Indexing
29mPostgres Index types and where to find them
29mExplaining the Postgres Query Optimizer
52mPostgreSQL Index Strategy Guide