Cost Model Constants and Parameters

You will tune cost constants to steer the planner toward efficient hardware-specific execution paths.

In this lesson, you will learn how to manipulate PostgreSQL’s internal cost constants to influence the query planner’s preference for specific storage media and hardware configurations.

The Planner’s "Price List"

PostgreSQL’s query planner is a cost-based optimizer. It estimates the "price" of different execution paths—like a Sequential Scan versus an Index Scan—based on a set of internal variables. These constants act as a "price list" that tells the engine how "expensive" certain operations are relative to one another.

When you have specialized hardware, such as ultra-fast NVMe SSDs or high-latency network storage, the default costs may lead the planner to choose suboptimal plans. You can adjust these in postgresql.conf or via SET commands to align the planner’s assumptions with your actual physical reality.

Key Tuning Parameters

The most influential parameters are:

seq_page_cost: The cost of fetching one page from disk during a sequential scan. The default is 1.0. If you are using fast SSDs, reducing this (e.g., to 0.1) makes sequential scans appear "cheaper," encouraging the planner to prefer them for large data fetches.
random_page_cost: The cost of fetching a non-sequentially accessed page (e.g., via an index). The default is 4.0. On modern SSDs, random access is nearly as fast as sequential access. Setting this closer to 1.1 prevents the planner from being overly biased against index scans.
cpu_tuple_cost: The cost of processing each individual row. Increasing this value forces the planner to be more aggressive about using indexes to filter data early, rather than scanning many rows and filtering them in memory.

Practical Example

If you have moved your data to high-performance flash storage, your planner might still be acting as if it is reading from spinning platters. You can nudge it toward index usage by lowering the random_page_cost:

-- Lower the cost of random access to reflect SSD performance
SET random_page_cost = 1.1;

-- Now explain a query to see if the planner switches to an Index Scan
EXPLAIN SELECT * FROM orders WHERE customer_id = 55;

By adjusting these constants, you tune the database’s "intuition" to match the specific performance characteristics of your underlying hardware, ensuring the chosen plan is truly the most efficient.

Key Takeaway: By adjusting cost constants like random_page_cost, you calibrate the query planner’s internal logic to match the physical I/O capabilities of your storage hardware.