Commit d25ea0127 got rid of what I thought were entirely unnecessary derived child expressions in EquivalenceClasses for EC members that mention multiple baserels. But it turns out that some of the child expressions that code created are necessary for partitionwise joins, else we fail to find matching pathkeys for Sort nodes. (This happens only for certain shapes of the resulting plan; it may be that partitionwise aggregation is also necessary to show the failure, though I'm not sure of that.)
Reverting that commit entirely would be quite painful performance-wise for large partition sets. So instead, add code that explicitly generates child expressions that match only partitionwise child join rels we have actually generated.
Per report from Justin Pryzby. (Amit Langote noticed the problem earlier, though it's not clear if he recognized then that it could result in a planner error, not merely failure to exploit partitionwise join, in the code as-committed.) Back-patch to v12 where commit d25ea0127 came in.
Amit Langote, with lots of kibitzing from me
Discussion: https://postgr.es/m/CA+HiwqG2WVUGmLJqtR0tPFhniO=H=9qQ+Z3L_ZC+Y3-EVQHFGg@mail.gmail.com Discussion: https://postgr.es/m/20191011143703.GN10470@telsasoft.com
529ebb20aa Generate EquivalenceClass members for partitionwise child join rels.
src/backend/optimizer/path/equivclass.c | 161 ++++++++++++++++++++++++---
src/backend/optimizer/util/relnode.c | 15 ++-
src/include/optimizer/paths.h | 5 +
src/test/regress/expected/partition_join.out | 77 +++++++++++++
src/test/regress/sql/partition_join.sql | 15 +++
5 files changed, 259 insertions(+), 14 deletions(-)