Parameter · Query Tuning / Genetic Query Optimizer
geqo_threshold
Sets the threshold of FROM items beyond which GEQO is used.
- Default:
12 - Type: integer
- Range: 2 … 2147483647
- Applies: Session (any user)
In plain words
Below this number of tables (more exactly, FROM items) the planner searches all join orders properly. At or above it, the genetic optimizer is used instead. Raising it gets better plans for big joins at the price of longer planning.
For DBAs and operators
The count is per join list after collapsing: a FULL OUTER JOIN counts as one item, and lists split by from_collapse_limit/join_collapse_limit are counted separately. Since both collapse limits default to 8, which is below 12, GEQO is in practice only used for long comma-separated FROM lists or when the collapse limits have been raised.
Planning time with exhaustive search depends on how connected the join graph is. Measured on 18.6 with geqo = off: a 14-table chain join planned in about 2.5 ms, but the same 14 tables all joined on one shared key took about 11 seconds. Measure with EXPLAIN (SUMMARY), which prints Planning Time, before and after changing it.
Typical tuning: reporting systems with large generated queries sometimes raise it to 14–20 to get stable, optimal plans; if planning time then dominates, lower it again or reduce the collapse limits.
How to change it
Any user can change it for their session with SET.