PostgreSQL parallel query planner misestimating costs with expression indexes

Answer to the Core Question

When PostgreSQL’s cost‑based planner decides whether to run a query in parallel, it compares the estimated cost of a serial plan with that of a parallel plan. Expression indexes—indexes on expressions such as LOWER(col) or custom functions—introduce per‑row CPU overhead that the planner’s default cost model may not fully capture. Below are the factors that influence the estimate and how they affect the chosen parallel degree.

Likely explanation

• The planner assigns a fixed cpu_tuple_cost per tuple for regular index scans. For expression indexes this per‑tuple cost is intended to cover expression evaluation, but the assigned value is a generic constant.
• If the indexed expression is non‑trivial (e.g., a complex IMMUTABLE function or a multi‑step pure‑SQL expression), the real CPU time per row can be several times the default.
• PostgreSQL 15 added a cost adjustment that adds the expression’s estimated CPU cost to the index‑scan cost, but only when the planner can estimate selectivity or the index is partial. Earlier releases (≤14) never apply this adjustment.
• Selectivity estimation matters: when the planner knows how many rows the expression will filter, it can proportionally add cost; otherwise it falls back to the generic model.
• Partial expression indexes allow the planner to use filter statistics, improving cost accuracy. Without such statistics the planner reverts to the generic model.
• The planner uses parallel_tuple_cost and cpu_tuple_cost to weight per‑tuple CPU work. If these are set too low relative to the actual expression cost, the parallel plan appears cheaper than it really is.

Impact on parallel‑degree selection

The planner chooses a parallel degree up to max_parallel_workers_per_gather when the parallel‑plan cost is lower. Under‑estimating the expression‑index cost makes the parallel plan look cheaper, potentially selecting a higher degree than is beneficial. Over‑estimating can suppress parallelism even when it would reduce runtime.

Diagnostic & mitigation steps (for this case)

1. Confirm PostgreSQL major version. Run SHOW server_version;. Version 15+ includes the expression‑index cost adjustment; earlier versions do not.
2. Inspect the execution plan. Run EXPLAIN (ANALYZE, BUFFERS) SELECT ...; with the expression‑index predicate and compare the estimated cost to the actual runtime. Also run the same query with SET enable_parallelism = off; to see the serial baseline.
3. Check cost parameters. Run SHOW parallel_tuple_cost; and SHOW cpu_tuple_cost;. If the expression evaluation is known to be expensive, consider raising these values to better reflect real CPU usage.
4. Test a rewritten plan. Materialize the expression into a computed column or use a partial index that the planner can estimate more accurately, then re‑run EXPLAIN to observe cost changes.
5. Disable parallelism if needed. Append SET enable_parallelism = off; to the query or add a LIMIT 1 to force a serial scan when the parallel plan underperforms.

To give a more precise recommendation, could you provide the PostgreSQL major version you are using?