Parameter · Query Tuning / Genetic Query Optimizer
geqo
Enables genetic query optimization. This algorithm attempts to do planning without exhaustive searching.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
Finding the best order to join tables becomes extremely expensive as the number of tables grows. For big joins (12 or more FROM items by default) PostgreSQL switches to a genetic algorithm that tries random orders and "breeds" the good ones. It is faster but not guaranteed to find the best plan.
For DBAs and operators
The standard join search is dynamic programming: it finds the best plan for every pair of relations, then every set of three, and so on. The work grows exponentially with the number of relations. When a join list has at least geqo_threshold items and geqo is on, geqo() replaces this search for that list.
GEQO treats a join order as a chromosome (a permutation of relations), creates a random population, and for each generation picks two parents (biased toward cheap ones), combines them with edge recombination crossover and replaces the worst member with the child. The best order found is used.
The exhaustive search is not always slow: for a 14-table chain join on 18.6, planning took about 2.5 ms with geqo = off and about 20 ms with GEQO, because the planner avoids joins that have no join clause. The same 14 tables all joined on one shared key (so every pair is joinable) took about 11 seconds with geqo = off. The explosion happens with densely connected joins (star schemas, many cross-conditions). Turning GEQO off for very large joins can take seconds of planning and lots of memory; leaving it on means plans can vary when the query or stats change slightly. Many large-join problems are better handled by join_collapse_limit and writing the joins in a sensible order.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
geqo_threshold geqo_effort join_collapse_limit from_collapse_limit geqo_seed