Webinar Recording: Automating Postgres Index Selection Using Constraint Programming

Find the presentation slide deck and all other shared material below! We will also be publishing a technical report that details the constraint programming model for Postgres index selection in a couple of weeks.

On November 15th, 2023, we hosted our webinar on index selection with constraint programming. We walked through our constraint programming optimization model that finds the mathematically optimal solution, showed how to guide a constraint programming solver to find the right indexes for a table, and how pganalyze extracts the relevant data from a Postgres instance.

Articles and material we talked about in the webinar:

Presentation slides: https://resources.pganalyze.com/pgana...

pganalyze newsletter: pganalyze newsletter

Index Selection Algorithm research paper mentioned in the webinar: https://www.vldb.org/pvldb/vol13/p238...

pganalyze Webinar recording “How to use the Postgres query planner to debug bad plans and speed up queries“:
   • Webinar Recording: How to use the Postgres...  

“Explaining indexes in Postgres“ - blog post by Tadeas Petak:
  / explaining-indexes-in-postgres  

Documentation article about Index Write Overhead: https://pganalyze.com/docs/indexing-e...

pganalyze eBook “Effective Indexing in Postgres” download link: https://pganalyze.com/ebooks/postgres...

5mins of Postgres Episode 14: HOT Updates vs Bottom-Up Index Deletion in Postgres 14: https://pganalyze.com/blog/5mins-post...

5mins of Postgres Episode 86: HOT Updates and BRIN indexes in Postgres 16: https://pganalyze.com/blog/5mins-post...

pganalyze Resources Library: https://pganalyze.com/resources

Get in touch with us to get a product walk-through or a demo tailored to your needs: https://pganalyze.com/contact

- - - - - - - - - - - - -

Following, we are sharing some of the responses to the questions we could not fit into our live Q&A session with all of you as they might prove to be useful:

- -
"How does modifying the cost values within the postgresql.conf impact the "cost" determined by the Index Write Overhead discussed? Or does it impact/change the results at all?" - by Joshua W.

Adjusting the costs would not affect the Index Write Overhead, since the cost settings in Postgres are all related to how the planner comes up with a query plan, focused on read activitiy. You can learn more about Index Write Overhead here: https://pganalyze.com/docs/indexing-e...
- -

- -
"Are you basing this talk on a conference paper? Will you publish one? What conference?" - by Vlastislav D.

Lukas and Philippe talked about Index selection at their talk at PGCon 2023. If you are interested in it, you can watch it here:    • Automating Index Selection Using Constrain...  

We will also be publishing a technical report that details the constraint programming model for Postgres index selection, in a couple of weeks.
- -

- -
"How do the index suggestions work for Partitioned tables? Will there be any difference when compared to non-partitioned tables?" - by Ashokraj M.

Generally, the pganalyze Index Advisor looks at partitioned tables in aggregate (and will suggest indexes for the whole table), but may also suggest indexes that only apply to parts of the table, if individual partitions are being queried.
- -

- -
"Do you have something that talks a bit more about reading your index advisor results table? Particularly talking about the Impact and Queries columns?" by Conrad H.

You can find information about the Index Advisor overview in the docs here: https://pganalyze.com/docs/index-advi... as well as in the initial announcement blog post (https://pganalyze.com/blog/automatic-....
- -