GEQO for Queries Joining Many Tables #

A report built on four views joins fourteen base tables. Its plan changes shape after routine analyzes, sometimes runs in two seconds and sometimes in forty, and nobody can reproduce the slow version on demand. The query has crossed geqo_threshold, and its join order is no longer found by exhaustive search but by a genetic algorithm sampling the space.

How the planner searches below that threshold is covered in join order and collapse limits. This page covers what changes above it.

The Planner Condition #

GEQO replaces dynamic programming for one join list when:

“Items” are the relations in the list after collapsing. Because collapse limits default to 8, a query only reaches GEQO in one list if its relations are comma-joined, or the collapse limits have been raised, or subqueries have been flattened into a large list. Explicitly nested joins above 8 are usually split into smaller lists first — which is why raising the collapse limits is a common route into GEQO.

The algorithm treats a join order as a sequence of relations. It creates a population of random sequences, costs each by building the joins in that order, and repeatedly combines pairs of cheaper sequences to form new candidates. The number of generations and the population size scale with geqo_effort (1–10, default 5) unless set explicitly with geqo_pool_size and geqo_generations. The random number generator starts from geqo_seed (default 0).

The key property: GEQO evaluates a tiny fraction of possible orders. For 14 relations there are tens of billions of join trees, and GEQO costs perhaps a few thousand sequences. It usually finds a reasonable plan and rarely the best one, and which reasonable plan it finds depends on the costs it saw.

How GEQO searches join orders A cycle of four stages. A pool of random join sequences is created from geqo_seed. Each sequence is costed by building its joins. Cheaper sequences are selected as parents. Parents are recombined into new sequences that replace the most expensive members. After the configured number of generations, the cheapest sequence becomes the plan. random pool seeded by geqo_seed cost each order build joins, sum cost select parents biased to cheap orders recombine replace worst members cheapest found after N generations generations and pool size scale with geqo_effort

Annotated EXPLAIN Evidence #

EXPLAIN does not print “GEQO”. The evidence is a comparison:

BEGIN;
SET LOCAL geqo = on;
EXPLAIN (ANALYZE, SUMMARY) SELECT … ;   -- 14-relation report
ROLLBACK;
Hash Join  (actual time=31044.2..38910.6 rows=22104 loops=1)
  …  (a nested-loop chain driven from order_events, actual rows=18,210,442)
Planning Time: 11.8 ms
Execution Time: 38975.4 ms
BEGIN;
SET LOCAL geqo = off;
EXPLAIN (ANALYZE, SUMMARY) SELECT … ;
ROLLBACK;
Hash Join  (actual time=1802.6..2107.3 rows=22104 loops=1)
  …  (driven from the 340-row filtered accounts set)
Planning Time: 612.4 ms           -- 50× more planning
Execution Time: 2170.9 ms         -- 18× less execution

Reading the comparison:

Two captures of the same fourteen-table report With GEQO on, planning takes 11.8 milliseconds, the plan drives from order_events and execution takes 39 seconds. With GEQO off, planning takes 612 milliseconds, the plan drives from the 340-row accounts set and execution takes 2.2 seconds. geqo = on Planning Time: 11.8 ms driving relation: order_events Execution Time: 38,975 ms geqo = off Planning Time: 612.4 ms driving relation: accounts (340 rows) Execution Time: 2,171 ms compare both lines; EXPLAIN never says which search produced the plan

Step-by-Step Resolution #

  1. Count relations per list. Expand views and pulled-up subqueries. EXPLAIN (VERBOSE) output lists every scanned relation; count distinct scan nodes under the same query level.

  2. Measure GEQO on vs off with EXPLAIN (ANALYZE, SUMMARY) as above. Record both Planning Time and Execution Time, and multiply planning time by how often the statement is planned — once per execution unless a cached plan is reused.

  3. Check seed sensitivity. Plan with three or four geqo_seed values. If execution time varies by more than a small factor between them, the query is a poor fit for sampled search.

  4. Raise the threshold for the workload that benefits, not server-wide:

    ALTER ROLE reporting SET geqo_threshold = 16;
-- or per transaction
SET LOCAL geqo_threshold = 16;

    If exhaustive planning is too slow, try geqo_effort = 8 or 10 first: more generations often find a better order at a fraction of exhaustive cost.

  5. Shrink the list where possible. Precompute a stable dimension join into a materialized view, or materialize a selective subset with a MATERIALIZED CTE — a fence that splits one large list into two smaller ones, each below the threshold. The trade-offs of that fence are covered in materialized vs not materialized CTEs.

Planning time against relation count A chart with relation count from 6 to 18 on the horizontal axis. Exhaustive planning time rises steeply, becoming very large past 14 relations. GEQO planning time rises gently. The default threshold at 12 sits where exhaustive search begins its steep climb. 6912 1518 slow fast geqo_threshold = 12 exhaustive search GEQO

Before and After #

-- BEFORE: geqo_threshold = 12, 14 relations → GEQO
Planning Time: 11.8 ms     Execution Time: 38975.4 ms

-- AFTER: ALTER ROLE reporting SET geqo_threshold = 16
Planning Time: 612.4 ms    Execution Time: 2170.9 ms

When the trade is worth it #

The report above runs a few dozen times a day, so 600 ms of extra planning per execution is irrelevant next to 36 seconds of saved execution. The calculation changes for statements executed at high frequency. A 13-table query behind an API endpoint called 200 times a second would spend two minutes of CPU per second planning exhaustively — clearly impossible. For such statements, a prepared statement whose generic plan is found once by exhaustive search and then reused is the escape: planning cost is paid on the first executions and never again, so a high geqo_threshold costs almost nothing. That requires the driver to prepare statements and the plan to be stable across parameter values, which the prepared statement plan pinning section covers.

Views and ORMs make relation counts easy to underestimate. A single “customer summary” view that joins nine tables, joined to two other views, puts a query well above the threshold while its text mentions three names. Counting scan nodes in the actual plan is the reliable method.

Common Pitfalls #

Turning GEQO off globally. Exhaustive search on a 20-relation list can plan for seconds or far longer, and a single ad-hoc query can stall a backend. Diagnostic signal: sessions stuck in planning with pg_stat_activity.state = 'active' and no I/O. Fix: raise the threshold for specific roles instead.

Raising collapse limits without noticing GEQO. Setting join_collapse_limit = 20 to let the planner reorder a 14-table query moves that query from separate small lists into one list above geqo_threshold. Diagnostic signal: planning time drops while execution gets worse after raising the limits. Fix: raise geqo_threshold together with the collapse limits.

Attributing seed variance to data changes. Small statistic changes move GEQO to very different plans. Diagnostic signal: large plan flips after ordinary autoanalyze, only on queries above the threshold. Fix: test seed sensitivity, then raise the threshold or reduce the list.

Using GEQO plans on skewed estimates. Sampled search amplifies poor estimates because it has fewer alternatives to fall back on. Diagnostic signal: wildly wrong rows= on the driving relation. Fix: repair statistics first; a better estimate helps both search methods.

Frequently Asked Questions #

How can I tell whether GEQO planned my query? EXPLAIN does not label it. Count relations in the largest join list after expansion; if the count reaches geqo_threshold with geqo on, the genetic optimizer ran. Confirm by planning with SET LOCAL geqo = off.

Is GEQO non-deterministic? It is deterministic for fixed inputs because geqo_seed defaults to 0. Changing the seed, statistics or cost settings can lead it to a very different order.

Should I just turn GEQO off? Not globally. Raise geqo_threshold for the workload that benefits and keep GEQO as the safety net for lists too large to search exhaustively.

Up: Join Order and Collapse Limits