Parameter · Query Tuning / Genetic Query Optimizer
geqo_effort
GEQO: effort is used to set the default for other GEQO parameters.
- Default:
5 - Type: integer
- Range: 1 … 10
- Applies: Session (any user)
In plain words
A simple trade-off for the genetic optimizer: more effort means a bigger population of candidate join orders and more rounds of breeding, so better plans but longer planning. It only matters for joins large enough to use GEQO.
For DBAs and operators
When geqo_pool_size is 0 (default), the pool size is 2n+1 for n relations, clamped between 10 × effort and 50 × effort. With the default effort 5 that is 50 to 250 join orders; with effort 10, 100 to 500. When geqo_generations is 0, the number of generations equals the pool size. So, once the pool hits its clamp, the number of join orders costed (pool plus generations) grows roughly in proportion to the effort.
On 18.6 a 14-table join with GEQO planned in about 20 ms at effort 5 and about 35 ms at effort 10. Raising effort helps when GEQO plans for the same query vary a lot between runs or are clearly worse than with GEQO off; it is the recommended knob rather than setting pool size and generations by hand.
How to change it
Any user can change it for their session with SET.