PostgreSQL Query Plan Tuning

Epistemic Status: Sprouting - This is a zettelkasten-style card about a concept I’m interested in remembering.

---

PostgreSQL has several performance related settings that can be adjusted to improve query planning. Two in particular are:

• random_page_cost and seq_page_cost control the “cost” assigned to index scan and sequential scans. The defaults are optimized for hard disk drives which are not used much anymore. Changing the value of random_page_cost from the default of “4” to “1.1” can help the query planner decide to use indexes more often.
• work_mem affects the working memory available to a query operation. This can affects things like which sorting algorithm is used in an ORDER BY query. Setting it higher can cause the planner to use a much faster in-memory algorithm instead of an on-disk algorithm.

Paweł Urbanek’s article on Easy to Overlook PostgreSQL Performance Issues talks about these and other performance tweaks in the context of Rails applications.