PostgreSQL 19’s optimizer work targets two limits: rewrites the planner couldn’t prove safe, and plans it couldn’t see. NULL semantics blocked many rewrites. NOT IN stayed an opaque SubPlan, and IS DISTINCT FROM, COALESCE, and IS TRUE/FALSE/UNKNOWN hid from the planner even when their inputs could never be NULL. On the planning side, aggregation was invisible during join planning, semijoin unique-ification considered only the cheapest path, and ordered Append never considered incremental sorts. Executor and estimation costs also stopped scaling: hash joins bloated buckets with NULL-keyed tuples (one report saw thousands of batches), and MCV matching was O(N²) with today’s large statistics targets. PG19 fixes these by collecting NOT NULL information earlier, widening the plan space, and repairing statistics and costing gaps.
The optimizer section of the PostgreSQL 19 beta 4 notes has sixteen items. They fall into three themes:
- Proving more about NULLs. Many rewrites are only legal when an expression provably can’t be NULL. PG19 gets better at proving that and applies it earlier.
- More choices for joins and aggregation. The planner gets new join conversions, eager aggregation, a better semijoin strategy, and more sort options under Append.
- Better estimates and costing. These cover MCV matching, boolean-function statistics, virtual generated columns, and partial-path startup costs.
Part A: Join-type conversions
NOT IN becomes an ANTI JOIN when NULLs are absent
The planner has never converted x NOT IN (SELECT y ...) into an anti-join, because of NULL semantics. If x = y returns NULL, NOT IN evaluates to NULL (effectively false) and the row is discarded. An anti-join keeps the row when no match is found.
If neither side can yield NULL, and the operator can’t return NULL for non-null inputs, the two behave identically. The planner can then treat the sublink as a first-class relation instead of an opaque SubPlan filter. That unlocks global join ordering and cost-based choice of join algorithm, often with significant gains on large datasets.
The planner verifies two things:
- Operator safety. The operator must belong to a B-tree or Hash operator family. This is a proxy for standard boolean behavior, since returning NULL on valid non-null inputs would break index integrity.
- Operand non-nullability. It uses several existing mechanisms:
- outer-join-aware Vars, to confirm a Var isn’t from the nullable side of an outer join
- the NOT-NULL-attnums hash table, for schema-level NOT NULL constraints
find_nonnullable_vars, for Vars forced non-nullable by qual clauses.expr_is_nonnullable, for other expression types
More LEFT JOINs become ANTI JOINs
If a right-hand-side var is forced to NULL by upper-level quals (A ‘qual’ or ‘qualification’ is a statement or expression that specifies or satisfies certain validation criteria along the code-path). but is known non-null for any matching row, the quals can only be satisfied by a null-extended row, meaning the join failed to match. The left join is then really an anti-join.
Previously this applied only when the join’s own quals were strict for the var forced to null. PG19 also checks table constraints. If the forced-null var belongs to the RHS, is NOT NULL in the schema, and isn’t nullable due to lower-level outer joins, the reduction applies.
To confirm the lower-level condition, the first pass of reduce-outer-joins collects the relids of base rels that are nullable within each subtree. The second pass uses them to verify that a NOT NULL var is safe to treat as non-nullable. This builds on the NOT NULL attribute infrastructure from commit e2debb643. It is based on a proposal by Nicolas Adenis-Lamarre, though not the original patch.
Memoize for ANTI JOINs with unique inner sides
Nested-loop SEMI and ANTI joins don’t scan the inner relation to completion, so Memoize can’t mark a cache entry complete. Marking it complete after the first inner tuple isn’t safe either. If that tuple fails the join clauses for the current outer tuple, a second inner tuple matching the parameters would find the entry already marked complete.
If the inner side is provably unique, there is no second matching tuple. PG19 therefore enables Memoize for ANTI joins with a provably unique inner side. SEMI joins gain nothing, because a SEMI join with a provably unique inner side is already reduced to an inner join by reduce_unique_semijoins.
Part B: Aggregation and join mechanics
Eager aggregation
Eager aggregation partially pushes aggregation past a join and finalizes it once all relations are joined. It may reduce the rows entering the join and give a better overall plan.
The planner’s separation between scan/join planning and post-scan/join planning kept aggregation invisible while the join tree was built. The patch collects aggregate functions from the targetlist and HAVING clause, plus grouping expressions from GROUP BY, and stores them in PlannerInfo. During scan/join planning, each base or join relation is checked for eligibility. If eligible, a separate RelOptInfo, called a grouped relation, is created to represent the partially aggregated version, with its own grouped paths.
Grouped paths come from two sources:
- Sorted and hashed partial aggregation paths added on top of non-grouped paths. Only the cheapest or suitably sorted non-grouped paths are considered, to limit planning time.
- Joining a grouped relation with a non-grouped relation. Joining two grouped relations is not supported.
Partial aggregation is pushed only to the lowest feasible level of the join tree where it significantly reduces row count. This limits planning time and keeps all grouped paths for the same grouped relation producing the same set of rows, which supports a fundamental planner assumption.
Grouping keys. For a partial aggregation pushed to a non-aggregated relation, every expression from that relation involved in upper join clauses must be included in the grouping keys, using compatible operators. This ensures an aggregated row matches the other side of the join if and only if each row in the partial group does. All rows in a partial group share the same “destiny”.
Restrictions and fixes:
- Outer joins. Partial aggregation can’t be pushed to the nullable side of an outer join. The NULL-extended rows wouldn’t exist yet, and rows could be grouped differently or aggregate values come out wrong.
- Collation fix. Eager aggregation relies on the B-tree
equalimagesupport function to confirm that equality implies image equality. The code had passed the data type’s default collation instead of the expression’s actual collation. With a non-deterministic column collation on a type whose default collation was deterministic, rows could be grouped prematurely and then discarded by strict join conditions, giving incorrect results. PG19 passes the expression’s actual collation. - Volatile functions. Pushing aggregates containing volatile functions below a join changes how many times the function runs. The Aggref nodes in the targetlist and
havingQualare checked, and eager aggregation is disabled when such functions are present. - Finalization. If a grouped relation exists for the topmost join relation, its paths are finalized at the end and compete with regular paths.
Antonin Houska originally proposed the patch in 2017. The commit reworks major aspects and rewrites most of the code.
Hash joins and NULL join keys
In a plain join, a tuple with a NULL join key can be discarded, since it can’t match anything (assuming a strict operator). If it comes from the outer side of an outer join, it must still be emitted null-extended. Hash joins used to insert such tuples into the hash table like normal ones. That is inefficient, and a large number of them bloats a hash bucket, possibly causing useless repeated attempts to split it or increase the number of batches. One report described a large join vainly creating many thousands of batches.
PG19 keeps these tuples out of the hash table and pushes them into a separate tuplestore, from which they are returned later. Returning them immediately would require substantial refactoring, and it wouldn’t work when rescanning an unmodified hash table. This also works in parallel hash joins, because whichever worker reads a null-keyed tuple can just return it, so the tuplestores are local even in a parallel join.
The commit also fixes a pre-existing bug. ExecHashRemoveNextSkewBucket failed to decrement hashtable->skewTuples for tuples moved from the skew table into the main hash table. That skewed ExecHashTableInsert‘s count of main-table tuples, though probably not by much.
Better planning of semijoins
Semijoins have two implementation techniques: JOIN_SEMI, where the executor emits at most one matching row per LHS row, and unique-ifying the RHS followed by a plain inner join. The latter had three drawbacks:
- Only the cheapest-total RHS path was considered, so a path with a better sort order could be missed.
- Heuristics chose between hash-based and sort-based unique-ification.
- The sort-based implementation ignored the pathkeys of the input subpath and the output, which could add redundant sorts.
PG19 creates a new RelOptInfo for the RHS representing its unique-ified version. It holds multiple paths: hash-based (from the cheapest total path of the original RHS) and sort-based (using presorted input paths, or explicitly sorting the cheapest total path). All compete in add_path(), and the unique-ified rel is joined to the other side with a plain inner join.
Most JOIN_UNIQUE_OUTER and JOIN_UNIQUE_INNER code is gone. T_Unique now means adjacent-duplicate removal on presorted input for both semijoins and upper DISTINCT, sharing data structures and functions. The dead UNIQUE_PATH_NOOP code is also removed, since a provably unique RHS is already simplified to an inner join by analyzejoins.c.
Incremental sorts under Append and MergeAppend
An ordered Append or MergeAppend must inject an explicit sort into any subpath that isn’t ordered enough, and only full sorts were considered. Now, if incremental sort is enabled and there are presorted keys, the planner uses an explicit incremental sort. This rests on the assumption, used elsewhere in the code, that incremental sort is always faster than full sort with presorted keys, and the cost model tends to agree. Full sort is therefore not considered in that case. The change is not backpatched because it could change plans.
Part C: Nullability-driven simplification
IS [NOT] DISTINCT FROM NULL becomes IS [NOT] NULL
A NullTest with !argisrow is fully equivalent to IS [NOT] DISTINCT FROM NULL. The parser already rewrites literal NULLs. A DistinctExpr whose input becomes NULL during planning, through const-folding of 1 + NULL or parameter substitution in custom plans, stayed a DistinctExpr. PG19 converts the case where one input is a constant NULL and the other is a nullable non-constant expression. (If the other input were not like that, the node would already have been simplified to constant TRUE or FALSE.) NullTest is far more amenable to optimization because the planner knows a good deal about it and next to nothing about DistinctExpr.
IS [NOT] DISTINCT FROM becomes =/<> for non-nullable inputs
IS DISTINCT FROM treats NULL as a normal value and never returns NULL. Previously the planner simplified it only when all inputs were constants. Now:
x IS DISTINCT FROM NULLfolds to constant TRUE ifxis non-nullable.- If both inputs are non-nullable, the expression is equivalent to
x <> y, and theDistinctExprbecomes an inequalityOpExpr. IS NOT DISTINCT FROMbecomes an equality operator.
A standard operator allows partial indexes and constraint exclusion. The equality form enables index scans, merge joins, hash joins, and EC-based qual deduction.
Earlier constant folding of var IS [NOT] NULL
Commit b262ad440 reduced IS [NOT] NULL on a NOT NULL column to a constant, but late, during qual distribution in query_planner. That prevented further folding with the constant and proved bug-prone. Folding at constant-folding time was impossible because per-relation NOT NULL information was collected only when building RelOptInfos.
PG19 collects NOT NULL attribute information before pull_up_sublinks, stores it in a hash table keyed by relation OID, and uses it for NullTest deduction on Vars during constant folding. This is also what makes pulling up NOT IN subqueries possible.
restriction_is_always_true and restriction_is_always_false stay. Self-join elimination can introduce new IS NOT NULL quals after constant folding. Also, converting outer joins to inner joins can make previously irreducible NullTests reducible.
COALESCE and ROW(…) IS [NOT] NULL
COALESCE returns its first non-null argument. If an argument is proven non-null and is the first non-null-constant argument, the whole expression is replaced by it. If it’s a later argument, all following arguments are dropped. This used to work only for Const arguments and now works for any provably non-nullable expression. Unreachable arguments aren’t evaluated, the planner no longer treats the expression as non-strict, and index scans become possible on the result. One plan change in generated_virtual.out required a test modification.
ROW(...) IS [NOT] NULL is broken into per-field tests, now using expr_is_nonnullable(). For IS NULL, one non-nullable field refutes the whole test, reducing it to constant FALSE. For IS NOT NULL, a non-nullable field’s check is guaranteed, so it’s discarded. Existing NullTest folding also now calls expr_is_nonnullable() instead of var_is_nonnullable(), so it benefits from future improvements to that function.
IS [NOT] TRUE/FALSE/UNKNOWN on non-nullable input
BooleanTest treats NULL as “unknown”. When the input is proven non-nullable, that handling is redundant, and the construct simplifies to a plain boolean expression or a constant.
Part D: Statistics and costing
Statistics for functions returning boolean
Commit a391ff3c3 let a support function provide a custom selectivity for WHERE f(...), but unintentionally removed the chance to apply expression statistics when no support function applies, because the code no longer fell through to boolvarsel(). PG19 restores the fall-through and puts the 0.3333333 default back into boolvarsel(). A regression test is added, using extended statistics. It is master-only, since backpatching could alter plan choices.
Extended statistics on virtual generated columns
Univariate and multivariate statistics can now be built on virtual generated columns and on expressions referring to them. The restriction against extended statistics on a single column is lifted for a single virtual generated column, since it’s treated as a single expression. The catalogs store references to virtual generated columns as-is. They are expanded at ANALYZE time to build statistics, and at planning time so the optimizer can use them. If a column’s generation expression is altered, existing statistics data is deleted and ANALYZE rebuilds it correctly.
Startup costs of partial paths
Comments had claimed startup cost is pointless for partial paths. It isn’t: a fast-start path is reasonable for a plan with LIMIT, perhaps over an aggregate that processes enough data to justify parallel query while the query still wants only some result rows. add_partial_path and add_partial_path_precheck are rewritten to consider startup costs. This also fixes a bug where add_partial_path_precheck ignored the new disabled_nodes field after commit e22253467942. It now compares costs the way compare_path_costs_fuzzily does. The work is based on earlier work by Tomas Vondra.
Takeaway
Taken together, the sixteen changes make one point: the more the planner can prove about NULLs, the more it can rewrite, and the rewritten forms (anti-joins, plain =/<>, simplified boolean expressions) are ones it understands well enough to use index scans, merge and hash joins, and global join ordering. Alongside that, eager aggregation, the unique-ified semijoin relation, Memoize for anti-joins, incremental sorts, and partial-path startup costs give the planner more plans to choose from, while hashed MCV matching, hash join NULL handling, and the statistics fixes keep estimation and execution efficient. Practically, declare NOT NULL wherever it’s true, and re-check key query plans with EXPLAIN after upgrading, since several of these changes are master-only because they can alter plan choices.