Optimizing Procedure Cache Hit Rates

In this lesson, you will learn how function volatility categories directly dictate whether the PostgreSQL planner can cache your function’s execution plan or if it must re-evaluate it every time.

Understanding Function Volatility

In PostgreSQL, every function is assigned a volatility category: VOLATILE, STABLE, or IMMUTABLE. These categories are not just labels; they are critical hints to the query planner that determine how aggressively it can cache and optimize your code.

The Impact on Plan Cache

When the planner encounters a function call in a query, its ability to cache the result or the execution plan depends on the volatility:

• IMMUTABLE: The function always returns the same output for the same input. Because the result is guaranteed, the planner can pre-evaluate the function during planning time, effectively "inlining" the result into the query plan.
• STABLE: The function is guaranteed not to modify the database and will return the same result within a single table scan. The planner can cache the execution plan, but it must re-evaluate the function once per scan.
• VOLATILE: The function can change the database state or perform lookups that change over time (like random() or clock_timestamp()). The planner assumes the worst and must re-evaluate the function for every single row processed.

Illustrative Example

Consider a function that retrieves a tax rate from a configuration table. If you label this function VOLATILE (the default), the planner cannot cache the execution path:

-- Poor performance: Planner re-executes the function for every row
CREATE OR REPLACE FUNCTION get_tax_rate() RETURNS numeric AS $$
    SELECT rate FROM tax_config WHERE id = 1;
$$ LANGUAGE SQL VOLATILE;

If you change this to STABLE, the planner recognizes that the value won't change during the duration of the statement. It can optimize the plan by fetching the value once and reusing it throughout the query execution, significantly increasing your cache hit rate and reducing overhead.

By accurately defining volatility, you prevent the planner from defaulting to a "safe" but slow execution strategy, allowing it to reuse cached plans more effectively.

Key Takeaway: Precise function volatility labeling enables the PostgreSQL planner to reuse execution plans effectively by signaling when results can be safely cached across rows or queries.