Conceptual
Login

The Settings Behind Every Cost Estimate: Page Costs, Tuple Cost and Effective Cache Size

The planner turns estimated row counts into a cost using a handful of configuration settings: seq_page_cost (1.0) for a page read in order, random_page_cost (4.0) for a page read out of order, cpu_tuple_cost (0.01) per row processed, and effective_cache_size, its guess at how much of the data is already in memory. Those defaults describe a spinning disk, which is why a database on SSD can wrongly believe index access is four times more expensive than it is. Knowing the settings exist explains why the planner keeps choosing a sequential scan on hardware where an index would win.

Questions this Concept answers

  • Why can PostgreSQL's default cost settings misjudge index access on a server backed by SSDs?