Data Interview: How do you diagnose a PostgreSQL generic plan for a parameterized query?

Prompt and use case

An API using prepared statements develops long-tail latency after launch: small tenants are fast, while large tenants suddenly receive sequential scans. Explain how PostgreSQL chooses custom and generic plans, how to compare them with EXPLAIN (GENERICPLAN), how to verify statistics, parameter skew, caching, and plancache_mode, and how to fix the issue without breaking transactions or the connection pool.

What the interviewer is testing

• Separating planning, execution, and result-serialization cost.
• Understanding that a generic plan ignores concrete parameter values while a custom plan can use selectivity.
• Using EXPLAIN ANALYZE safely without shipping write side effects to production.
• Combining statistics, indexes, connection pools, and parameterization to find the regression.
• Choosing auto, forcegenericplan, or forcecustomplan from evidence.

Questions to clarify first

• Is the query executed through a prepared statement, ORM, or proxy, and are connections reused?
• Are parameters skewed, with meaningful differences in tenant size and data temperature?
• Is the regression in planning, execution, lock wait, IO, or serialization?
• May you change indexes, statistics targets, SQL, the pool, or session-level settings?

Thirty-second answer

Start with EXPLAIN (GENERICPLAN) to see the plan that does not depend on parameter values, then use representative values with EXPLAIN ANALYZE EXECUTE to inspect custom plans and actual rows. A generic plan saves planning work but can stay inefficient when selectivity is skewed. Verify statistics and plan-cache behavior, benchmark planning, execution, and tail latency, then change plancache_mode or the query in a controlled session and validate every pool connection.

Deep-dive answer, step by step

1. Separate planning from execution

The planner chooses scans and joins from SQL, statistics, and parameters; the executor reads pages, filters rows, and returns results. Application wall time alone cannot prove that a generic plan is the cause.

2. Explain a custom plan

A custom plan is generated for the current parameters and can exploit selectivity. A small tenant may favor an index scan while a large tenant favors a sequential scan or a different join order; the cost is repeated planning.

3. Explain a generic plan

A generic plan uses placeholders and ignores the current values. It amortizes planning overhead, but one plan may be poor for most values when the distribution is highly skewed. EXPLAIN (GENERIC_PLAN) cannot be combined with ANALYZE.

4. Inspect the generic plan first

EXPLAIN (GENERIC_PLAN)
SELECT sum(amount)
FROM invoices
WHERE tenant_id = $1 AND status = $2;

Inspect scan type, estimated rows, index conditions, join order, and total cost. Add explicit casts when parameter types cannot be inferred, so a type issue is not mistaken for a planning issue.

5. Inspect representative custom plans

In an isolated environment, execute EXPLAIN (ANALYZE, BUFFERS) EXECUTE with values from tenants of different sizes. Compare estimated and actual rows, shared hits and reads, planning time, execution time, and disk sorts; do not compare cost numbers alone.

6. Check statistics and distribution

Confirm that autovacuum or manual ANALYZE covers recent changes, then inspect cardinality, correlation, and most-common values. A higher statistics target may help a skewed column, but measure both planning accuracy and planning overhead.

7. Choose the repair boundary

Session-level plancachemode=forcecustomplan can test whether custom plans remove the regression; forcegenericplan suits stable queries with expensive planning. Long-term fixes may be an index, query split, explicit types, or avoiding unnecessary prepared statements in the ORM, not a global setting change alone.

8. Validate the pool and rollout

A pool makes session settings, prepared-statement lifetime, and PostgreSQL-version differences important. During canary rollout compare p95/p99, planning time, buffer hits, and error rate by parameter percentile, tenant size, pool instance, and database node, with a rollback switch ready.

Trade-offs and boundaries

A generic plan saves planning work but loses selectivity from the current values; a custom plan may waste planning CPU for frequent short queries. EXPLAIN ANALYZE executes the statement, so write statements need a rollback transaction or read-only replica. Plan cost is an estimate, not milliseconds; statistics are sampled, and plans can change with data, PostgreSQL versions, and ANALYZE.

Rollout plan and evidence

1. Record query text, parameter types, pool mode, PostgreSQL version, and plan-cache behavior.
2. Baseline GENERIC_PLAN and ANALYZE plans for representative parameter values.
3. Check statistics age, estimate error, index hits, IO, and planning time.
4. Test plancachemode in one connection or a canary session, not by changing global configuration first.
5. Accept on p95/p99, planning CPU, shared reads, lock waits, and errors, while retaining a rollback switch.

Common mistakes and follow-ups

Mistake 1: Removing a generic plan after seeing a sequential scan

A sequential scan may be correct for a large result. Compare actual rows, IO, and tail latency for representative values first.

Mistake 2: Treating cost as real time

Cost is a relative planner estimate. Combine ANALYZE actual time, buffers, and production metrics.

Mistake 3: Running write EXPLAIN ANALYZE directly in production

ANALYZE executes the statement. Validate writes in a rollback transaction or isolated replica.

Mistake 4: Raising statistics targets only

Higher targets add analysis and planning work and may not fix pool or parameter-type issues. Prove the benefit with a benchmark.

Mistake 5: Ignoring pool session boundaries

Session-level plancachemode and prepared statements may affect only some connections. Cover every pool connection and recycle policy before release.