Don't build child-join EC members that reference outside rels add_child_join_rel_equivalences() built a child version of every multi-relation EquivalenceClass member that overlapped the child join's topmost parent relids. For a member that also references a relation outside the join, the child version is of no use for what these members exist for, which is matching sort pathkeys of the child join, since such a member cannot be computed there.
The only effect of these members was on the clauses generated for parameterized child-join paths, and it was a harmful one. Each child join nested within a larger child join supplies its own version of such a member, as does each rebuild of the same child join by GEQO. These versions are all the same expression, yet generate_join_implied_equalities() would equate them to each other, producing redundant clauses that share one rinfo_serial and so trip the assertion added by commit 8510bcc98. Even a lone such member could yield a join clause that duplicates a condition already enforced by the inner path, as seen in an existing regression test whose plan now loses a redundant join filter. To fix, consider only members that can be computed at the child join's topmost parent joinrel. This matches what add_child_rel_equivalences() has done for base relations since commit 2489d76c4. In passing, simplify how both functions compute the child member's relids. Now that the parent member's relids are known to lie within the parent rel, the child member's relids are simply the child rel's. Back-patch to v19, as with commit 8510bcc98. Reported-by: Alexander Lakhin <[email protected]> Author: Richard Guo <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 19 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/0e8ba177cb3f1542d76cb5c6cbf71ff339bc80a9 Modified Files -------------- src/backend/optimizer/path/equivclass.c | 35 +++----- src/test/regress/expected/partition_join.out | 122 ++++++++++++++++++++++++++- src/test/regress/sql/partition_join.sql | 25 ++++++ 3 files changed, 158 insertions(+), 24 deletions(-)
