Basic partition-wise join functionality

Enterprise / PostgreSQL - Robert Haas [postgresql.org] - 6 October 2017 11:11 EDT

Instead of joining two partitioned tables in their entirety we can, if it is an equi-join on the partition keys, join the matching partitions individually. This involves teaching the planner about "other join" rels, which are related to regular join rels in the same way that other member rels are related to baserels. This can use significantly more CPU time and memory than regular join planning, because there may now be a set of "other" rels not only for every base relation but also for every join relation. In most practical cases, this probably shouldn't be a problem, because (1) it's probably unusual to join many tables each with many partitions using the partition keys for all joins and (2) if you do that scenario then you probably have a big enough machine to handle the increased memory cost of planning and (3) the resulting plan is highly likely to be better, so what you spend in planning you'll make up on the execution side. All the same, for now, turn this feature off by default.

Currently, we can only perform joins between two tables whose partitioning schemes are absolutely identical. It would be nice to cope with other scenarios, such as extra partitions on one side or the other with no match on the other side, but that will have to wait for a future patch.

Ashutosh Bapat, reviewed and tested by Rajkumar Raghuwanshi, Amit Langote, Rafia Sabih, Thomas Munro, Dilip Kumar, Antonin Houska, Amit Khandekar, and by me. A few final adjustments by me.

Discussion: http://postgr.es/m/CAFjFpRfQ8GrQvzp3jA2wnLqrHmaXna-urjm_UY9BqXj=EaDTSA@mail.gmail.com Discussion: http://postgr.es/m/CAFjFpRcitjfrULr5jfuKWRPsGUX0LQ0k8-yG0Qw2+1LBGNpMdw@mail.gmail.com

f49842d Basic partition-wise join functionality.
contrib/postgres_fdw/expected/postgres_fdw.out | 120 ++
contrib/postgres_fdw/sql/postgres_fdw.sql | 53 +
doc/src/sgml/config.sgml | 20 +
doc/src/sgml/fdwhandler.sgml | 20 +
src/backend/optimizer/README | 26 +
src/backend/optimizer/geqo/geqo_eval.c | 3 +
src/backend/optimizer/path/allpaths.c | 268 +++-
src/backend/optimizer/path/costsize.c | 1 +
src/backend/optimizer/path/joinpath.c | 102 +-
src/backend/optimizer/path/joinrels.c | 316 ++++-
src/backend/optimizer/plan/createplan.c | 35 +-
src/backend/optimizer/plan/planner.c | 22 +
src/backend/optimizer/plan/setrefs.c | 58 +-
src/backend/optimizer/prep/prepunion.c | 95 ++
src/backend/optimizer/util/pathnode.c | 363 +++++
src/backend/optimizer/util/placeholder.c | 58 +
src/backend/optimizer/util/plancat.c | 32 +-
src/backend/optimizer/util/relnode.c | 368 ++++-
src/backend/utils/misc/guc.c | 9 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/foreign/fdwapi.h | 6 +
src/include/nodes/extensible.h | 3 +
src/include/nodes/relation.h | 50 +-
src/include/optimizer/cost.h | 1 +
src/include/optimizer/pathnode.h | 6 +
src/include/optimizer/paths.h | 5 +
src/include/optimizer/placeholder.h | 2 +
src/include/optimizer/planner.h | 2 +
src/include/optimizer/prep.h | 6 +
src/test/regress/expected/partition_join.out | 1789 ++++++++++++++++++++++++
src/test/regress/expected/sysviews.out | 31 +-
src/test/regress/parallel_schedule | 3 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/partition_join.sql | 354 +++++
34 files changed, 4089 insertions(+), 140 deletions(-)

Upstream: git.postgresql.org


  • Share