Tuning random_page_cost and how index correlation affects query plans

*Find the article on our blog here:*

https://pganalyze.com/blog/5mins-postgres-tuning-random-page-cost

In E105 of "5mins of Postgres" we discuss why changing random_page_cost from the default of 4 is usually a good idea, and a specific example of where a high random_page_cost caused a bad plan due to index correlation. We dive into the relevant parts of the Postgres source, and explain how planner costing works.

*Learn more about pganalyze:*

https://pganalyze.com

https://pganalyze.com/newsletter

https://twitter.com/pganalyze/

*Check out the pganalyze library for eBooks, webinars, and more:*

https://pganalyze.com/resources

📑 *What we have discussed in this episode of 5mins of Postgres:*

*100x Faster Query in Aurora Postgres with a lower random_page_cost - Blog post by Shayon Mukherjee*

https://www.shayon.dev/post/2024/55/100x-faster-query-in-aurora-postgres-with-a-lower-random_page_cost

*Postgres Documentation: Using EXPLAIN*

https://www.postgresql.org/docs/current/using-explain.html

*Postgres source: cost_index in costsize.c*

https://github.com/postgres/postgres/blob/453c46873774219243501b8efc16b2b5a5e9d194/src/backend/optimizer/path/costsize.c#L549

*Postgres source: btcostestimate in selfuncs.c*

https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/selfuncs.c#L6783

*Postgres source: genericcostestimate in selfuncs.c*

https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/selfuncs.c#L6544

*Postgres Documentation: random_page_cost*

https://www.postgresql.org/docs/16/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS