Generic and custom plans, and the switch

A prepared statement is planned with its actual parameter values the first several times it runs, producing a custom plan each time. After that the server builds one plan with the parameters left as placeholders, and if that generic plan does not cost more than the average of the custom ones it keeps it and stops planning the statement again. The saving is planning time on a statement executed constantly. The exposure is one plan serving values of very different selectivity.

The switch happens on the sixth execution

The count is fixed rather than tuned: five custom plans, then the comparison. What makes this awkward to diagnose is that the sixth execution of a statement can be an order of magnitude slower than the fifth with nothing else having changed, and the statement text is byte-identical throughout, so every tool keyed on the text shows one entry whose average time drifts.

Whether the switch is a problem depends entirely on the data. If a parameter is a customer identifier and every customer has a similar number of rows, the generic plan is right and the saved planning is free. If one value covers a third of the table and the rest cover a handful of rows each, the generic plan is costed against an average nobody actually runs, and it will be wrong for both cases in different ways.

Two settings bear on it. plan_cache_mode forces either behaviour, and setting it to force a custom plan is the one-step test for whether this is what you are looking at. The other is indirect: the generic estimate leans on the planner’s idea of how many distinct values the column holds, so an inaccurate distinct-value estimate makes the comparison itself unreliable.

Counting the plans the server made

The view of prepared statements reports both counts per statement.

CREATE TABLE reading AS SELECT g AS seq_key, repeat('.', 60) AS pad
FROM generate_series(1, 500000) g;
CREATE INDEX ON reading (seq_key);
CREATE TABLE hit (seq_key int);
ANALYZE reading;
PREPARE record_hit(int) AS INSERT INTO hit SELECT seq_key FROM reading WHERE seq_key = $1;
EXECUTE record_hit(1); EXECUTE record_hit(2); EXECUTE record_hit(3);
EXECUTE record_hit(4); EXECUTE record_hit(5);
EXPLAIN (COSTS OFF) EXECUTE record_hit(6);
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

SELECT 500000
CREATE INDEX
CREATE TABLE
ANALYZE
PREPARE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
                         QUERY PLAN                         
------------------------------------------------------------
 Insert on hit
   ->  Index Only Scan using reading_seq_key_idx on reading
         Index Cond: (seq_key = $1)
(3 rows)

    name    | generic_plans | custom_plans 
------------+---------------+--------------
 record_hit |             1 |            5
(1 row)

The plan printed on the sixth execution shows the placeholder rather than a value in its index condition, which is the visible signature of a generic plan and the thing to look for in a logged plan. The counts beside it are the durable evidence: five custom, one generic, and from here the statement is planned no further unless something invalidates it.

What to do when the generic plan is the wrong one

Prove it first by forcing a custom plan for that session; if the time returns, the diagnosis is settled in one step. The durable fixes are to stop preparing that particular statement, to set the mode at the narrowest scope that covers it, or to fix the distinct-value estimate that made the comparison wrong, which is n_distinct. Where the plan changed for reasons that have nothing to do with preparation, the wider diagnosis is query plan regression.