Fix more duplicate qual clauses in parameterized paths Commit f836b688f ensured that at most one clone of a join clause is chosen for a parameterized path, so long as the clone variants are parse-tree distinct. Two ways remained to end up enforcing the same condition more than once, which wastes execution effort and applies the clause's selectivity multiple times, underestimating the row count.
If a commuting outer join nulls no Var referenced by a particular qual, the clone variants of that qual are parse-tree identical, and the incompatible_relids test cannot tell them apart. To fix, enforce just the first surviving variant, identified by rinfo_serial. The other case is in get_joinrel_parampathinfo, where the clause-recovery pass for dropped EquivalenceClasses queries the EC machinery with a context that overlaps the preceding loop's, so the derived-clause cache can hand back a clause that that loop already accepted. To fix, skip any clause that is already present in the collected list. Also add assertions verifying that the clauses to be enforced at a join or within a parameterized path contain no duplicate rinfo_serial, to catch any remaining or future violations. These assertions are added in master only, since hard prevention of duplicate clauses is a new goal and other unfixed cases may remain. Back-patch to v19, as with commit f836b688f. Author: Richard Guo <[email protected]> Author: Tom Lane <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/cambws4_ezb1lnj7bqctqsysc6b5ufzxfhib8xr6rqpu5uqr...@mail.gmail.com Backpatch-through: 19 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/8510bcc981bd4bba06fd2c038dab7cc15887b51e Modified Files -------------- src/backend/optimizer/util/relnode.c | 146 +++++++++++++++++++++++++---------- src/test/regress/expected/join.out | 55 +++++++++++++ src/test/regress/sql/join.sql | 22 ++++++ 3 files changed, 183 insertions(+), 40 deletions(-)
