Use genetic query optimization to plan queries with at least this many FROM items involved.
At a glance
| Property | Value |
|---|---|
| Parameter | geqo_threshold |
| Category | Query Planning |
| Default | 12 |
| Value type | integer |
| Change scope | Per-session (SET) |
| Available in | PostgreSQL 12, 13, 14, 15, 16, 17, 18, 19 (added in 12) |
What it does
Use genetic query optimization to plan queries with at least this many FROM items involved. (Note that a FULL OUTER JOIN construct counts as only one FROM item.) The default is 12. For simpler queries it is usually best to use the regular, exhaustive-search planner, but for queries with many tables the exhaustive search takes too long, often longer than the penalty of executing a suboptimal plan. Thus, a threshold on the size of the query is a convenient way to manage use of GEQO.
(Description quoted from the official PostgreSQL documentation.)
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW geqo_threshold; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'geqo_threshold';.
Tuning guidance
Raise it so more joins are planned exhaustively (better plans, slower planning) before GEQO takes over; lower it to cap planning time on very large joins.