See how a finding is written before you buy the investigation.

Generic plans forced for the app role send small-tenant queries through a 2-million-row index scan

Observation

After plan_cache_mode = force_generic_plan was set on the app role, the “recent open orders” statement went from 0.91 ms to 12.98 ms mean execution time, and its max went from 8.83 ms to 735 ms. On the same 4-connection load, throughput fell from about 4,100 to about 300 statements per second. Every execution logged above 100 ms was for a small tenant (ids 1001–1020).

Evidence

E1 pg_stat_statements for the statement, 30 s per run:

Run	Mean	Stddev	Max	Calls / 30 s	>100 ms logged
A. Default (auto)	0.91 ms	0.28 ms	8.83 ms	122,878	0
B. Generic plans forced	12.98 ms	82.82 ms	735.00 ms	9,248	191

E2 auto_explain entry from run B (one of 191):

duration: 555.793 ms  plan:
  Query Parameters: $1 = '1020'
  Limit  (cost=0.43..384.74 rows=20 width=27)
    ->  Index Scan Backward using orders_created_idx on orders  (cost=0.43..128688.77 rows=6697 width=27)
          Filter: ((tenant_id = $1) AND (status = 'open'::text))

E3 EXPLAIN (ANALYZE, BUFFERS) for small tenant 1007, generic plan compared with custom plan:

-- generic plan
Index Scan Backward using orders_created_idx on orders
  (cost=0.43..128688.77 rows=6697) (actual time=6.127..531.739 rows=18 loops=1)
  Rows Removed by Filter: 2001182
Execution Time: 531.784 ms

-- custom plan
Index Scan using orders_tenant_idx on orders
  (cost=0.43..11.85 rows=25) (actual time=0.017..0.022 rows=18 loops=1)
  Rows Removed by Filter: 42
Execution Time: 0.060 ms

E4 Change timeline: the latency shift starts when the role-level setting is applied. Under the default auto mode, PostgreSQL kept custom plans for this statement, and no execution exceeded 100 ms.

Likely cause

The generic plan is costed for an “average” tenant: an estimated 6,697 open orders. That makes it look cheap to read orders_created_idx backward and stop after 20 matches, and for an established tenant with 40,000 orders that is true (1.3 ms). A small tenant has only 18 open orders, so the scan never fills LIMIT 20. It reads the whole index and discards 2,001,182 rows. A custom plan knows the tenant is small and uses orders_tenant_idx instead.

Impact

Small tenants wait 0.5–0.7 s on this endpoint. Slow calls also hold pooled connections, which is why total throughput fell for every tenant, not only the small ones. New customers are the ones in this cohort.

Recommendation

1. First: run ALTER ROLE app RESET plan_cache_mode; This returns to the known prior state. It applies only to new sessions, so recycle the application pool after the change. Measured: mean 1.08 ms, max 10.62 ms, nothing above 100 ms.
2. Then: run CREATE INDEX CONCURRENTLY orders_tenant_created_idx ON orders (tenant_id, created_at); The index serves both the tenant filter and the sort, so every tenant gets a good plan whichever plan mode is active. Measured with generic plans still forced: mean 0.03 ms, max 5.24 ms, 1,474,772 calls in 30 s.
3. Not recommended: setting force_custom_plan globally. It treats the symptom on this statement and adds planning cost to every other one.

Risk

Resetting the role setting brings back planning work that the setting was probably added to save, so check planning time and CPU after the change. The new index adds 60 MB (heap: 130 MB) and some write cost on every insert. A failed concurrent build leaves an INVALID index that has to be dropped. orders_tenant_idx may become redundant, but do not drop it in the same change.

Rollback

To undo step 1, re-apply the role setting. To undo step 2, run DROP INDEX CONCURRENTLY orders_tenant_created_idx;. Neither step changes table data.

Validation

Compare the same pg_stat_statements queryid over the same time window (mean, stddev and max). For tenant ids 1001–1020, auto_explain should log nothing above 100 ms. EXPLAIN for one small and one established tenant should show orders_tenant_created_idx. After the index exists, check insert latency and WAL volume.

Lab conditions: PostgreSQL 17.11 in Docker on a laptop, warm cache, pgbench with 4 clients for 30 s per run, 98% of calls from established tenants and 2% from small tenants. The absolute timings won’t transfer to your system; the relative change and the plan shapes are what matter. In a real engagement, this evidence comes from your client-run, read-only collection.