Parameter-sensitive query plan: explain fast and slow executions

The symptoms

• •The same parameterized statement returns in milliseconds with one set of bind values and in seconds (or minutes) with another set, against the same table state.
• •Application logs or pg_stat_statements show two distinct mean_exec_time values for the same queryid, with one cluster near the floor and the other clustered much higher.
• •EXPLAIN (ANALYZE, BIND_PARAMETERS) shows a Hash Join or Nested Loop with very different row counts, buffer reads, or plan shape when only the bound parameters change.
• •Workload traces show latency correlated with a parameter value range, not with traffic volume or locks; the slow path is reproducible by replaying the slow parameter in isolation.
• •Statistics targets are at defaults (default_statistics_target = 100) yet histograms lack detail in the column ranges actually used by the parameter distribution.

Likely causes

• •Prepared statements allow the planner to choose a generic plan; when the first few executions have parameters, custom plans may be skipped in favor of a generic plan that fits the average, not the worst case.
• •Skewed data distribution in columns referenced by the parameter (for example, a status column with 99% one value) means the planner underestimates selectivity for the rare value unless extended statistics or expression indexes exist.
• •Defaults such as default_statistics_target = 100 and the absence of per-column statistics on the filtered column leave multi-modal distributions under-modeled, so parameter values in the long tail get inlined into a generic plan with poor estimates.
• •plan_cache_mode is set to force_generic or auto without a careful plan equality check, and the planner does not detect that the saved plan is materially worse for the actual parameter.
• •Correlated predicates are evaluated independently in the planner; lack of CREATE STATISTICS on correlated column sets causes the planner to assume independence, producing wrong row estimates once a parameter pins one side of the correlation.
• •Histogram bucket layout (text or numeric) places the parameter across a bucket boundary where the most-common-value fraction changes abruptly, leading to step-function selectivity differences between adjacent parameter values.

First ten minutes

1. 01Capture the exact statement text and both parameter sets (fast and slow) from application logs or a reproducer; do not paraphrase the predicate, because operator and constant types matter to the planner.
2. 02Run EXPLAIN (ANALYZE, BIND_PARAMETERS) with the slow parameter set against a test copy or a read replica to confirm actual row counts, buffer reads, and plan shape; compare against the fast parameter set.
3. 03Query pg_stat_statements for the same queryid and record mean_exec_time, calls, rows, and shared_blks_hit/read to verify the workload evidence (two execution-time clusters) rather than assuming from a single run.
4. 04Inspect plan_cache_mode and the number of custom_plan versus generic_plan executions via pg_prepared_statements or application-side prepared-statement settings, so you know whether a generic plan is being forced.
5. 05Check pg_stats for the filtered columns: most_common_vals, most_common_freqs, histogram_bounds, n_distinct, and any null_frac; note whether the slow parameter value falls into a sparsely-sampled bucket.

Evidence to collect

• •EXPLAIN (ANALYZE, BIND_PARAMETERS) output for both the fast and slow parameter sets, preserving buffers output and the actual row counts at each node.
• •pg_stat_statements rows for the affected queryid showing mean_exec_time, stddev_exec_time, calls, rows, and shared buffer counts so the bimodal execution distribution is visible.
• •pg_stats rows for every column referenced in the WHERE clause, including most_common_vals, most_common_freqs, histogram_bounds, n_distinct, and null_frac.
• •Server settings: default_statistics_target, plan_cache_mode, random_page_cost, effective_cache_size, and any per-column ALTER TABLE ... SET STATISTICS values.
• •List of extended statistics objects on the table from pg_statistic_ext and pg_stats_ext, so you can rule out missing correlation models.
• •Application-side prepared statement usage (PREPARE/EXECUTE counts, psql \bind, JDBC prepareThreshold, psycopg prepared statements) to identify whether a generic plan is being selected and reused.

Where to look

• •Planner boundary: the input to the planner includes pg_statistics rows, plan_cache_mode, and any plan hints from extensions; divergence in plan shape between two parameter sets is decided at this boundary.
• •Executor boundary: the plan tree produced by the planner feeds the executor; differences in node types (Hash Join versus Nested Loop, Index Scan versus Seq Scan) appear here and explain the runtime gap.
• •Statistics storage boundary: pg_statistic and pg_statistic_ext are read by the planner; their bucket counts, MCVs, and correlation entries determine selectivity when a parameter is bound.
• •Prepared statement cache boundary: pg_prepared_statements and the session's plan_cache_mode decide whether a custom plan is recomputed per execution or a generic plan is reused, which is where parameter sensitivity is suppressed or surfaced.
• •Monitoring statistics boundary: pg_stat_statements aggregates per queryid across the whole cluster; it is the place where the bimodal mean_exec_time distribution that proves parameter sensitivity is observable.

Diagnostic steps

1. 01Compare EXPLAIN plans for the fast and slow parameter sets and label each node change; if node types or join order differ, the planner is reacting to parameter-driven selectivity and the issue is genuine plan divergence.
2. 02Compute the ratio of actual rows to estimated rows at each node for both plans; a ratio greater than 10x on multiple nodes in the slow plan indicates selectivity misestimation, not a different optimum.
3. 03Reproduce the slow plan with the default parameter set inside a fresh session where the planner has no cached plan, to confirm whether the issue is plan caching or a generic plan being chosen for many distinct parameters.
4. 04Run ANALYZE on the table and observe whether the slow plan shifts to the fast plan's shape; if it does, the root cause is stale pg_statistics rather than parameter sensitivity per se.
5. 05Check whether the filtered column has ALTER TABLE ... SET STATISTICS > 100 or a non-default target; if not, and the slow parameter lives in a low-frequency tail, the default target is a contributing cause.
6. 06List pg_statistic_ext entries on the table; if the WHERE clause joins two columns whose literals share correlation, missing extended statistics is a candidate root cause.
7. 07Force a custom plan for a session (plan_cache_mode = force_custom_plan) and re-measure; if the slow parameter is now fast, the parameters are being optimized against a generic plan and the cure is plan-mode tuning, not statistics.
8. 08Cross-reference the slow parameter value against most_common_vals and most_common_freqs; if the slow parameter is not in the MCV list and sits in a wide histogram bucket, the planner's assumption is linear within the bucket, which is often wrong for skewed columns.

Common mistakes

• •Treating a single slow execution as a flaky query; parameter-sensitive behavior requires showing that the same statement text produces a slower plan for a specific parameter distribution, not that one call was slow.
• •Increasing default_statistics_target globally without first checking whether the slow parameter falls into a histogram bucket that the additional buckets will actually subdivide; other queries may pay planning cost without fixing this one.
• •Forcing a generic plan globally to "stabilize" the plan; this can lock the planner into a plan tuned for the common parameter and make the rare parameter catastrophic, worsening the bimodal distribution.
• •Adding an index based on the slow parameter without verifying that the planner's estimate was wrong; if the planner already estimates one row, an index may not change the plan and may increase write cost.
• •Copying the predicate into a non-parameterized test query; the planner only sees parameter values when custom plans are used, so a constant-only EXPLAIN can hide the generic-versus-custom distinction that is the actual problem.

Safe fixes

• •Increase the per-column statistics target for the filtered column with ALTER TABLE ... SET STATISTICS <n> and then ANALYZE the table; this raises histogram resolution in the parameter range of interest without inflating global planning cost.
• •Create extended statistics with CREATE STATISTICS on correlated column sets referenced in the WHERE clause, then ANALYZE; this gives the planner a multivariate model so that pinned parameters do not produce independent-column estimates.
• •Adjust plan_cache_mode per session or per role to force_custom_plan when the application's parameter distribution is wide and skew-sensitive; verify with EXPLAIN that the slow parameter now picks a plan closer to the fast plan.
• •Replace generic prepared statements with custom plans at the driver level (for example, JDBC prepareThreshold changes, libpq PQexecParams instead of prepared statements) when the application can afford per-execution planning and the parameter space is small.
• •Add a partial index that matches the slow parameter's selectivity if the planner's row estimate is realistic (single-digit rows) but the estimated cost is wrong; check that EXPLAIN now uses the partial index for the slow parameter before keeping the index.
• •Re-ANALYZE the table after any bulk change to the filtered column's distribution; the safest fix is often the most boring one, and stale statistics are the most common root cause of parameter sensitivity.

Prove the fix

1. 01Run EXPLAIN (ANALYZE, BIND_PARAMETERS) for five representative parameter values spanning the slow and fast ranges; actual row counts should now stay within an order of magnitude of estimated row counts at every node.
2. 02Re-query pg_stat_statements for the same queryid and confirm that stddev_exec_time and the gap between max_exec_time and min_exec_time shrink versus the pre-fix baseline; calls should remain similar so the comparison is meaningful.
3. 03Replay the original slow parameter through the application and observe that end-to-end latency drops to within a small constant factor of the fast parameter's latency, not merely lower than before.
4. 04Confirm that the new plan shape is used on a fresh session where no cached plan exists; if the fix only works because of a cached plan, it is a caching side effect, not a selectivity fix.
5. 05Capture the pg_stats row for the filtered column before and after the fix and confirm that histogram_bounds has more entries in the parameter range of interest, or that extended statistics records exist for the correlated predicate, whichever applies.

Prevention and next steps

• •Set ALTER TABLE ... SET STATISTICS on columns whose value distributions are known to be skewed, rather than relying on the server-wide default_statistics_target, and document the chosen target values alongside the schema.
• •Create extended statistics for any WHERE clause that combines two or more columns whose values are correlated in production data, and re-create them when the schema changes.
• •Schedule ANALYZE around bulk loads, partition rotations, and backfills so that statistics track the real distribution; log the last-analyze time from pg_stat_user_tables in monitoring.
• •Use pg_stat_statements to alert on queries whose stddev_exec_time exceeds a multiple of mean_exec_time, because a bimodal distribution is the signature of parameter-sensitive planning.
• •Review prepared-statement usage at the application layer; prefer custom plans for statements whose parameter space is small and skew-sensitive, and prefer generic plans only when the planner has demonstrated stable plans across the distribution.

Safe commands and checks

EXPLAIN (ANALYZE, BIND_PARAMETERS) <prepared_statement_name> USING <slow_parameter_value>;
SELECT queryid, calls, mean_exec_time, stddev_exec_time, min_exec_time, max_exec_time, rows FROM pg_stat_statements WHERE queryid = <queryid>;
SELECT attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds, null_frac FROM pg_stats WHERE tablename = '<table_name>' AND attname = '<column_name>';
SELECT name, setting FROM pg_settings WHERE name IN ('default_statistics_target', 'plan_cache_mode', 'random_page_cost', 'effective_cache_size');
SELECT * FROM pg_statistic_ext WHERE stxrelid = '<table_name>'::regclass;
SELECT attname, attstattarget FROM pg_attribute WHERE attrelid = '<table_name>'::regclass AND attstattarget <> -1;
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET STATISTICS <n>;
VACUUM (ANALYZE) <table_name>;