Does Postgres switch a partial-index query to a generic plan on its own inside a prepared statement — or is the 1,673-fold cliff something you have to opt into?

Postgres declines. On the partial index all forty executions used a custom plan — the counter reads 40/0. The reason it declines is the disaster itself: a generic plan cannot use the partial index, so its estimated cost comes out high and the planner does not choose it. The composite index switches at the sixth execution exactly as documented (5/35) and loses nothing by it. So the 1,673-fold cliff is real but fenced: reaching it takes writing `plan_cache_mode = force_generic_plan`.

Postgres never turned the partial index into a generic plan: forty executions, forty custom plans

The partial index measurement pinned plan_cache_mode to each extreme and found a factor of 1,673 between them: 11,752 tps on a custom plan, 7 on a generic one. What it did not measure is the setting everyone actually runs — auto.

That record wrote a sentence there: “the profile degrades as it warms up… it does not show up in staging.” It was a reasonable inference and it turned out to be wrong.

The decision was read, not guessed

Postgres runs a prepared statement on a custom plan for its first five executions, then compares the generic plan’s estimated cost against the average of the custom ones, and switches only if the generic looks cheaper. pg_prepared_statements keeps that decision as a counter, so nothing has to be inferred from timings.

Strategy	First generic plan	Final counter (custom/generic)	First 5 (ms)	Last 5 (ms)
partial the switch was declined	never	40 / 0	0.36	0.20
composite	execution 6	5 / 35	0.40	0.21
(status)	never	40 / 0	0.98	0.78

One session, one prepared statement, forty executions. The counters were read after every one.

The composite index switches where the documentation says it will, right after the fifth:

#5  0.266 ms   custom=5  generic=0
#6  0.275 ms   custom=5  generic=1   ← switched
#7  0.185 ms   custom=5  generic=2

On the partial index it never happens. Forty executions, forty custom plans.

It declines because of the disaster itself

The planner does not choose the generic plan because it estimates its cost correctly. A generic plan does not know what $1 is; to use the partial index it would have to prove $1 = 'pending', it cannot, so that plan falls back to a sequential scan and its estimate goes through the roof. The average custom plan sits far below it, and the comparison comes out in favour of custom every time.

The real trade is smaller, and elsewhere

The protection has a price: a query on the partial index is re-planned on every execution. Forty executions, forty plans. The composite one reuses its plan from the sixth onwards.

At this scale the difference is unmeasurable — 0.20 ms against 0.21 ms. But planning is cheap on a cheap query, not on a many-table join or a long IN list. The partial index’s hidden costs are bloat (see the endurance measurement) and constant re-planning; both are small in this workload, and neither is guaranteed to stay small in another.

It corrects the earlier record

The “degrades as it warms up” paragraph in the partial index record went too far, and a note pointing at this finding has been added there. A measurement correcting its own publication is what this section’s contract asks for: if a number is published, so is the number that refutes it.

Postgres never turned the partial index into a generic plan: forty executions, forty custom plans

The decision was read, not guessed

It declines because of the disaster itself

The real trade is smaller, and elsewhere

It corrects the earlier record

Other records

The partial index grew three hundred and eighty times in fifteen minutes — and autovacuum never ran

A partial index makes a queue table forty-one times smaller — for as long as the planner picks it

Laravel's preload curve: 123 files buy eight times what the last 1,912 do