Question
PostgreSQL parallel query planner misestimating costs with expression indexes
Ari Moss
0 reputation · 11 Nov 2022, 23:10 UTC
38.4K views1
Loading…
0 reputation · 11 Nov 2022, 23:10 UTC
PostgreSQL 14+ utilizes a cost-based planner to determine if a query should be executed in parallel. This decision depends on the max_parallel_workers_per_gather setting and the estimated cost of a serial plan versus a parallel plan.
When queries rely on expression indexes rather than standard B-tree indexes on base columns, the planner's ability to accurately estimate the cost of parallel scans can be inconsistent. This creates uncertainty regarding whether the planner will correctly identify a plan as 'Parallel Aware' or default to a serial execution path despite available CPU resources.
Given these constraints, what specific factors influence the planner's cost estimation for parallel scans when expression indexes are involved? How does the presence of these indexes impact the selection of the parallel degree?
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.
postgresqlcpu_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.IMMUTABLE function or a multi‑step pure‑SQL expression), the real CPU time per row can be several times the default.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.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.
SHOW server_version;. Version 15+ includes the expression‑index cost adjustment; earlier versions do not.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.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.EXPLAIN to observe cost changes.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?
Use comments to ask for clarification. Post a solution as an answer.
2,800 reputation · 12 Nov 2022, 05:13 UTC
To build on the discussion of CPU overhead, it is important to note that the planner's decision to use a parallel index scan often hinges on selectivity estimation. When using expression indexes, if the planner cannot accurately estimate the number of rows returned (selectivity), it may overstate the cost of the parallel gather phase relative to the index scan itself.
This often leads to a "plan flip," where the optimizer unexpectedly chooses a sequential scan over a parallel index scan. To verify if this is occurring in PostgreSQL 14 or 15, you can use the following diagnostic steps:
EXPLAIN (ANALYZE, VERBOSE) to compare the estimated cost against the actual time spent in the parallel workers.max_parallel_workers_per_gather = 0; if the plan shifts to a serial index scan with a significantly lower relative cost, the issue likely lies in the parallel worker overhead estimation rather than the expression cost itself.