A plan setting sped up large tenants—and stalled new ones.

Lab disclosure: Synthetic tenant and order data in an isolated PostgreSQL 17 environment. Measurements are from the lab, not a client system.

The change that triggered it

The lab models an orders endpoint shared by 50 established tenants and 20 small, new tenants. The query asks for a tenant's 20 most recent open orders. The application uses prepared statements. Initially, PostgreSQL's default plan_cache_mode = auto kept custom plans for this statement. Then the app role was changed to force_generic_plan, a plausible attempt to reduce planning work.

The symptom

With four load clients, throughput fell from about 4,100 to about 300 statements per second. Mean execution time rose from 0.91 ms to 12.98 ms, but the average hid the worst cohort: the maximum reached 735 ms. Every execution logged above 100 ms belonged to a small tenant. In a real SaaS service, this could appear as new-customer requests timing out while established tenants look healthy.

The evidence that narrowed the cause

The generic plan read the orders_created_idx index backwards, expecting to find 20 matching open orders quickly. A small tenant had only 18 matching rows. One measured plan scanned through the full index and removed 2,001,182 rows by filter, taking 531.8 ms. A custom plan used orders_tenant_idx, removed 42 rows by filter and completed in 0.06 ms for that tenant.

The timing of the role-setting change, the tenant cohort in auto_explain logs and the plan comparison all supported the same explanation. The sample investigation report includes the evidence table, plan excerpts, risk, rollback and validation details. PostgreSQL's PREPARE documentation explains how generic and custom plans differ.

Response and validation

1. Restore the known prior behavior: reset plan_cache_mode on the app role and recycle its connections, since role defaults apply to new sessions. The lab returned to a 1.08 ms mean and had no execution above 100 ms.
2. Test a structural alternative: a composite (tenant_id, created_at) index gave both tenant groups a useful ordered access path even while generic plans were forced. The lab measured 0.03 ms mean execution time for that run.
3. Check the trade-off: the new index was 60 MB against a 130 MB heap. A real decision would measure write latency, WAL, storage and index-build risk before keeping it.

These are warm-cache laptop results from 30-second runs; their absolute throughput is not a production promise. The reproducible SQL, workload and captured output live in the repository's labs/generic-plan-regression folder. The investigation lesson is to compare parameter cohorts and total workload impact before forcing a plan policy or adding an index.