# 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 the gap between them. What it did not measure is the setting everyone actually runs: `auto`. This record does not infer the decision from timings; it reads it out of `pg_prepared_statements`. - Kind: Measurement - Question: 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? - Finding: 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`. - Method: The same harness and the same 10-million-dead-plus-5,000-live table, across three index strategies. For each, one prepared statement was executed forty times in a single psql session and `pg_prepared_statements` was queried after every execution — the counters are session-local, which is why this measurement cannot be made from pgbench. The decision is not inferred from timings but read from the counters: `custom_plans` and `generic_plans` say directly what the planner chose on that execution. Timings arrive in pairs (the statement, then the counter query) and the parser keeps only the first. psql's aligned output pads the counter value with spaces, so output was switched to unaligned mode — the first run produced an empty series because of exactly that. - Metrics: Partial index, generic plans: 0 of 40 executions · Composite index, switch: execution 6 · Partial, first 5 → last 5: 0.36 → 0.20 ms · What reaching the cliff takes: force_generic_plan - Measured on: 2026-08-22 - Confidence: High confidence - Status: Current - Programme: Database & queries - Environment: Postgres 17-alpine · plan_cache_mode at its default (auto) · Table 10M dead + 5,000 live rows · Measurement one session · one prepared statement · 40 executions · Source of the decision pg_prepared_statements.custom_plans / generic_plans · Hardware Apple M4 Pro · 12 cores · 24 GB · Docker Desktop 29.7.2 - Technologies: PostgreSQL, SQL, Docker - To reproduce: EXECUTIONS=40 ./bench/plan-switch.sh - Source code: https://github.com/muhammetsafak/pg-queue-bench - Raw data: https://github.com/muhammetsafak/pg-queue-bench/blob/main/results/2026-08-22/plan-switch.json - Raw data licence: https://github.com/muhammetsafak/pg-queue-bench/blob/main/LICENSE - Published: 2026-08-23 - Source: https://www.muhammetsafak.com.tr/en/research/when-does-the-planner-go-generic/ - Language: en-US - Author: Muhammet Şafak --- The [partial index measurement](/en/research/partial-index-queue-table/) 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 | 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. > **Result** > > The cliff is real but fenced. The cost model is doing precisely the job of > keeping you off it, and it does that job without faltering across forty > executions. Reaching it takes writing `plan_cache_mode = force_generic_plan` — > turning the protection off by hand. ## 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](/en/research/queue-table-bloat-and-autovacuum/)) and constant re-planning; both are small in this workload, and neither is guaranteed to stay small in another. > **Caveat** > > One query shape, one Postgres version (17), and healthy statistics. The planner's > decision rests on an **estimate**; wrong statistics make a wrong estimate, so the > honest claim is "it did not switch under these conditions" rather than "it never > switches". Repeating this with stale statistics is a separate record — and that > scenario is the one route by which the sentence I am correcting here could turn > out to have been right after all. ## 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.