Reduce FULL JOIN to ANTI JOIN reduce_outer_joins() already recognizes that a LEFT JOIN is really an anti-join when an upper qual forces a nullable-side Var to be NULL but that Var is provably non-null in every row the nullable side emits. Only null-extended rows can then satisfy the qual, so the matched rows all drop out and JOIN_LEFT becomes JOIN_ANTI.
The same reasoning applies to a FULL JOIN. If a forced-null Var on one side is proven non-null, every row where that side is present, matched or not, has the Var non-null and is dropped by the qual. Only the rows where that side was null-extended survive, which is an anti-join that keeps the other side's unmatched rows. When the proven Var is on the RHS, the surviving LHS rows are already the left input, so this is a plain JOIN_ANTI. When it is on the LHS, the surviving RHS rows must become the left input, so we tag the join JOIN_RIGHT_ANTI and let the existing input-switching step, the one that flips JOIN_RIGHT to JOIN_LEFT, normalize it to JOIN_ANTI. Unlike the LEFT JOIN case, the join's own ON quals cannot serve as proof here, because they do not hold for the unmatched rows the proof must cover. Reducing the full join this way also lets qual constraints reach its inputs. reduce_outer_joins_pass2() passes nothing down through a JOIN_FULL, but it does pass the join's own quals down through the resulting JOIN_ANTI, so outer joins below it can now be reduced too. That is sound for the same reason it is for any anti-join: a row that a lower join null-extends cannot satisfy those quals, so it can never match, and removing it does not change which rows the anti-join emits. The proof that a forced-null Var is non-null, from the quals that hold for every row a subtree emits (optionally plus extra quals the caller supplies) or from a NOT NULL constraint, is factored into forced_null_var_is_nonnullable() and shared by the LEFT and FULL paths. Author: Richard Guo <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Discussion: https://postgr.es/m/cambws49h9khf+1gwyzd0tyaks6ce-ou+1mbuion3gzsobio...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/2ce5a57bc0d93c6b14f7cd5ea5eb105a987bd46f Modified Files -------------- src/backend/optimizer/prep/prepjointree.c | 159 ++++++++++++++------ src/test/regress/expected/join.out | 236 ++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 77 ++++++++++ 3 files changed, 429 insertions(+), 43 deletions(-)
