Reduce LEFT JOIN to ANTI JOIN using quals within the RHS subtree reduce_outer_joins() turns a LEFT JOIN into an ANTI JOIN when some Var that an upper qual requires to be NULL is actually non-nullable in any matching row. When that holds, only null-extended (unmatched) rows can satisfy the upper qual, which is exactly anti-join semantics. Until now we recognized such a Var as non-nullable only when the join's own clauses were strict for it, or when it was defined NOT NULL by table constraints.
This patch allows strict quals applied within the RHS subtree to serve as the proof as well. Because such quals hold for every row the RHS emits, they hold for every matching row, so a Var they force non-null can become NULL above the join only by null-extension. To avoid re-walking the jointree at decision time, the first pass of the reduce-outer-joins process gathers these proving quals into its per-subtree state, alongside nullable_rels. The second pass then proves non-nullness from the RHS subtree's collected quals together with the join's own ON quals. As before, the reduction fires only when the proven, forced-null Var belongs to the RHS of the join. 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/a812cf647a828fc6157ca1f864f74067f088b580 Modified Files -------------- src/backend/optimizer/prep/prepjointree.c | 93 +++++++++++---- src/test/regress/expected/join.out | 189 +++++++++++++++++++++++++++++- src/test/regress/sql/join.sql | 61 +++++++++- 3 files changed, 319 insertions(+), 24 deletions(-)
