Automating Index Selection Using Constraint Programming

on YouTube ↗49m

" summary

An open source constraint programming model converts PG stat statements workloads into per-table scans, uses hypoPG to get hypothetical planner costs and estimates an index write overhead metric, then lexically optimizes two goals (minimize cost, then overhead). The demo Rust CLI picks 1–3 indexes on demand, shows cost shifting from ~6000 to ~6400 while reducing overhead from 0.31 to 0.20, accepts a strictness 0.9 tradeoff, handles multi-column index permutations but notes GIN costing and partitioning as open problems.

AI-written summary · about

" chapters (6)

1. 0:00Introduction to the problem
2. 1:41Defining index selection
3. 9:37Constraint programming model
4. 27:26Practical implementation demo
5. 38:17PostgreSQL integration tips
6. 42:40Q&A and closing remarks

" related talks

• 37mHow Postgres could index itselfAndrew Kane · PostgresOpen Silicon Valley 2017
• 26mWhere do the performance cliffs come from?Tomáš Vondra · POSETTE: An Event for Postgres 2024
• 52mPostgreSQL Index Strategy GuideGreg Dostatni · Postgres Conference Orlando 2025
• 45mA Deep Dive into PostgreSQL IndexingIbrar Ahmed · PGConf NYC 2021
• 29mExplaining the Postgres Query OptimizerBruce Momjian · Citus Con: An Event for Postgres 2022
• 50mImproving Postgres' EfficiencyAndres Freund · PostgresOpen Silicon Valley 2017
• 26mBeyond Joins and IndexesBruce Momjian · POSETTE: An Event for Postgres 2024
• 52mA Deep Dive into PostgreSQL IndexingIbrar Ahmed · PGConf NYC 2022