[COMMITTERS] pgsql: Basic partition-wise join functionality. - Mailing list pgsql-committers

Basic partition-wise join functionality.

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

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/f49842d1ee31b976c681322f76025d7732e860f3

Modified Files
--------------
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(-)


--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers