From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix partitionwise join with partially-redundant join clauses
Date: Tue, 30 Jul 2024 06:53:53 +0000
Message-ID: <E1sYgk5-001rAN-3l@gemulon.postgresql.org> (raw)
Fix partitionwise join with partially-redundant join clauses
To determine if the two relations being joined can use partitionwise
join, we need to verify the existence of equi-join conditions
involving pairs of matching partition keys for all partition keys.
Currently we do that by looking through the join's restriction
clauses. However, it has been discovered that this approach is
insufficient, because there might be partition keys known equal by a
specific EC, but they do not form a join clause because it happens
that other members of the EC than the partition keys are constrained
to become a join clause.
To address this issue, in addition to examining the join's restriction
clauses, we also check if any partition keys are known equal by ECs,
by leveraging function exprs_known_equal(). To accomplish this, we
enhance exprs_known_equal() to check equality per the semantics of the
opfamily, if provided.
It could be argued that exprs_known_equal() could be called O(N^2)
times, where N is the number of partition key expressions, resulting
in noticeable performance costs if there are a lot of partition key
expressions. But I think this is not a problem. The number of a
joinrel's partition key expressions would only be equal to the join
degree, since each base relation within the join contributes only one
partition key expression. That is to say, it does not scale with the
number of partitions. A benchmark with a query involving 5-way joins
of partitioned tables, each with 3 partition keys and 1000 partitions,
shows that the planning time is not significantly affected by this
patch (within the margin of error), particularly when compared to the
impact caused by partitionwise join.
Thanks to Tom Lane for the idea of leveraging exprs_known_equal() to
check if partition keys are known equal by ECs.
Author: Richard Guo, Tom Lane
Reviewed-by: Tom Lane, Ashutosh Bapat, Robert Haas
Discussion: https://postgr.es/m/CAN_9JTzo_2F5dKLqXVtDX5V6dwqB0Xk+ihstpKEt3a1LT6X78A@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/9b282a9359a12831c087eba7f0f5f0b1dba7b7eb
Modified Files
--------------
src/backend/optimizer/path/equivclass.c | 26 +++++--
src/backend/optimizer/util/relnode.c | 103 ++++++++++++++++++++++++---
src/backend/utils/adt/selfuncs.c | 5 +-
src/include/optimizer/paths.h | 3 +-
src/test/regress/expected/partition_join.out | 88 +++++++++++++++++++++++
src/test/regress/sql/partition_join.sql | 10 +++
6 files changed, 214 insertions(+), 21 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix partitionwise join with partially-redundant join clauses
In-Reply-To: <E1sYgk5-001rAN-3l@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox