PL/pgSQL Function Plan Caching #

A function that has been fast for months becomes slow for one particular argument, and only inside long-lived connections. Calling the same SQL directly is fast. Restarting the application makes the function fast again for a while. Nothing in the function changed, and nothing in the data looks unusual.

The mechanism is the same plan caching described in prepared statement plan pinning, applied somewhere people do not expect it: every SQL statement inside a PL/pgSQL function is an implicitly prepared statement.

The Condition #

When PL/pgSQL executes a SQL statement for the first time in a session, it prepares it — parses, analyses and plans it — and caches the result for the life of the session. Subsequent executions reuse the plan, with the function’s variables supplied as parameters.

That produces behaviour identical to explicit prepared statements, including the custom-versus-generic transition: the first five executions get custom plans built with the actual parameter values, and from the sixth PostgreSQL compares the average custom plan cost with the generic plan cost and switches to the generic plan if it is not worse.

Three consequences follow, and all three are surprising the first time:

The session scope also means that anything invalidating the plan — a schema change, an ANALYZE on a referenced table — causes a re-plan on the next call, which is why the behaviour sometimes corrects itself without explanation.

How a statement inside a function gets its plan Four stages. On the first call the statement is parsed, analysed and planned with the actual argument values, giving a custom plan. The next four calls also get custom plans, and their costs are averaged. From the sixth call PostgreSQL compares that average with a generic plan's cost. If the generic plan is not worse it is used for the rest of the session. first call parsed and planned calls 2–5 costs averaged call 6 generic compared onward generic plan reused the cache lives for the session, not the transaction

Annotated Evidence #

The function:

CREATE FUNCTION orders_for(p_customer int, p_since timestamptz)
RETURNS TABLE (id bigint, total numeric) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.total FROM orders o
    WHERE o.customer_id = p_customer AND o.placed_at >= p_since;
END $$;

Making the internal plan visible with auto_explain:

SET auto_explain.log_nested_statements = on;
SET auto_explain.log_min_duration = 0;     -- only for the investigation
SET auto_explain.log_analyze = on;
SELECT * FROM orders_for(4821, '2026-01-01');
-- first calls: a custom plan for a customer with few orders
LOG:  duration: 0.412 ms  plan:
  Index Scan using orders_customer_placed_idx on orders o  (actual rows=12 loops=1)
        Index Cond: ((customer_id = $1) AND (placed_at >= $2))

-- from the sixth call: the generic plan took over
LOG:  duration: 1840.204 ms  plan:
  Bitmap Heap Scan on orders o  (actual rows=12 loops=1)
        Recheck Cond: (placed_at >= $2)
        Rows Removed by Filter: 4102008
-- the generic plan assumes average selectivity for both parameters

Confirming the cause by forcing the alternative:

SET plan_cache_mode = force_custom_plan;
SELECT * FROM orders_for(4821, '2026-01-01');   -- fast again
Three signals inside a function Three items are annotated. A function that is fast in a new session and slow in a long-lived one indicates a session-scoped plan cache. Nested statement plans logged by auto_explain showing dollar-sign parameters confirm the statement is prepared. The same function becoming fast again under force_custom_plan identifies the generic plan as the cause. fast in a new session, slow in an old one per-session plan cache logged plan shows $1 and $2 in conditions the statement is prepared force_custom_plan makes it fast the generic plan was the problem calling the SQL directly will not reproduce it

Step-by-Step Resolution #

  1. Reproduce by session age. Run the function in a fresh session and in one that has already called it several times. A difference confirms the plan cache.

  2. See the internal plan with auto_explain.log_nested_statements = on, which is the only convenient way to observe statements inside functions. Keep the duration threshold high outside investigations.

  3. Decide whether the generic plan is wrong. It usually is not; when it is, the cause is a skewed parameter whose average selectivity misleads the planner.

  4. Force a custom plan where needed, scoped as narrowly as possible:

    ALTER FUNCTION orders_for(int, timestamptz) SET plan_cache_mode = force_custom_plan;

    Function-level settings apply for the duration of the call and revert afterwards, which is a much better scope than a session or a system.

  5. Consider dynamic SQL for genuinely variable shapes. EXECUTE with USING re-plans every call — appropriate when the right plan really does depend on the arguments, and wasteful when it does not.

  6. Improve the estimates instead where possible: extended statistics on correlated columns, or a larger statistics target on the skewed one. A generic plan built from good statistics is often correct.

  7. Verify in a pooled environment. Connections there live for hours, so the generic-plan case is the normal one, not the exception.

Which control fits the case? Three cases. If the generic plan is fine, which is the common case, change nothing. If a skewed parameter makes the generic plan wrong, set plan_cache_mode to force_custom_plan on the function. If the right plan genuinely depends on the argument shape, use dynamic SQL with EXECUTE and USING. is the generic plan acceptable? yes, usually change nothing reuse is the point no, skewed parameter ALTER FUNCTION … SET force_custom_plan shape varies by argument dynamic EXECUTE … USING re-planned each time fix the estimates first where that is possible

Before and After #

-- BEFORE: generic plan for a skewed parameter, in a pooled connection
Bitmap Heap Scan on orders  Rows Removed by Filter: 4102008   1840 ms

-- AFTER: ALTER FUNCTION orders_for(int, timestamptz) SET plan_cache_mode = force_custom_plan
Index Scan using orders_customer_placed_idx   0.4 ms

Why direct SQL does not reproduce it #

The most confusing part of this problem is that running the function’s body as a direct query is always fast. That is not a coincidence: a literal query is planned with the values in front of the planner, so it always gets the equivalent of a custom plan. The function’s statement is parameterised, so from the sixth call it may not.

The practical consequence is that reproducing the problem requires reproducing the mechanism, not just the SQL. A PREPARE and six EXECUTEs with the same parameters does it, as does SET plan_cache_mode = force_generic_plan before a single call — the latter being much the fastest way to check whether a generic plan is the issue at all.

The same reasoning applies to every wrapper that parameterises: views called with parameters through a function, SQL-language functions that are not inlined, and statements inside DO blocks. Anywhere values become parameters, the custom-versus-generic decision applies, and anywhere it applies, a skewed distribution can make it the wrong decision.

SQL functions and inlining #

Not every function caches plans this way, and the distinction is worth knowing because it changes which tool applies.

A simple SQL-language function — one statement, no volatility surprises, declared STABLE or IMMUTABLE — is often inlined into the calling query. Its body becomes part of the caller’s plan, planned with whatever the caller knows, so there is no separate cached plan and no custom-to-generic decision of its own. That is usually the best outcome: the function is a naming convenience with no planning consequences at all.

A SQL function that cannot be inlined — multiple statements, VOLATILE where it matters, or a set-returning function used in a way that prevents it — is planned separately, much like a PL/pgSQL statement. And PL/pgSQL is never inlined; its control flow makes that impossible by construction.

The practical rule is that a single-statement helper is usually better written in SQL than in PL/pgSQL, because inlining gives the planner the whole query to work with. Wrapping the same statement in PL/pgSQL hides it behind a parameter boundary and introduces exactly the caching behaviour this page describes.

Common Pitfalls #

Testing the function’s SQL directly. It is always planned with literals and always fast. Diagnostic signal: cannot reproduce outside the function. Fix: force_generic_plan, or six calls.

Setting plan_cache_mode for the whole system. Every statement loses plan reuse. Diagnostic signal: planning time rising everywhere. Fix: ALTER FUNCTION … SET.

Reaching for dynamic SQL first. It removes caching entirely, including where caching was helping. Diagnostic signal: planning cost dominating a hot function. Fix: use it only where the plan must genuinely vary.

Ignoring statistics. A generic plan built from good statistics is often right. Diagnostic signal: wildly wrong row estimates in the logged plan. Fix: extended statistics, higher targets.

Frequently Asked Questions #

Are statements inside PL/pgSQL functions prepared? #

Yes. PL/pgSQL prepares each SQL statement the first time it runs in a session and caches the plan for the session, including the custom-to-generic transition that explicit prepared statements undergo.

How do I see the plan of a query inside a function? #

Enable auto_explain with log_nested_statements. EXPLAIN on a function call shows only the call itself; the nested statements’ plans are otherwise invisible.

How do I stop a function using a generic plan? #

Set plan_cache_mode to force_custom_plan for that function with ALTER FUNCTION … SET, which applies for the duration of each call. Alternatively use dynamic SQL with EXECUTE, which is planned afresh every time.

Up: Prepared Statement Plan Pinning