pgsql: Reduce outer joins to anti joins for whole-row IS NULL tests

Reduce outer joins to anti joins for whole-row IS NULL tests

reduce_outer_joins() recognizes "WHERE b.z IS NULL" above an outer
join as an anti-join condition, but not the whole-row "WHERE b IS
NULL", which is a natural way to ask for an anti-join without naming a
specific column. Teach it to recognize the whole-row form too.

A row that the join null-extends has all of b's columns set to NULL,
so it satisfies "b IS NULL". A matched row satisfies the test only if
its columns happen to be all NULL. Hence proving any one column of b
non-null in matching rows rules out every matched row, leaving only
null-extended rows: exactly anti-join semantics. This mirrors the
single-column case, and the same proofs apply: a NOT NULL table
constraint, a strict join clause (for LEFT joins), or strict quals
within the relation's subtree. Because any one column suffices, the
whole-row test reduces in a strict superset of the cases a
single-column test does.

To implement this, find_forced_null_vars() now reports a whole-row Var
tested with row-format IS NULL as a varattno-zero entry meaning that
all of the relation's columns are forced null. Row-format tests on
ordinary composite-type columns remain excluded, since such a test
does not force the column null: it is also true when the column is a
non-null row whose fields are all NULL. The proof functions in
reduce_outer_joins() treat a whole-row entry accordingly: it is
refuted by proving any one column of its relation non-null. A match
on the whole-row attribute itself proves nothing, because a non-null
composite datum can still have all columns NULL, so the per-column
matching now explicitly excludes that attribute.

Author: Richard Guo <guofenglinux(at)gmail(dot)com>
Reviewed-by: wenhui qiu <qiuwenhuifx(at)gmail(dot)com>
Discussion: https://postgr.es/m/CAMbWs49H9khf+1GwyzD0TYaks6cE-OU+1mbUiOn3gZSoBiO7zg@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/02119d6c87e20bd17f9b94b0420c2713d62275bc

Modified Files
--------------
src/backend/optimizer/prep/prepjointree.c | 101 +++++++++++++++----
src/backend/optimizer/util/clauses.c | 19 +++-
src/test/regress/expected/join.out | 156 ++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 60 ++++++++++++
4 files changed, 314 insertions(+), 22 deletions(-)