At PGCon 2023 we spoke about our new Constraint Programming Model for Index Selection in Postgres. This model is configured through customizable goals and rules that are passed to a constraint programming model, which then utilizes a solver such as CP-SAT to find the optimal index selection.
Our main goal with this new approach is to provide an iteration on what we've offered as part of pganalyze Index Advisor and the Indexing Engine for over a year, with an emphasis on better developer control, as well as a more sophisticated mechanism for dealing with the trade-off between Index Write Overhead and improved query performance thanks to added indexes.
This new model also represents an important step towards an upcoming feature for index consolidation recommendations within pganalyze, such as when two similar indexes can be simplified into one.
We're actively working on updating the pganalyze Index Advisor with this research, so you can easily run this new constraint programming model against your production query statistics in pganalyze. If you'd like to try this out once its available for testing, let us know by replying to this email.
In the meantime you can take a look at the published open-source model, or try out our experimental lint tool to run it locally during development. We've also published the slides, and anticipate a video recording to be available within the next weeks.
