public inbox for [email protected]
help / color / mirror / Atom feedA problem about partitionwise join
32+ messages / 13 participants
[nested] [flat]
* A problem about partitionwise join
@ 2019-08-26 09:32 Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2019-08-26 09:32 UTC (permalink / raw)
To: pgsql-hackers
Hi All,
To generate partitionwise join, we need to make sure there exists an
equi-join condition for each pair of partition keys, which is performed
by have_partkey_equi_join(). This makes sense and works well.
But if, let's say, one certain pair of partition keys (foo.k = bar.k)
has formed an equivalence class containing consts, no join clause would
be generated for it, since we have already generated 'foo.k = const' and
'bar.k = const' and pushed them into the proper restrictions earlier.
This will make partitionwise join fail to be planned if there are
multiple partition keys and the pushed-down restrictions 'xxx = const'
fail to prune away any partitions.
Consider the examples below:
create table p (k1 int, k2 int, val int) partition by range(k1,k2);
create table p_1 partition of p for values from (1,1) to (10,100);
create table p_2 partition of p for values from (10,100) to (20,200);
If we are joining on each pair of partition keys, we can generate
partitionwise join:
# explain (costs off)
select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 = bar.k2;
QUERY PLAN
----------------------------------------------------------------------
Append
-> Hash Join
Hash Cond: ((foo.k1 = bar.k1) AND (foo.k2 = bar.k2))
-> Seq Scan on p_1 foo
-> Hash
-> Seq Scan on p_1 bar
-> Hash Join
Hash Cond: ((foo_1.k1 = bar_1.k1) AND (foo_1.k2 = bar_1.k2))
-> Seq Scan on p_2 foo_1
-> Hash
-> Seq Scan on p_2 bar_1
(11 rows)
But if we add another qual 'foo.k2 = const', we will be unable to
generate partitionwise join any more, because have_partkey_equi_join()
thinks not every partition key has an equi-join condition.
# explain (costs off)
select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 = bar.k2
and foo.k2 = 16;
QUERY PLAN
-----------------------------------------
Hash Join
Hash Cond: (foo.k1 = bar.k1)
-> Append
-> Seq Scan on p_1 foo
Filter: (k2 = 16)
-> Seq Scan on p_2 foo_1
Filter: (k2 = 16)
-> Hash
-> Append
-> Seq Scan on p_1 bar
Filter: (k2 = 16)
-> Seq Scan on p_2 bar_1
Filter: (k2 = 16)
(13 rows)
Is this a problem?
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-27 00:51 Amit Langote <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Amit Langote @ 2019-08-27 00:51 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: pgsql-hackers
Hi Richard,
On Mon, Aug 26, 2019 at 6:33 PM Richard Guo <[email protected]> wrote:
>
> Hi All,
>
> To generate partitionwise join, we need to make sure there exists an
> equi-join condition for each pair of partition keys, which is performed
> by have_partkey_equi_join(). This makes sense and works well.
>
> But if, let's say, one certain pair of partition keys (foo.k = bar.k)
> has formed an equivalence class containing consts, no join clause would
> be generated for it, since we have already generated 'foo.k = const' and
> 'bar.k = const' and pushed them into the proper restrictions earlier.
>
> This will make partitionwise join fail to be planned if there are
> multiple partition keys and the pushed-down restrictions 'xxx = const'
> fail to prune away any partitions.
>
> Consider the examples below:
>
> create table p (k1 int, k2 int, val int) partition by range(k1,k2);
> create table p_1 partition of p for values from (1,1) to (10,100);
> create table p_2 partition of p for values from (10,100) to (20,200);
>
> If we are joining on each pair of partition keys, we can generate
> partitionwise join:
>
> # explain (costs off)
> select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 = bar.k2;
> QUERY PLAN
> ----------------------------------------------------------------------
> Append
> -> Hash Join
> Hash Cond: ((foo.k1 = bar.k1) AND (foo.k2 = bar.k2))
> -> Seq Scan on p_1 foo
> -> Hash
> -> Seq Scan on p_1 bar
> -> Hash Join
> Hash Cond: ((foo_1.k1 = bar_1.k1) AND (foo_1.k2 = bar_1.k2))
> -> Seq Scan on p_2 foo_1
> -> Hash
> -> Seq Scan on p_2 bar_1
> (11 rows)
>
> But if we add another qual 'foo.k2 = const', we will be unable to
> generate partitionwise join any more, because have_partkey_equi_join()
> thinks not every partition key has an equi-join condition.
>
> # explain (costs off)
> select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 = bar.k2 and foo.k2 = 16;
> QUERY PLAN
> -----------------------------------------
> Hash Join
> Hash Cond: (foo.k1 = bar.k1)
> -> Append
> -> Seq Scan on p_1 foo
> Filter: (k2 = 16)
> -> Seq Scan on p_2 foo_1
> Filter: (k2 = 16)
> -> Hash
> -> Append
> -> Seq Scan on p_1 bar
> Filter: (k2 = 16)
> -> Seq Scan on p_2 bar_1
> Filter: (k2 = 16)
> (13 rows)
>
> Is this a problem?
Perhaps. Maybe it has to do with the way have_partkey_equi_join() has
been coded. If it was coded such that it figured out on its own that
the equivalence (foo.k2, bar.k2, ...) does exist, then that would
allow partitionwise join to occur, which I think would be OK to do.
But maybe I'm missing something.
Thanks,
Amit
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-27 07:56 Richard Guo <[email protected]>
parent: Amit Langote <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2019-08-27 07:56 UTC (permalink / raw)
To: Amit Langote <[email protected]>; +Cc: pgsql-hackers
On Tue, Aug 27, 2019 at 8:51 AM Amit Langote <[email protected]>
wrote:
> Hi Richard,
>
> On Mon, Aug 26, 2019 at 6:33 PM Richard Guo <[email protected]> wrote:
> >
> > Hi All,
> >
> > To generate partitionwise join, we need to make sure there exists an
> > equi-join condition for each pair of partition keys, which is performed
> > by have_partkey_equi_join(). This makes sense and works well.
> >
> > But if, let's say, one certain pair of partition keys (foo.k = bar.k)
> > has formed an equivalence class containing consts, no join clause would
> > be generated for it, since we have already generated 'foo.k = const' and
> > 'bar.k = const' and pushed them into the proper restrictions earlier.
> >
> > This will make partitionwise join fail to be planned if there are
> > multiple partition keys and the pushed-down restrictions 'xxx = const'
> > fail to prune away any partitions.
> >
> > Consider the examples below:
> >
> > create table p (k1 int, k2 int, val int) partition by range(k1,k2);
> > create table p_1 partition of p for values from (1,1) to (10,100);
> > create table p_2 partition of p for values from (10,100) to (20,200);
> >
> > If we are joining on each pair of partition keys, we can generate
> > partitionwise join:
> >
> > # explain (costs off)
> > select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 =
> bar.k2;
> > QUERY PLAN
> > ----------------------------------------------------------------------
> > Append
> > -> Hash Join
> > Hash Cond: ((foo.k1 = bar.k1) AND (foo.k2 = bar.k2))
> > -> Seq Scan on p_1 foo
> > -> Hash
> > -> Seq Scan on p_1 bar
> > -> Hash Join
> > Hash Cond: ((foo_1.k1 = bar_1.k1) AND (foo_1.k2 = bar_1.k2))
> > -> Seq Scan on p_2 foo_1
> > -> Hash
> > -> Seq Scan on p_2 bar_1
> > (11 rows)
> >
> > But if we add another qual 'foo.k2 = const', we will be unable to
> > generate partitionwise join any more, because have_partkey_equi_join()
> > thinks not every partition key has an equi-join condition.
> >
> > # explain (costs off)
> > select * from p as foo join p as bar on foo.k1 = bar.k1 and foo.k2 =
> bar.k2 and foo.k2 = 16;
> > QUERY PLAN
> > -----------------------------------------
> > Hash Join
> > Hash Cond: (foo.k1 = bar.k1)
> > -> Append
> > -> Seq Scan on p_1 foo
> > Filter: (k2 = 16)
> > -> Seq Scan on p_2 foo_1
> > Filter: (k2 = 16)
> > -> Hash
> > -> Append
> > -> Seq Scan on p_1 bar
> > Filter: (k2 = 16)
> > -> Seq Scan on p_2 bar_1
> > Filter: (k2 = 16)
> > (13 rows)
> >
> > Is this a problem?
>
> Perhaps. Maybe it has to do with the way have_partkey_equi_join() has
> been coded. If it was coded such that it figured out on its own that
> the equivalence (foo.k2, bar.k2, ...) does exist, then that would
> allow partitionwise join to occur, which I think would be OK to do.
> But maybe I'm missing something.
>
>
This should be caused by how we deduce join clauses from equivalence
classes. ECs containing consts will not be considered so we cannot
generate (foo.k2 = bar.k2) for the query above.
In addition, when generating join clauses from equivalence classes, we
only select the joinclause with the 'best score', or the first
joinclause with a score of 3. This may make us miss some joinclause on
partition keys.
Check the query below as a more illustrative example:
create table p (k int, val int) partition by range(k);
create table p_1 partition of p for values from (1) to (10);
create table p_2 partition of p for values from (10) to (100);
If we use quals 'foo.k = bar.k and foo.k = bar.val', we can generate
partitionwise join:
# explain (costs off)
select * from p as foo join p as bar on foo.k = bar.k and foo.k = bar.val;
QUERY PLAN
-----------------------------------------
Append
-> Hash Join
Hash Cond: (foo.k = bar.k)
-> Seq Scan on p_1 foo
-> Hash
-> Seq Scan on p_1 bar
Filter: (k = val)
-> Hash Join
Hash Cond: (foo_1.k = bar_1.k)
-> Seq Scan on p_2 foo_1
-> Hash
-> Seq Scan on p_2 bar_1
Filter: (k = val)
(13 rows)
But if we exchange the order of the two quals to 'foo.k = bar.val and
foo.k = bar.k', then partitionwise join cannot be generated any more,
because we only have joinclause 'foo.k = bar.val' as it first reached
score of 3. We have missed the joinclause on the partition key although
it does exist.
# explain (costs off)
select * from p as foo join p as bar on foo.k = bar.val and foo.k = bar.k;
QUERY PLAN
-----------------------------------------
Hash Join
Hash Cond: (foo.k = bar.val)
-> Append
-> Seq Scan on p_1 foo
-> Seq Scan on p_2 foo_1
-> Hash
-> Append
-> Seq Scan on p_1 bar
Filter: (val = k)
-> Seq Scan on p_2 bar_1
Filter: (val = k)
(11 rows)
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-28 10:49 Etsuro Fujita <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Etsuro Fujita @ 2019-08-28 10:49 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers
Hi,
On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <[email protected]> wrote:
> Check the query below as a more illustrative example:
>
> create table p (k int, val int) partition by range(k);
> create table p_1 partition of p for values from (1) to (10);
> create table p_2 partition of p for values from (10) to (100);
>
> If we use quals 'foo.k = bar.k and foo.k = bar.val', we can generate
> partitionwise join:
>
> # explain (costs off)
> select * from p as foo join p as bar on foo.k = bar.k and foo.k = bar.val;
> QUERY PLAN
> -----------------------------------------
> Append
> -> Hash Join
> Hash Cond: (foo.k = bar.k)
> -> Seq Scan on p_1 foo
> -> Hash
> -> Seq Scan on p_1 bar
> Filter: (k = val)
> -> Hash Join
> Hash Cond: (foo_1.k = bar_1.k)
> -> Seq Scan on p_2 foo_1
> -> Hash
> -> Seq Scan on p_2 bar_1
> Filter: (k = val)
> (13 rows)
>
> But if we exchange the order of the two quals to 'foo.k = bar.val and
> foo.k = bar.k', then partitionwise join cannot be generated any more,
> because we only have joinclause 'foo.k = bar.val' as it first reached
> score of 3. We have missed the joinclause on the partition key although
> it does exist.
>
> # explain (costs off)
> select * from p as foo join p as bar on foo.k = bar.val and foo.k = bar.k;
> QUERY PLAN
> -----------------------------------------
> Hash Join
> Hash Cond: (foo.k = bar.val)
> -> Append
> -> Seq Scan on p_1 foo
> -> Seq Scan on p_2 foo_1
> -> Hash
> -> Append
> -> Seq Scan on p_1 bar
> Filter: (val = k)
> -> Seq Scan on p_2 bar_1
> Filter: (val = k)
> (11 rows)
I think it would be nice if we can address this issue.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-29 09:44 Richard Guo <[email protected]>
parent: Etsuro Fujita <[email protected]>
0 siblings, 3 replies; 32+ messages in thread
From: Richard Guo @ 2019-08-29 09:44 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers
On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <[email protected]>
wrote:
> Hi,
>
> On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <[email protected]> wrote:
> > Check the query below as a more illustrative example:
> >
> > create table p (k int, val int) partition by range(k);
> > create table p_1 partition of p for values from (1) to (10);
> > create table p_2 partition of p for values from (10) to (100);
> >
> > If we use quals 'foo.k = bar.k and foo.k = bar.val', we can generate
> > partitionwise join:
> >
> > # explain (costs off)
> > select * from p as foo join p as bar on foo.k = bar.k and foo.k =
> bar.val;
> > QUERY PLAN
> > -----------------------------------------
> > Append
> > -> Hash Join
> > Hash Cond: (foo.k = bar.k)
> > -> Seq Scan on p_1 foo
> > -> Hash
> > -> Seq Scan on p_1 bar
> > Filter: (k = val)
> > -> Hash Join
> > Hash Cond: (foo_1.k = bar_1.k)
> > -> Seq Scan on p_2 foo_1
> > -> Hash
> > -> Seq Scan on p_2 bar_1
> > Filter: (k = val)
> > (13 rows)
> >
> > But if we exchange the order of the two quals to 'foo.k = bar.val and
> > foo.k = bar.k', then partitionwise join cannot be generated any more,
> > because we only have joinclause 'foo.k = bar.val' as it first reached
> > score of 3. We have missed the joinclause on the partition key although
> > it does exist.
> >
> > # explain (costs off)
> > select * from p as foo join p as bar on foo.k = bar.val and foo.k =
> bar.k;
> > QUERY PLAN
> > -----------------------------------------
> > Hash Join
> > Hash Cond: (foo.k = bar.val)
> > -> Append
> > -> Seq Scan on p_1 foo
> > -> Seq Scan on p_2 foo_1
> > -> Hash
> > -> Append
> > -> Seq Scan on p_1 bar
> > Filter: (val = k)
> > -> Seq Scan on p_2 bar_1
> > Filter: (val = k)
> > (11 rows)
>
> I think it would be nice if we can address this issue.
>
Thank you.
Attached is a patch as an attempt to address this issue. The idea is
quite straightforward. When building partition info for joinrel, we
generate any possible EC-derived joinclauses of form 'outer_em =
inner_em', which will be used together with the original restrictlist to
check if there exists an equi-join condition for each pair of partition
keys.
Any comments are welcome!
Thanks
Richard
Attachments:
[application/octet-stream] v1-0001-Fix-up-partitionwise-join.patch (9.7K, ../../CAN_9JTxC8JdpCDDY0ic-VqQ4fbUGS9O_xas1pU6aXF4Q8imcKA@mail.gmail.com/3-v1-0001-Fix-up-partitionwise-join.patch)
download | inline diff:
From 4f8c49b871386b45393d93ff8c220214a44c7650 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Wed, 28 Aug 2019 14:48:54 +0000
Subject: [PATCH] Fix up partitionwise join.
---
src/backend/optimizer/path/equivclass.c | 102 +++++++++++++++++++++++++++
src/backend/optimizer/util/relnode.c | 19 +++--
src/include/optimizer/paths.h | 4 ++
src/test/regress/expected/partition_join.out | 38 ++++++++++
src/test/regress/sql/partition_join.sql | 4 ++
5 files changed, 161 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index ccc07ba..d6eb739 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1197,6 +1197,108 @@ generate_join_implied_equalities(PlannerInfo *root,
}
/*
+ * generate_join_implied_equalities_for_all
+ * Create any EC-derived joinclauses of form 'outer_em = inner_em'.
+ *
+ * This is used when building partition info for joinrel.
+ */
+List *
+generate_join_implied_equalities_for_all(PlannerInfo *root,
+ Relids join_relids,
+ Relids outer_relids,
+ Relids inner_relids)
+{
+ List *result = NIL;
+ Bitmapset * matching_ecs;
+ int i;
+
+ /*
+ * Get all eclasses in common between inner_relids and outer_relids
+ */
+ matching_ecs = get_common_eclass_indexes(root, inner_relids,
+ outer_relids);
+
+ i = -1;
+ while ((i = bms_next_member(matching_ecs, i)) >= 0)
+ {
+ EquivalenceClass *ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
+ List *outer_members = NIL;
+ List *inner_members = NIL;
+ ListCell *lc1;
+
+ /* Do not consider this EC if it's ec_broken */
+ if (ec->ec_broken)
+ continue;
+
+ /* Single-member ECs won't generate any deductions */
+ if (list_length(ec->ec_members) <= 1)
+ continue;
+
+ /*
+ * First, scan the EC to identify member values that are computable at the
+ * outer rel or at the inner rel.
+ */
+ foreach(lc1, ec->ec_members)
+ {
+ EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc1);
+
+ /*
+ * We don't need to check explicitly for child EC members. This test
+ * against join_relids will cause them to be ignored except when
+ * considering a child inner rel, which is what we want.
+ */
+ if (!bms_is_subset(cur_em->em_relids, join_relids))
+ continue; /* not computable yet, or wrong child */
+
+ if (bms_is_subset(cur_em->em_relids, outer_relids))
+ outer_members = lappend(outer_members, cur_em);
+ else if (bms_is_subset(cur_em->em_relids, inner_relids))
+ inner_members = lappend(inner_members, cur_em);
+ }
+
+ /*
+ * First, select the joinclause if needed. We can equate any one outer
+ * member to any one inner member, since we know this EC is not
+ * ec_broken.
+ */
+ if (outer_members && inner_members)
+ {
+ RestrictInfo *rinfo;
+
+ foreach(lc1, outer_members)
+ {
+ EquivalenceMember *outer_em = (EquivalenceMember *) lfirst(lc1);
+ ListCell *lc2;
+
+ foreach(lc2, inner_members)
+ {
+ EquivalenceMember *inner_em = (EquivalenceMember *) lfirst(lc2);
+ Oid eq_op;
+
+ eq_op = select_equality_operator(ec,
+ outer_em->em_datatype,
+ inner_em->em_datatype);
+ if (!OidIsValid(eq_op))
+ continue;
+
+ /*
+ * Create clause, setting parent_ec to mark it as redundant with other
+ * joinclauses
+ */
+ rinfo = create_join_clause(root, ec, eq_op,
+ outer_em, inner_em,
+ ec);
+
+ result = lappend(result, rinfo);
+ }
+ }
+ }
+ }
+
+ return result;
+}
+
+/*
* generate_join_implied_equalities_for_ecs
* As above, but consider only the listed ECs.
*/
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 8541538..07b49d3 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -55,7 +55,7 @@ static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
static void set_foreign_rel_properties(RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel);
static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
-static void build_joinrel_partition_info(RelOptInfo *joinrel,
+static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
List *restrictlist, JoinType jointype);
static void build_child_join_reltarget(PlannerInfo *root,
@@ -706,7 +706,7 @@ build_join_rel(PlannerInfo *root,
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
/* Store the partition information. */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel, restrictlist,
sjinfo->jointype);
/*
@@ -870,7 +870,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->has_eclass_joins = parent_joinrel->has_eclass_joins;
/* Is the join between partitions itself partitioned? */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel, restrictlist,
jointype);
/* Child joinrel is parallel safe if parent is parallel safe. */
@@ -1600,9 +1600,9 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer)
* the join relation.
*/
static void
-build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
- RelOptInfo *inner_rel, List *restrictlist,
- JoinType jointype)
+build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
+ RelOptInfo *outer_rel, RelOptInfo *inner_rel,
+ List *restrictlist, JoinType jointype)
{
int partnatts;
int cnt;
@@ -1615,6 +1615,13 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
return;
}
+ restrictlist =
+ list_concat_unique_ptr(generate_join_implied_equalities_for_all(root,
+ joinrel->relids,
+ outer_rel->relids,
+ inner_rel->relids),
+ restrictlist);
+
/*
* We can only consider this join as an input to further partitionwise
* joins if (a) the input relations are partitioned and have
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7345137..b640b66 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -140,6 +140,10 @@ extern List *generate_join_implied_equalities(PlannerInfo *root,
Relids join_relids,
Relids outer_relids,
RelOptInfo *inner_rel);
+extern List *generate_join_implied_equalities_for_all(PlannerInfo *root,
+ Relids join_relids,
+ Relids outer_relids,
+ Relids inner_relids);
extern List *generate_join_implied_equalities_for_ecs(PlannerInfo *root,
List *eclasses,
Relids join_relids,
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 1296edc..e39cf11 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -62,6 +62,44 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
450 | 0450 | 450 | 0450
(4 rows)
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ QUERY PLAN
+-------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Merge Join
+ Merge Cond: (t1.a = t2.a)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1
+ -> Sort
+ Sort Key: t2.b
+ -> Seq Scan on prt2_p1 t2
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_1.a = t2_1.a)
+ -> Seq Scan on prt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_1
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2_2.a)
+ -> Seq Scan on prt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_2
+ Filter: (a = b)
+(22 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ a | c | b | c
+----+------+----+------
+ 0 | 0000 | 0 | 0000
+ 6 | 0006 | 6 | 0006
+ 12 | 0012 | 12 | 0012
+ 18 | 0018 | 18 | 0018
+ 24 | 0024 | 24 | 0024
+(5 rows)
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index db9a6b4..dfa2515 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -34,6 +34,10 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
--
2.7.4
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-29 18:08 Etsuro Fujita <[email protected]>
parent: Richard Guo <[email protected]>
2 siblings, 2 replies; 32+ messages in thread
From: Etsuro Fujita @ 2019-08-29 18:08 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers
On Thu, Aug 29, 2019 at 6:45 PM Richard Guo <[email protected]> wrote:
> On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <[email protected]> wrote:
>> On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <[email protected]> wrote:
>> > Check the query below as a more illustrative example:
>> >
>> > create table p (k int, val int) partition by range(k);
>> > create table p_1 partition of p for values from (1) to (10);
>> > create table p_2 partition of p for values from (10) to (100);
>> >
>> > If we use quals 'foo.k = bar.k and foo.k = bar.val', we can generate
>> > partitionwise join:
>> >
>> > # explain (costs off)
>> > select * from p as foo join p as bar on foo.k = bar.k and foo.k = bar.val;
>> > QUERY PLAN
>> > -----------------------------------------
>> > Append
>> > -> Hash Join
>> > Hash Cond: (foo.k = bar.k)
>> > -> Seq Scan on p_1 foo
>> > -> Hash
>> > -> Seq Scan on p_1 bar
>> > Filter: (k = val)
>> > -> Hash Join
>> > Hash Cond: (foo_1.k = bar_1.k)
>> > -> Seq Scan on p_2 foo_1
>> > -> Hash
>> > -> Seq Scan on p_2 bar_1
>> > Filter: (k = val)
>> > (13 rows)
>> >
>> > But if we exchange the order of the two quals to 'foo.k = bar.val and
>> > foo.k = bar.k', then partitionwise join cannot be generated any more,
>> > because we only have joinclause 'foo.k = bar.val' as it first reached
>> > score of 3. We have missed the joinclause on the partition key although
>> > it does exist.
>> >
>> > # explain (costs off)
>> > select * from p as foo join p as bar on foo.k = bar.val and foo.k = bar.k;
>> > QUERY PLAN
>> > -----------------------------------------
>> > Hash Join
>> > Hash Cond: (foo.k = bar.val)
>> > -> Append
>> > -> Seq Scan on p_1 foo
>> > -> Seq Scan on p_2 foo_1
>> > -> Hash
>> > -> Append
>> > -> Seq Scan on p_1 bar
>> > Filter: (val = k)
>> > -> Seq Scan on p_2 bar_1
>> > Filter: (val = k)
>> > (11 rows)
>>
>> I think it would be nice if we can address this issue.
> Attached is a patch as an attempt to address this issue. The idea is
> quite straightforward. When building partition info for joinrel, we
> generate any possible EC-derived joinclauses of form 'outer_em =
> inner_em', which will be used together with the original restrictlist to
> check if there exists an equi-join condition for each pair of partition
> keys.
Thank you for the patch! Will review. Could you add the patch to the
upcoming CF so that it doesn’t get lost?
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-30 03:15 Richard Guo <[email protected]>
parent: Etsuro Fujita <[email protected]>
1 sibling, 1 reply; 32+ messages in thread
From: Richard Guo @ 2019-08-30 03:15 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers
On Fri, Aug 30, 2019 at 2:08 AM Etsuro Fujita <[email protected]>
wrote:
> On Thu, Aug 29, 2019 at 6:45 PM Richard Guo <[email protected]> wrote:
> > On Wed, Aug 28, 2019 at 6:49 PM Etsuro Fujita <[email protected]>
> wrote:
> >> On Tue, Aug 27, 2019 at 4:57 PM Richard Guo <[email protected]> wrote:
> >> > Check the query below as a more illustrative example:
> >> >
> >> > create table p (k int, val int) partition by range(k);
> >> > create table p_1 partition of p for values from (1) to (10);
> >> > create table p_2 partition of p for values from (10) to (100);
> >> >
> >> > If we use quals 'foo.k = bar.k and foo.k = bar.val', we can generate
> >> > partitionwise join:
> >> >
> >> > # explain (costs off)
> >> > select * from p as foo join p as bar on foo.k = bar.k and foo.k =
> bar.val;
> >> > QUERY PLAN
> >> > -----------------------------------------
> >> > Append
> >> > -> Hash Join
> >> > Hash Cond: (foo.k = bar.k)
> >> > -> Seq Scan on p_1 foo
> >> > -> Hash
> >> > -> Seq Scan on p_1 bar
> >> > Filter: (k = val)
> >> > -> Hash Join
> >> > Hash Cond: (foo_1.k = bar_1.k)
> >> > -> Seq Scan on p_2 foo_1
> >> > -> Hash
> >> > -> Seq Scan on p_2 bar_1
> >> > Filter: (k = val)
> >> > (13 rows)
> >> >
> >> > But if we exchange the order of the two quals to 'foo.k = bar.val and
> >> > foo.k = bar.k', then partitionwise join cannot be generated any more,
> >> > because we only have joinclause 'foo.k = bar.val' as it first reached
> >> > score of 3. We have missed the joinclause on the partition key
> although
> >> > it does exist.
> >> >
> >> > # explain (costs off)
> >> > select * from p as foo join p as bar on foo.k = bar.val and foo.k =
> bar.k;
> >> > QUERY PLAN
> >> > -----------------------------------------
> >> > Hash Join
> >> > Hash Cond: (foo.k = bar.val)
> >> > -> Append
> >> > -> Seq Scan on p_1 foo
> >> > -> Seq Scan on p_2 foo_1
> >> > -> Hash
> >> > -> Append
> >> > -> Seq Scan on p_1 bar
> >> > Filter: (val = k)
> >> > -> Seq Scan on p_2 bar_1
> >> > Filter: (val = k)
> >> > (11 rows)
> >>
> >> I think it would be nice if we can address this issue.
>
> > Attached is a patch as an attempt to address this issue. The idea is
> > quite straightforward. When building partition info for joinrel, we
> > generate any possible EC-derived joinclauses of form 'outer_em =
> > inner_em', which will be used together with the original restrictlist to
> > check if there exists an equi-join condition for each pair of partition
> > keys.
>
> Thank you for the patch! Will review. Could you add the patch to the
> upcoming CF so that it doesn’t get lost?
>
Added this patch: https://commitfest.postgresql.org/24/2266/
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-08-30 06:13 Etsuro Fujita <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Etsuro Fujita @ 2019-08-30 06:13 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers
On Fri, Aug 30, 2019 at 12:15 PM Richard Guo <[email protected]> wrote:
> On Fri, Aug 30, 2019 at 2:08 AM Etsuro Fujita <[email protected]> wrote:
>> On Thu, Aug 29, 2019 at 6:45 PM Richard Guo <[email protected]> wrote:
>> > Attached is a patch as an attempt to address this issue. The idea is
>> > quite straightforward. When building partition info for joinrel, we
>> > generate any possible EC-derived joinclauses of form 'outer_em =
>> > inner_em', which will be used together with the original restrictlist to
>> > check if there exists an equi-join condition for each pair of partition
>> > keys.
>> Could you add the patch to the
>> upcoming CF so that it doesn’t get lost?
> Added this patch: https://commitfest.postgresql.org/24/2266/
Thanks!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-09-10 20:48 Alvaro Herrera from 2ndQuadrant <[email protected]>
parent: Richard Guo <[email protected]>
2 siblings, 1 reply; 32+ messages in thread
From: Alvaro Herrera from 2ndQuadrant @ 2019-09-10 20:48 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
So in this patch, the input restrictlist is modified to include the
clauses generated by generate_join_implied_equalities_for_all. That
doesn't seem okay -- doesn't it affect downstream usage of the
restrictlist in the caller of set_joinrel_size_estimates?
I wonder if it's possible to do this by using the ECs directly in
have_partkey_equi_join instead of using them to create fake join
clauses.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-09-11 03:56 Richard Guo <[email protected]>
parent: Alvaro Herrera from 2ndQuadrant <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Richard Guo @ 2019-09-11 03:56 UTC (permalink / raw)
To: Alvaro Herrera from 2ndQuadrant <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
Hi Alvaro,
Thank you for reviewing this patch.
On Wed, Sep 11, 2019 at 4:48 AM Alvaro Herrera from 2ndQuadrant <
[email protected]> wrote:
> So in this patch, the input restrictlist is modified to include the
> clauses generated by generate_join_implied_equalities_for_all. That
> doesn't seem okay -- doesn't it affect downstream usage of the
> restrictlist in the caller of set_joinrel_size_estimates?
>
Actually the joinclauses generated by
generate_join_implied_equalities_for_all only affects the restrictlist
used in have_partkey_equi_join to check equi-join conditions for
partition keys. The input restrictlist would not be altered.
>
> I wonder if it's possible to do this by using the ECs directly in
> have_partkey_equi_join instead of using them to create fake join
> clauses.
>
Hmm.. I thought about this option and at last figured that what we need
to do in have_partkey_equi_join with the ECs is actually the same as in
generate_join_implied_equalities_for_all. Maybe I didn't think it
correctly.
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-09-20 04:48 Dilip Kumar <[email protected]>
parent: Richard Guo <[email protected]>
2 siblings, 1 reply; 32+ messages in thread
From: Dilip Kumar @ 2019-09-20 04:48 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Thu, Aug 29, 2019 at 3:15 PM Richard Guo <[email protected]> wrote:
>
>
> Attached is a patch as an attempt to address this issue. The idea is
> quite straightforward. When building partition info for joinrel, we
> generate any possible EC-derived joinclauses of form 'outer_em =
> inner_em', which will be used together with the original restrictlist to
> check if there exists an equi-join condition for each pair of partition
> keys.
>
> Any comments are welcome!
/*
+ * generate_join_implied_equalities_for_all
+ * Create any EC-derived joinclauses of form 'outer_em = inner_em'.
+ *
+ * This is used when building partition info for joinrel.
+ */
+List *
+generate_join_implied_equalities_for_all(PlannerInfo *root,
+ Relids join_relids,
+ Relids outer_relids,
+ Relids inner_relids)
I think we need to have more detailed comments about why we need this
separate function, we can also explain that
generate_join_implied_equalities function will avoid
the join clause if EC has the constant but for partition-wise join, we
need that clause too.
+ while ((i = bms_next_member(matching_ecs, i)) >= 0)
+ {
+ EquivalenceClass *ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
+ List *outer_members = NIL;
+ List *inner_members = NIL;
+ ListCell *lc1;
+
+ /* Do not consider this EC if it's ec_broken */
+ if (ec->ec_broken)
+ continue;
+
+ /* Single-member ECs won't generate any deductions */
+ if (list_length(ec->ec_members) <= 1)
+ continue;
+
I am wondering isn't it possible to just process the missing join
clause? I mean 'generate_join_implied_equalities' has only skipped
the ECs which has const so
can't we create join clause only for those ECs and append it the
"Restrictlist" we already have? I might be missing something?
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-09-20 09:02 Richard Guo <[email protected]>
parent: Dilip Kumar <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2019-09-20 09:02 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
Hi Dilip,
Thank you for reviewing this patch.
On Fri, Sep 20, 2019 at 12:48 PM Dilip Kumar <[email protected]> wrote:
> On Thu, Aug 29, 2019 at 3:15 PM Richard Guo <[email protected]> wrote:
> >
> >
> > Attached is a patch as an attempt to address this issue. The idea is
> > quite straightforward. When building partition info for joinrel, we
> > generate any possible EC-derived joinclauses of form 'outer_em =
> > inner_em', which will be used together with the original restrictlist to
> > check if there exists an equi-join condition for each pair of partition
> > keys.
> >
> > Any comments are welcome!
> /*
> + * generate_join_implied_equalities_for_all
> + * Create any EC-derived joinclauses of form 'outer_em = inner_em'.
> + *
> + * This is used when building partition info for joinrel.
> + */
> +List *
> +generate_join_implied_equalities_for_all(PlannerInfo *root,
> + Relids join_relids,
> + Relids outer_relids,
> + Relids inner_relids)
>
> I think we need to have more detailed comments about why we need this
> separate function, we can also explain that
> generate_join_implied_equalities function will avoid
> the join clause if EC has the constant but for partition-wise join, we
> need that clause too.
>
Thank you for the suggestion.
>
> + while ((i = bms_next_member(matching_ecs, i)) >= 0)
> + {
> + EquivalenceClass *ec = (EquivalenceClass *) list_nth(root->eq_classes,
> i);
> + List *outer_members = NIL;
> + List *inner_members = NIL;
> + ListCell *lc1;
> +
> + /* Do not consider this EC if it's ec_broken */
> + if (ec->ec_broken)
> + continue;
> +
> + /* Single-member ECs won't generate any deductions */
> + if (list_length(ec->ec_members) <= 1)
> + continue;
> +
>
> I am wondering isn't it possible to just process the missing join
> clause? I mean 'generate_join_implied_equalities' has only skipped
> the ECs which has const so
> can't we create join clause only for those ECs and append it the
> "Restrictlist" we already have? I might be missing something?
>
For ECs without const, 'generate_join_implied_equalities' also has
skipped some join clauses since it only selects the joinclause with
'best_score' between outer members and inner members. And the missing
join clauses are needed to generate partitionwise join. That's why the
query below cannot be planned as partitionwise join, as we have missed
joinclause 'foo.k = bar.k'.
select * from p as foo join p as bar on foo.k = bar.val and foo.k = bar.k;
And yes 'generate_join_implied_equalities_for_all' will create join
clauses that have existed in restrictlist. I think it's OK since the
same RestrictInfo deduced from EC will share the same pointer and
list_concat_unique_ptr will make sure there are no duplicates in the
restrictlist used by have_partkey_equi_join.
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-09-21 06:28 Dilip Kumar <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Dilip Kumar @ 2019-09-21 06:28 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Fri, Sep 20, 2019 at 2:33 PM Richard Guo <[email protected]> wrote:
>
> Hi Dilip,
>
> Thank you for reviewing this patch.
>
> On Fri, Sep 20, 2019 at 12:48 PM Dilip Kumar <[email protected]> wrote:
>>
>> On Thu, Aug 29, 2019 at 3:15 PM Richard Guo <[email protected]> wrote:
>> >
>> >
>> > Attached is a patch as an attempt to address this issue. The idea is
>> > quite straightforward. When building partition info for joinrel, we
>> > generate any possible EC-derived joinclauses of form 'outer_em =
>> > inner_em', which will be used together with the original restrictlist to
>> > check if there exists an equi-join condition for each pair of partition
>> > keys.
>> >
>> > Any comments are welcome!
>> /*
>> + * generate_join_implied_equalities_for_all
>> + * Create any EC-derived joinclauses of form 'outer_em = inner_em'.
>> + *
>> + * This is used when building partition info for joinrel.
>> + */
>> +List *
>> +generate_join_implied_equalities_for_all(PlannerInfo *root,
>> + Relids join_relids,
>> + Relids outer_relids,
>> + Relids inner_relids)
>>
>> I think we need to have more detailed comments about why we need this
>> separate function, we can also explain that
>> generate_join_implied_equalities function will avoid
>> the join clause if EC has the constant but for partition-wise join, we
>> need that clause too.
>
>
> Thank you for the suggestion.
>
>>
>>
>> + while ((i = bms_next_member(matching_ecs, i)) >= 0)
>> + {
>> + EquivalenceClass *ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
>> + List *outer_members = NIL;
>> + List *inner_members = NIL;
>> + ListCell *lc1;
>> +
>> + /* Do not consider this EC if it's ec_broken */
>> + if (ec->ec_broken)
>> + continue;
>> +
>> + /* Single-member ECs won't generate any deductions */
>> + if (list_length(ec->ec_members) <= 1)
>> + continue;
>> +
>>
>> I am wondering isn't it possible to just process the missing join
>> clause? I mean 'generate_join_implied_equalities' has only skipped
>> the ECs which has const so
>> can't we create join clause only for those ECs and append it the
>> "Restrictlist" we already have? I might be missing something?
>
>
> For ECs without const, 'generate_join_implied_equalities' also has
> skipped some join clauses since it only selects the joinclause with
> 'best_score' between outer members and inner members. And the missing
> join clauses are needed to generate partitionwise join. That's why the
> query below cannot be planned as partitionwise join, as we have missed
> joinclause 'foo.k = bar.k'.
oh right. I missed that part.
> select * from p as foo join p as bar on foo.k = bar.val and foo.k = bar.k;
>
> And yes 'generate_join_implied_equalities_for_all' will create join
> clauses that have existed in restrictlist. I think it's OK since the
> same RestrictInfo deduced from EC will share the same pointer and
> list_concat_unique_ptr will make sure there are no duplicates in the
> restrictlist used by have_partkey_equi_join.
>
ok
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-11-26 11:35 Etsuro Fujita <[email protected]>
parent: Etsuro Fujita <[email protected]>
1 sibling, 1 reply; 32+ messages in thread
From: Etsuro Fujita @ 2019-11-26 11:35 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Amit Langote <[email protected]>; pgsql-hackers
Hi Richard,
On Fri, Aug 30, 2019 at 3:08 AM Etsuro Fujita <[email protected]> wrote:
> On Thu, Aug 29, 2019 at 6:45 PM Richard Guo <[email protected]> wrote:
> > Attached is a patch as an attempt to address this issue. The idea is
> > quite straightforward. When building partition info for joinrel, we
> > generate any possible EC-derived joinclauses of form 'outer_em =
> > inner_em', which will be used together with the original restrictlist to
> > check if there exists an equi-join condition for each pair of partition
> > keys.
>
> Will review.
I've just started reviewing this patch. One comment I have for now
is: this is categorized into Bug Fixes, but we have a workaround at
least to the regression test case in the patch (ie, just reorder join
clauses), so this seems to me more like an improvement than a bug fix.
Sorry for the delay.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-11-29 03:03 Michael Paquier <[email protected]>
parent: Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Michael Paquier @ 2019-11-29 03:03 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Richard Guo <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Tue, Nov 26, 2019 at 08:35:33PM +0900, Etsuro Fujita wrote:
> I've just started reviewing this patch. One comment I have for now
> is: this is categorized into Bug Fixes, but we have a workaround at
> least to the regression test case in the patch (ie, just reorder join
> clauses), so this seems to me more like an improvement than a bug fix.
Hmm. Agreed. Changed the category and moved to next CF.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-11-29 03:07 Richard Guo <[email protected]>
parent: Michael Paquier <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2019-11-29 03:07 UTC (permalink / raw)
To: Michael Paquier <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Fri, Nov 29, 2019 at 11:03 AM Michael Paquier <[email protected]>
wrote:
> On Tue, Nov 26, 2019 at 08:35:33PM +0900, Etsuro Fujita wrote:
> > I've just started reviewing this patch. One comment I have for now
> > is: this is categorized into Bug Fixes, but we have a workaround at
> > least to the regression test case in the patch (ie, just reorder join
> > clauses), so this seems to me more like an improvement than a bug fix.
>
> Hmm. Agreed. Changed the category and moved to next CF.
>
Thanks Etsuro for the comment and thanks Michael for the change.
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2019-11-29 03:35 Etsuro Fujita <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Etsuro Fujita @ 2019-11-29 03:35 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Fri, Nov 29, 2019 at 12:08 PM Richard Guo <[email protected]> wrote:
> On Fri, Nov 29, 2019 at 11:03 AM Michael Paquier <[email protected]> wrote:
>> On Tue, Nov 26, 2019 at 08:35:33PM +0900, Etsuro Fujita wrote:
>> > I've just started reviewing this patch. One comment I have for now
>> > is: this is categorized into Bug Fixes, but we have a workaround at
>> > least to the regression test case in the patch (ie, just reorder join
>> > clauses), so this seems to me more like an improvement than a bug fix.
>>
>> Hmm. Agreed. Changed the category and moved to next CF.
> thanks Michael for the change.
+1
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-01-19 04:01 Richard Guo <[email protected]>
parent: Etsuro Fujita <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2020-01-19 04:01 UTC (permalink / raw)
To: Etsuro Fujita <[email protected]>; +Cc: Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
Rebased the patch with latest master and also addressed the test case
failure reported by PostgreSQL Patch Tester.
Thanks
Richard
On Fri, Nov 29, 2019 at 11:35 AM Etsuro Fujita <[email protected]>
wrote:
> On Fri, Nov 29, 2019 at 12:08 PM Richard Guo <[email protected]> wrote:
> > On Fri, Nov 29, 2019 at 11:03 AM Michael Paquier <[email protected]>
> wrote:
> >> On Tue, Nov 26, 2019 at 08:35:33PM +0900, Etsuro Fujita wrote:
> >> > I've just started reviewing this patch. One comment I have for now
> >> > is: this is categorized into Bug Fixes, but we have a workaround at
> >> > least to the regression test case in the patch (ie, just reorder join
> >> > clauses), so this seems to me more like an improvement than a bug fix.
> >>
> >> Hmm. Agreed. Changed the category and moved to next CF.
>
> > thanks Michael for the change.
>
> +1
>
> Best regards,
> Etsuro Fujita
>
Attachments:
[application/octet-stream] v2-0001-Fix-up-partitionwise-join.patch (9.7K, ../../CAN_9JTxsSsEcZqLnsZNU8dXXLY+_wRNB_6sKToU068KS_VNqbA@mail.gmail.com/3-v2-0001-Fix-up-partitionwise-join.patch)
download | inline diff:
From 885b2e87f22ab939e652f6610fba97e4e49c46c6 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Sun, 19 Jan 2020 07:44:18 +0000
Subject: [PATCH] Fix up partitionwise join.
---
src/backend/optimizer/path/equivclass.c | 102 +++++++++++++++++++++++++++
src/backend/optimizer/util/relnode.c | 19 +++--
src/include/optimizer/paths.h | 4 ++
src/test/regress/expected/partition_join.out | 38 ++++++++++
src/test/regress/sql/partition_join.sql | 4 ++
5 files changed, 161 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 4ef1254..76fd9c9 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1269,6 +1269,108 @@ generate_join_implied_equalities_for_ecs(PlannerInfo *root,
}
/*
+ * generate_join_implied_equalities_for_all
+ * Create any EC-derived joinclauses of form 'outer_em = inner_em'.
+ *
+ * This is used when building partition info for joinrel.
+ */
+List *
+generate_join_implied_equalities_for_all(PlannerInfo *root,
+ Relids join_relids,
+ Relids outer_relids,
+ Relids inner_relids)
+{
+ List *result = NIL;
+ Bitmapset * matching_ecs;
+ int i;
+
+ /*
+ * Get all eclasses in common between inner_relids and outer_relids
+ */
+ matching_ecs = get_common_eclass_indexes(root, inner_relids,
+ outer_relids);
+
+ i = -1;
+ while ((i = bms_next_member(matching_ecs, i)) >= 0)
+ {
+ EquivalenceClass *ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
+ List *outer_members = NIL;
+ List *inner_members = NIL;
+ ListCell *lc1;
+
+ /* Do not consider this EC if it's ec_broken */
+ if (ec->ec_broken)
+ continue;
+
+ /* Single-member ECs won't generate any deductions */
+ if (list_length(ec->ec_members) <= 1)
+ continue;
+
+ /*
+ * First, scan the EC to identify member values that are computable at the
+ * outer rel or at the inner rel.
+ */
+ foreach(lc1, ec->ec_members)
+ {
+ EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc1);
+
+ /*
+ * We don't need to check explicitly for child EC members. This test
+ * against join_relids will cause them to be ignored except when
+ * considering a child inner rel, which is what we want.
+ */
+ if (!bms_is_subset(cur_em->em_relids, join_relids))
+ continue; /* not computable yet, or wrong child */
+
+ if (bms_is_subset(cur_em->em_relids, outer_relids))
+ outer_members = lappend(outer_members, cur_em);
+ else if (bms_is_subset(cur_em->em_relids, inner_relids))
+ inner_members = lappend(inner_members, cur_em);
+ }
+
+ /*
+ * First, select the joinclause if needed. We can equate any one outer
+ * member to any one inner member, since we know this EC is not
+ * ec_broken.
+ */
+ if (outer_members && inner_members)
+ {
+ RestrictInfo *rinfo;
+
+ foreach(lc1, outer_members)
+ {
+ EquivalenceMember *outer_em = (EquivalenceMember *) lfirst(lc1);
+ ListCell *lc2;
+
+ foreach(lc2, inner_members)
+ {
+ EquivalenceMember *inner_em = (EquivalenceMember *) lfirst(lc2);
+ Oid eq_op;
+
+ eq_op = select_equality_operator(ec,
+ outer_em->em_datatype,
+ inner_em->em_datatype);
+ if (!OidIsValid(eq_op))
+ continue;
+
+ /*
+ * Create clause, setting parent_ec to mark it as redundant with other
+ * joinclauses
+ */
+ rinfo = create_join_clause(root, ec, eq_op,
+ outer_em, inner_em,
+ ec);
+
+ result = lappend(result, rinfo);
+ }
+ }
+ }
+ }
+
+ return result;
+}
+
+/*
* generate_join_implied_equalities for a still-valid EC
*/
static List *
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 374f938..adf75f5 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -55,7 +55,7 @@ static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
static void set_foreign_rel_properties(RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel);
static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
-static void build_joinrel_partition_info(RelOptInfo *joinrel,
+static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
List *restrictlist, JoinType jointype);
static void build_child_join_reltarget(PlannerInfo *root,
@@ -706,7 +706,7 @@ build_join_rel(PlannerInfo *root,
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
/* Store the partition information. */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel, restrictlist,
sjinfo->jointype);
/*
@@ -870,7 +870,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->has_eclass_joins = parent_joinrel->has_eclass_joins;
/* Is the join between partitions itself partitioned? */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel, restrictlist,
jointype);
/* Child joinrel is parallel safe if parent is parallel safe. */
@@ -1613,9 +1613,9 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer)
* the join relation.
*/
static void
-build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
- RelOptInfo *inner_rel, List *restrictlist,
- JoinType jointype)
+build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
+ RelOptInfo *outer_rel, RelOptInfo *inner_rel,
+ List *restrictlist, JoinType jointype)
{
int partnatts;
int cnt;
@@ -1628,6 +1628,13 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
return;
}
+ restrictlist =
+ list_concat_unique_ptr(generate_join_implied_equalities_for_all(root,
+ joinrel->relids,
+ outer_rel->relids,
+ inner_rel->relids),
+ restrictlist);
+
/*
* We can only consider this join as an input to further partitionwise
* joins if (a) the input relations are partitioned and have
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 9ab73bd..ab19ea3 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -140,6 +140,10 @@ extern List *generate_join_implied_equalities(PlannerInfo *root,
Relids join_relids,
Relids outer_relids,
RelOptInfo *inner_rel);
+extern List *generate_join_implied_equalities_for_all(PlannerInfo *root,
+ Relids join_relids,
+ Relids outer_relids,
+ Relids inner_relids);
extern List *generate_join_implied_equalities_for_ecs(PlannerInfo *root,
List *eclasses,
Relids join_relids,
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index b3fbe47..20346f3 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -62,6 +62,44 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
450 | 0450 | 450 | 0450
(4 rows)
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Merge Join
+ Merge Cond: (t1_1.a = t2_1.a)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on prt2_p1 t2_1
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2_2.a)
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_2
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_3.a = t2_3.a)
+ -> Seq Scan on prt1_p3 t1_3
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_3
+ Filter: (a = b)
+(22 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ a | c | b | c
+----+------+----+------
+ 0 | 0000 | 0 | 0000
+ 6 | 0006 | 6 | 0006
+ 12 | 0012 | 12 | 0012
+ 18 | 0018 | 18 | 0018
+ 24 | 0024 | 24 | 0024
+(5 rows)
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 575ba7b..30c981e 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -34,6 +34,10 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
--
2.7.4
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-04-04 20:37 Tom Lane <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2020-04-04 20:37 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Etsuro Fujita <[email protected]>; Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
Richard Guo <[email protected]> writes:
> Rebased the patch with latest master and also addressed the test case
> failure reported by PostgreSQL Patch Tester.
I looked this patch over, but I don't like it too much: it seems very
brute-force (and badly under-commented). Creating all those extra
RestrictInfos isn't too cheap in itself, plus they'll jam up the
equivalence-class machinery for future tests.
There is already something in equivclass.c that would almost do what
we want here: exprs_known_equal() would tell us whether the partkeys
can be found in the same eclass, without having to generate data
structures along the way. The current implementation is not watertight
because it doesn't check opclass semantics, but that consideration
can be bolted on readily enough. So that leads me to something like
the attached.
One argument that could be made against this approach is that if there
are a lot of partkey expressions, this requires O(N^2) calls to
exprs_known_equal, something that's already not too cheap. I think
that that's not a big problem because the number of partkey expressions
would only be equal to the join degree (ie it doesn't scale with the
number of partitions of the baserels) ... but maybe I'm wrong about
that? I also wonder if it's really necessary to check every pair
of partkey expressions. It seems at least plausible that in the
cases we care about, all the partkeys on each side would be in the same
eclasses anyway, so that comparing the first members of each list would
be sufficient. But I haven't beat on that point.
regards, tom lane
Attachments:
[text/x-diff] v3-0001-Fix-up-partitionwise-join.patch (14.4K, ../../[email protected]/2-v3-0001-Fix-up-partitionwise-join.patch)
download | inline diff:
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 4ef1254..7c21692 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2074,15 +2074,17 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
* Detect whether two expressions are known equal due to equivalence
* relationships.
*
- * Actually, this only shows that the expressions are equal according
- * to some opfamily's notion of equality --- but we only use it for
- * selectivity estimation, so a fuzzy idea of equality is OK.
+ * If opfamily is given, the expressions must be known equal per the semantics
+ * of that opfamily (note it has to be a btree opfamily, since those are the
+ * only opfamilies equivclass.c deals with). If opfamily is InvalidOid, we'll
+ * return true if they're equal according to any opfamily, which is fuzzy but
+ * OK for estimation purposes.
*
* Note: does not bother to check for "equal(item1, item2)"; caller must
* check that case if it's possible to pass identical items.
*/
bool
-exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
+exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily)
{
ListCell *lc1;
@@ -2097,6 +2099,17 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
if (ec->ec_has_volatile)
continue;
+ /*
+ * It's okay to consider ec_broken ECs here. Brokenness just means we
+ * couldn't derive all the implied clauses we'd have liked to; it does
+ * not invalidate our knowledge that the members are equal.
+ */
+
+ /* Ignore if this EC doesn't use specified opfamily */
+ if (OidIsValid(opfamily) &&
+ !list_member_oid(ec->ec_opfamilies, opfamily))
+ continue;
+
foreach(lc2, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
@@ -2125,8 +2138,7 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
* (In principle there might be more than one matching eclass if multiple
* collations are involved, but since collation doesn't matter for equality,
* we ignore that fine point here.) This is much like exprs_known_equal,
- * except that we insist on the comparison operator matching the eclass, so
- * that the result is definite not approximate.
+ * except for the format of the input.
*/
EquivalenceClass *
match_eclasses_to_foreign_key_col(PlannerInfo *root,
@@ -2163,7 +2175,7 @@ match_eclasses_to_foreign_key_col(PlannerInfo *root,
/* Never match to a volatile EC */
if (ec->ec_has_volatile)
continue;
- /* Note: it seems okay to match to "broken" eclasses here */
+ /* It's okay to consider "broken" ECs here, see exprs_known_equal */
foreach(lc2, ec->ec_members)
{
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index af1fb48..18474d8 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -56,10 +56,10 @@ static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
static void set_foreign_rel_properties(RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel);
static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
-static void build_joinrel_partition_info(RelOptInfo *joinrel,
+static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
List *restrictlist, JoinType jointype);
-static bool have_partkey_equi_join(RelOptInfo *joinrel,
+static bool have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *rel1, RelOptInfo *rel2,
JoinType jointype, List *restrictlist);
static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
@@ -715,8 +715,8 @@ build_join_rel(PlannerInfo *root,
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
/* Store the partition information. */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
- sjinfo->jointype);
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel,
+ restrictlist, sjinfo->jointype);
/*
* Set estimates of the joinrel's size.
@@ -879,8 +879,8 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->has_eclass_joins = parent_joinrel->has_eclass_joins;
/* Is the join between partitions itself partitioned? */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
- jointype);
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel,
+ restrictlist, jointype);
/* Child joinrel is parallel safe if parent is parallel safe. */
joinrel->consider_parallel = parent_joinrel->consider_parallel;
@@ -1621,9 +1621,9 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer)
* partitioned join relation.
*/
static void
-build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
- RelOptInfo *inner_rel, List *restrictlist,
- JoinType jointype)
+build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
+ RelOptInfo *outer_rel, RelOptInfo *inner_rel,
+ List *restrictlist, JoinType jointype)
{
PartitionScheme part_scheme;
@@ -1649,7 +1649,7 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
!outer_rel->consider_partitionwise_join ||
!inner_rel->consider_partitionwise_join ||
outer_rel->part_scheme != inner_rel->part_scheme ||
- !have_partkey_equi_join(joinrel, outer_rel, inner_rel,
+ !have_partkey_equi_join(root, joinrel, outer_rel, inner_rel,
jointype, restrictlist))
{
Assert(!IS_PARTITIONED_REL(joinrel));
@@ -1713,15 +1713,14 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
* partition keys.
*/
static bool
-have_partkey_equi_join(RelOptInfo *joinrel,
+have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *rel1, RelOptInfo *rel2,
JoinType jointype, List *restrictlist)
{
PartitionScheme part_scheme = rel1->part_scheme;
- ListCell *lc;
- int cnt_pks;
bool pk_has_clause[PARTITION_MAX_KEYS];
- bool strict_op;
+ int pks_known_equal;
+ ListCell *lc;
/*
* This function must only be called when the joined relations have same
@@ -1730,13 +1729,19 @@ have_partkey_equi_join(RelOptInfo *joinrel,
Assert(rel1->part_scheme == rel2->part_scheme);
Assert(part_scheme);
+ /* We use a bool array to track which partkey columns are known equal */
memset(pk_has_clause, 0, sizeof(pk_has_clause));
+ /* ... as well as a count of how many are known equal */
+ pks_known_equal = 0;
+
+ /* First, look through the join's restriction clauses */
foreach(lc, restrictlist)
{
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
OpExpr *opexpr;
Expr *expr1;
Expr *expr2;
+ bool strict_op;
int ipk1;
int ipk2;
@@ -1796,11 +1801,15 @@ have_partkey_equi_join(RelOptInfo *joinrel,
if (ipk1 != ipk2)
continue;
+ /* Ignore clause if we already proved these keys equal. */
+ if (pk_has_clause[ipk1])
+ continue;
+
/*
* The clause allows partitionwise join only if it uses the same
* operator family as that specified by the partition key.
*/
- if (rel1->part_scheme->strategy == PARTITION_STRATEGY_HASH)
+ if (part_scheme->strategy == PARTITION_STRATEGY_HASH)
{
if (!OidIsValid(rinfo->hashjoinoperator) ||
!op_in_opfamily(rinfo->hashjoinoperator,
@@ -1813,16 +1822,88 @@ have_partkey_equi_join(RelOptInfo *joinrel,
/* Mark the partition key as having an equi-join clause. */
pk_has_clause[ipk1] = true;
+
+ /* We can stop examining clauses once we prove all keys equal. */
+ if (++pks_known_equal == part_scheme->partnatts)
+ return true;
}
- /* Check whether every partition key has an equi-join condition. */
- for (cnt_pks = 0; cnt_pks < part_scheme->partnatts; cnt_pks++)
+ /*
+ * Also check to see if any keys are known equal by equivclass.c. In most
+ * cases there would have been a join restriction clause generated from
+ * any EC that had such knowledge, but there might be no such clause, or
+ * it might happen to constrain other members of the ECs than the ones we
+ * are looking for.
+ */
+ for (int ipk = 0; ipk < part_scheme->partnatts; ipk++)
{
- if (!pk_has_clause[cnt_pks])
- return false;
+ Oid btree_opfamily;
+
+ /* Ignore if we already proved these keys equal. */
+ if (pk_has_clause[ipk])
+ continue;
+
+ /*
+ * We need a btree opfamily to ask equivclass.c about. If the
+ * partopfamily is a hash opfamily, look up its equality operator, and
+ * select some btree opfamily that that operator is part of. (Any
+ * such opfamily should be good enough, since equivclass.c will track
+ * multiple opfamilies as appropriate.)
+ */
+ if (part_scheme->strategy == PARTITION_STRATEGY_HASH)
+ {
+ Oid eq_op;
+ List *eq_opfamilies;
+
+ eq_op = get_opfamily_member(part_scheme->partopfamily[ipk],
+ part_scheme->partopcintype[ipk],
+ part_scheme->partopcintype[ipk],
+ HTEqualStrategyNumber);
+ if (!OidIsValid(eq_op))
+ break; /* we're not going to succeed */
+ eq_opfamilies = get_mergejoin_opfamilies(eq_op);
+ if (eq_opfamilies == NIL)
+ break; /* we're not going to succeed */
+ btree_opfamily = linitial_oid(eq_opfamilies);
+ }
+ else
+ btree_opfamily = part_scheme->partopfamily[ipk];
+
+ /*
+ * We consider only non-nullable partition keys here; nullable ones
+ * would not be treated as part of the same equivalence classes as
+ * non-nullable ones.
+ */
+ foreach(lc, rel1->partexprs[ipk])
+ {
+ Node *expr1 = (Node *) lfirst(lc);
+ ListCell *lc2;
+
+ foreach(lc2, rel2->partexprs[ipk])
+ {
+ Node *expr2 = (Node *) lfirst(lc2);
+
+ if (exprs_known_equal(root, expr1, expr2, btree_opfamily))
+ {
+ pk_has_clause[ipk] = true;
+ break;
+ }
+ }
+ if (pk_has_clause[ipk])
+ break;
+ }
+
+ if (pk_has_clause[ipk])
+ {
+ /* We can stop examining keys once we prove all keys equal. */
+ if (++pks_known_equal == part_scheme->partnatts)
+ return true;
+ }
+ else
+ break; /* no chance to succeed, give up */
}
- return true;
+ return false;
}
/*
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 4fdcb07..5878a14 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -3109,10 +3109,11 @@ add_unique_group_var(PlannerInfo *root, List *varinfos,
/*
* Drop known-equal vars, but only if they belong to different
- * relations (see comments for estimate_num_groups)
+ * relations (see comments for estimate_num_groups). We aren't too
+ * fussy about the semantics of "equal" here.
*/
if (vardata->rel != varinfo->rel &&
- exprs_known_equal(root, var, varinfo->var))
+ exprs_known_equal(root, var, varinfo->var, InvalidOid))
{
if (varinfo->ndistinct <= ndistinct)
{
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index c689fe8..3756a44 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -142,7 +142,8 @@ extern List *generate_join_implied_equalities_for_ecs(PlannerInfo *root,
Relids join_relids,
Relids outer_relids,
RelOptInfo *inner_rel);
-extern bool exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2);
+extern bool exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2,
+ Oid opfamily);
extern EquivalenceClass *match_eclasses_to_foreign_key_col(PlannerInfo *root,
ForeignKeyOptInfo *fkinfo,
int colno);
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index b3fbe47..9e8eeaf 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -62,6 +62,45 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
450 | 0450 | 450 | 0450
(4 rows)
+-- inner join with partially-redundant join clauses
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Merge Join
+ Merge Cond: (t1_1.a = t2_1.a)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on prt2_p1 t2_1
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2_2.a)
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_2
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_3.a = t2_3.a)
+ -> Seq Scan on prt1_p3 t1_3
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_3
+ Filter: (a = b)
+(22 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ a | c | b | c
+----+------+----+------
+ 0 | 0000 | 0 | 0000
+ 6 | 0006 | 6 | 0006
+ 12 | 0012 | 12 | 0012
+ 18 | 0018 | 18 | 0018
+ 24 | 0024 | 24 | 0024
+(5 rows)
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 575ba7b..1f218c0 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -34,6 +34,11 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
+-- inner join with partially-redundant join clauses
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-04-08 04:58 Richard Guo <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2020-04-08 04:58 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Richard Guo <[email protected]>; Etsuro Fujita <[email protected]>; Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Sun, Apr 5, 2020 at 4:38 AM Tom Lane <[email protected]> wrote:
> Richard Guo <[email protected]> writes:
> > Rebased the patch with latest master and also addressed the test case
> > failure reported by PostgreSQL Patch Tester.
>
> I looked this patch over, but I don't like it too much: it seems very
> brute-force (and badly under-commented). Creating all those extra
> RestrictInfos isn't too cheap in itself, plus they'll jam up the
> equivalence-class machinery for future tests.
>
Thanks for the review.
>
> There is already something in equivclass.c that would almost do what
> we want here: exprs_known_equal() would tell us whether the partkeys
> can be found in the same eclass, without having to generate data
> structures along the way. The current implementation is not watertight
> because it doesn't check opclass semantics, but that consideration
> can be bolted on readily enough. So that leads me to something like
> the attached.
>
I looked through this patch and it's much more elegant than the previous
one. Thank you for working on it.
For partkeys which fail to be identified as equal by looking through
restrictlist, it's a good idea to check them in ECs with the help of
exprs_known_equal().
I have some concern about we only check non-nullable partexprs. Is it
possible that two nullable partexprs come from the same EC? I tried to
give an example but failed.
>
> One argument that could be made against this approach is that if there
> are a lot of partkey expressions, this requires O(N^2) calls to
> exprs_known_equal, something that's already not too cheap. I think
> that that's not a big problem because the number of partkey expressions
> would only be equal to the join degree (ie it doesn't scale with the
> number of partitions of the baserels) ... but maybe I'm wrong about
> that?
You are right. According to how partexpr is formed for joinrel in
set_joinrel_partition_key_exprs(), each base relation within the join
contributes one partexpr, so the number of partexprs would be equal to
the join degree.
> I also wonder if it's really necessary to check every pair
> of partkey expressions. It seems at least plausible that in the
> cases we care about, all the partkeys on each side would be in the same
> eclasses anyway, so that comparing the first members of each list would
> be sufficient. But I haven't beat on that point.
>
Not sure about it. But cannot come out with a counterexample.
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-04-08 17:07 Tom Lane <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2020-04-08 17:07 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Richard Guo <[email protected]>; Etsuro Fujita <[email protected]>; Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
Richard Guo <[email protected]> writes:
> On Sun, Apr 5, 2020 at 4:38 AM Tom Lane <[email protected]> wrote:
>> There is already something in equivclass.c that would almost do what
>> we want here: exprs_known_equal() would tell us whether the partkeys
>> can be found in the same eclass, without having to generate data
>> structures along the way. The current implementation is not watertight
>> because it doesn't check opclass semantics, but that consideration
>> can be bolted on readily enough. So that leads me to something like
>> the attached.
> I have some concern about we only check non-nullable partexprs. Is it
> possible that two nullable partexprs come from the same EC? I tried to
> give an example but failed.
Currently the EC infrastructure doesn't really cope with outer join
equijoins. They are not treated as producing true equivalences,
so I think that the case you're worried about can't occur (which is why
I didn't code for it). I have hopes of being able to incorporate outer
joins into the EC logic in a less squishy way in the future, by making
the representation of Vars distinguish explicitly between
value-before-outer-join and value-after-outer-join, after which we could
make bulletproof assertions about what is equal to what, even with outer
joins in the mix. If that works out it might produce a cleaner answer
in this area too.
TBH, now that I have had some exposure to the partitionwise join
matching logic I don't much like any of it. I feel like it's doing
about the same job as ECs, but in an unprincipled and not very
efficient manner. Right now is no time to redesign it, of course,
but maybe at some point we could do that. (I did experiment with
removing all the rest of have_partkey_equi_join() and having it
*only* ask exprs_known_equal() about equivalences, which is more or
less what I'm envisioning here. That caused some of the existing
regression tests to fail, so there's something that the idea isn't
covering. I didn't dig any further at the time, and in particular
failed to check whether the problems were specifically about outer
joins, which'd be unsurprising given the above.)
Anyway, this work has missed the window for v13, so we've got plenty
of time to think about it.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-04-09 04:14 Richard Guo <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 3 replies; 32+ messages in thread
From: Richard Guo @ 2020-04-09 04:14 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Richard Guo <[email protected]>; Etsuro Fujita <[email protected]>; Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
On Thu, Apr 9, 2020 at 1:07 AM Tom Lane <[email protected]> wrote:
> Richard Guo <[email protected]> writes:
> > On Sun, Apr 5, 2020 at 4:38 AM Tom Lane <[email protected]> wrote:
> >> There is already something in equivclass.c that would almost do what
> >> we want here: exprs_known_equal() would tell us whether the partkeys
> >> can be found in the same eclass, without having to generate data
> >> structures along the way. The current implementation is not watertight
> >> because it doesn't check opclass semantics, but that consideration
> >> can be bolted on readily enough. So that leads me to something like
> >> the attached.
>
> > I have some concern about we only check non-nullable partexprs. Is it
> > possible that two nullable partexprs come from the same EC? I tried to
> > give an example but failed.
>
> Currently the EC infrastructure doesn't really cope with outer join
> equijoins. They are not treated as producing true equivalences,
> so I think that the case you're worried about can't occur (which is why
> I didn't code for it). I have hopes of being able to incorporate outer
> joins into the EC logic in a less squishy way in the future, by making
> the representation of Vars distinguish explicitly between
> value-before-outer-join and value-after-outer-join, after which we could
> make bulletproof assertions about what is equal to what, even with outer
> joins in the mix. If that works out it might produce a cleaner answer
> in this area too.
>
This is very appealing. Do we have ongoing discussions/threads about
this idea?
> (I did experiment with
> removing all the rest of have_partkey_equi_join() and having it
> *only* ask exprs_known_equal() about equivalences, which is more or
> less what I'm envisioning here. That caused some of the existing
> regression tests to fail, so there's something that the idea isn't
> covering. I didn't dig any further at the time, and in particular
> failed to check whether the problems were specifically about outer
> joins, which'd be unsurprising given the above.)
>
I think it would not work for outer joins if we only check
exprs_known_equal() for equivalences. If the equi-join conditions
involving pairs of matching partition keys are outer join quals
mentioning nonnullable side rels, they would not exist in any EC
according to the current EC infrastructure. So we still have to look
through restrictlist.
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-04-09 05:24 Tom Lane <[email protected]>
parent: Richard Guo <[email protected]>
2 siblings, 0 replies; 32+ messages in thread
From: Tom Lane @ 2020-04-09 05:24 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Richard Guo <[email protected]>; Etsuro Fujita <[email protected]>; Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
Richard Guo <[email protected]> writes:
> On Thu, Apr 9, 2020 at 1:07 AM Tom Lane <[email protected]> wrote:
>> I have hopes of being able to incorporate outer
>> joins into the EC logic in a less squishy way in the future, by making
>> the representation of Vars distinguish explicitly between
>> value-before-outer-join and value-after-outer-join, after which we could
>> make bulletproof assertions about what is equal to what, even with outer
>> joins in the mix. If that works out it might produce a cleaner answer
>> in this area too.
> This is very appealing. Do we have ongoing discussions/threads about
> this idea?
There's some preliminary noodling in this thread:
https://www.postgresql.org/message-id/flat/15848.1576515643%40sss.pgh.pa.us
I've pushed the earlier work discussed there, but stalled out due to
the call of other responsibilities after posting the currently-last
message in the thread. Hoping to get back into that over the summer.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-04-09 14:06 Ashutosh Bapat <[email protected]>
parent: Richard Guo <[email protected]>
2 siblings, 0 replies; 32+ messages in thread
From: Ashutosh Bapat @ 2020-04-09 14:06 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Tom Lane <[email protected]>; Richard Guo <[email protected]>; Etsuro Fujita <[email protected]>; Michael Paquier <[email protected]>; Amit Langote <[email protected]>; pgsql-hackers
>
> I think it would not work for outer joins if we only check
> exprs_known_equal() for equivalences. If the equi-join conditions
> involving pairs of matching partition keys are outer join quals
> mentioning nonnullable side rels, they would not exist in any EC
> according to the current EC infrastructure. So we still have to look
> through restrictlist.
>
When I wrote that function and even today, EC didn't accommodate outer
join equality conditions. If we can somehow do that,
have_partkey_equi_join() can be completely eliminated.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-11-06 15:25 Anastasia Lubennikova <[email protected]>
parent: Richard Guo <[email protected]>
2 siblings, 1 reply; 32+ messages in thread
From: Anastasia Lubennikova @ 2020-11-06 15:25 UTC (permalink / raw)
To: [email protected]; +Cc: Richard Guo <[email protected]>
Status update for a commitfest entry.
According to CFbot this patch fails to apply. Richard, can you send an update, please?
Also, I see that the thread was inactive for a while.
Are you going to continue this work? I think it would be helpful, if you could write a short recap about current state of the patch and list open questions for reviewers.
The new status of this patch is: Waiting on Author
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-11-10 09:12 Richard Guo <[email protected]>
parent: Anastasia Lubennikova <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2020-11-10 09:12 UTC (permalink / raw)
To: Anastasia Lubennikova <[email protected]>; +Cc: Pg Hackers <[email protected]>; Richard Guo <[email protected]>
On Fri, Nov 6, 2020 at 11:26 PM Anastasia Lubennikova <
[email protected]> wrote:
> Status update for a commitfest entry.
>
> According to CFbot this patch fails to apply. Richard, can you send an
> update, please?
>
> Also, I see that the thread was inactive for a while.
> Are you going to continue this work? I think it would be helpful, if you
> could write a short recap about current state of the patch and list open
> questions for reviewers.
>
> The new status of this patch is: Waiting on Author
>
Thanks Anastasia. I've rebased the patch with latest master.
To recap, the problem we are fixing here is when generating join clauses
from equivalence classes, we only select the joinclause with the 'best
score', or the first joinclause with a score of 3. This may cause us to
miss some joinclause on partition keys and thus fail to generate
partitionwise join.
The initial idea for the fix is to create all the RestrictInfos from ECs
in order to check whether there exist equi-join conditions involving
pairs of matching partition keys of the relations being joined for all
partition keys. And then Tom proposed a much better idea which leverages
function exprs_known_equal() to tell whether the partkeys can be found
in the same eclass, which is the current implementation in the latest
patch.
Thanks
Richard
Attachments:
[application/octet-stream] v4-0001-Fix-up-partitionwise-join.patch (14.4K, ../../CAMbWs49wRS5CkrjEOZxCVLO+Vm5YE_XHrYh8FCQbyvW-OUoMCg@mail.gmail.com/3-v4-0001-Fix-up-partitionwise-join.patch)
download | inline diff:
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index f98fd7b..8b526d6 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2200,15 +2200,17 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
* Detect whether two expressions are known equal due to equivalence
* relationships.
*
- * Actually, this only shows that the expressions are equal according
- * to some opfamily's notion of equality --- but we only use it for
- * selectivity estimation, so a fuzzy idea of equality is OK.
+ * If opfamily is given, the expressions must be known equal per the semantics
+ * of that opfamily (note it has to be a btree opfamily, since those are the
+ * only opfamilies equivclass.c deals with). If opfamily is InvalidOid, we'll
+ * return true if they're equal according to any opfamily, which is fuzzy but
+ * OK for estimation purposes.
*
* Note: does not bother to check for "equal(item1, item2)"; caller must
* check that case if it's possible to pass identical items.
*/
bool
-exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
+exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily)
{
ListCell *lc1;
@@ -2223,6 +2225,17 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
if (ec->ec_has_volatile)
continue;
+ /*
+ * It's okay to consider ec_broken ECs here. Brokenness just means we
+ * couldn't derive all the implied clauses we'd have liked to; it does
+ * not invalidate our knowledge that the members are equal.
+ */
+
+ /* Ignore if this EC doesn't use specified opfamily */
+ if (OidIsValid(opfamily) &&
+ !list_member_oid(ec->ec_opfamilies, opfamily))
+ continue;
+
foreach(lc2, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
@@ -2251,8 +2264,7 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
* (In principle there might be more than one matching eclass if multiple
* collations are involved, but since collation doesn't matter for equality,
* we ignore that fine point here.) This is much like exprs_known_equal,
- * except that we insist on the comparison operator matching the eclass, so
- * that the result is definite not approximate.
+ * except for the format of the input.
*
* On success, we also set fkinfo->eclass[colno] to the matching eclass,
* and set fkinfo->fk_eclass_member[colno] to the eclass member for the
@@ -2293,7 +2305,7 @@ match_eclasses_to_foreign_key_col(PlannerInfo *root,
/* Never match to a volatile EC */
if (ec->ec_has_volatile)
continue;
- /* Note: it seems okay to match to "broken" eclasses here */
+ /* It's okay to consider "broken" ECs here, see exprs_known_equal */
foreach(lc2, ec->ec_members)
{
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 76245c1..9247e75 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -56,10 +56,10 @@ static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
static void set_foreign_rel_properties(RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel);
static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
-static void build_joinrel_partition_info(RelOptInfo *joinrel,
+static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
List *restrictlist, JoinType jointype);
-static bool have_partkey_equi_join(RelOptInfo *joinrel,
+static bool have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *rel1, RelOptInfo *rel2,
JoinType jointype, List *restrictlist);
static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
@@ -717,8 +717,8 @@ build_join_rel(PlannerInfo *root,
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
/* Store the partition information. */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
- sjinfo->jointype);
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel,
+ restrictlist, sjinfo->jointype);
/*
* Set estimates of the joinrel's size.
@@ -882,8 +882,8 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->has_eclass_joins = parent_joinrel->has_eclass_joins;
/* Is the join between partitions itself partitioned? */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
- jointype);
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel,
+ restrictlist, jointype);
/* Child joinrel is parallel safe if parent is parallel safe. */
joinrel->consider_parallel = parent_joinrel->consider_parallel;
@@ -1624,9 +1624,9 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer)
* partitioned join relation.
*/
static void
-build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
- RelOptInfo *inner_rel, List *restrictlist,
- JoinType jointype)
+build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
+ RelOptInfo *outer_rel, RelOptInfo *inner_rel,
+ List *restrictlist, JoinType jointype)
{
PartitionScheme part_scheme;
@@ -1652,7 +1652,7 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
!outer_rel->consider_partitionwise_join ||
!inner_rel->consider_partitionwise_join ||
outer_rel->part_scheme != inner_rel->part_scheme ||
- !have_partkey_equi_join(joinrel, outer_rel, inner_rel,
+ !have_partkey_equi_join(root, joinrel, outer_rel, inner_rel,
jointype, restrictlist))
{
Assert(!IS_PARTITIONED_REL(joinrel));
@@ -1695,15 +1695,14 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
* partition keys.
*/
static bool
-have_partkey_equi_join(RelOptInfo *joinrel,
+have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *rel1, RelOptInfo *rel2,
JoinType jointype, List *restrictlist)
{
PartitionScheme part_scheme = rel1->part_scheme;
- ListCell *lc;
- int cnt_pks;
bool pk_has_clause[PARTITION_MAX_KEYS];
- bool strict_op;
+ int pks_known_equal;
+ ListCell *lc;
/*
* This function must only be called when the joined relations have same
@@ -1712,13 +1711,19 @@ have_partkey_equi_join(RelOptInfo *joinrel,
Assert(rel1->part_scheme == rel2->part_scheme);
Assert(part_scheme);
+ /* We use a bool array to track which partkey columns are known equal */
memset(pk_has_clause, 0, sizeof(pk_has_clause));
+ /* ... as well as a count of how many are known equal */
+ pks_known_equal = 0;
+
+ /* First, look through the join's restriction clauses */
foreach(lc, restrictlist)
{
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
OpExpr *opexpr;
Expr *expr1;
Expr *expr2;
+ bool strict_op;
int ipk1;
int ipk2;
@@ -1778,11 +1783,15 @@ have_partkey_equi_join(RelOptInfo *joinrel,
if (ipk1 != ipk2)
continue;
+ /* Ignore clause if we already proved these keys equal. */
+ if (pk_has_clause[ipk1])
+ continue;
+
/*
* The clause allows partitionwise join only if it uses the same
* operator family as that specified by the partition key.
*/
- if (rel1->part_scheme->strategy == PARTITION_STRATEGY_HASH)
+ if (part_scheme->strategy == PARTITION_STRATEGY_HASH)
{
if (!OidIsValid(rinfo->hashjoinoperator) ||
!op_in_opfamily(rinfo->hashjoinoperator,
@@ -1795,16 +1804,88 @@ have_partkey_equi_join(RelOptInfo *joinrel,
/* Mark the partition key as having an equi-join clause. */
pk_has_clause[ipk1] = true;
+
+ /* We can stop examining clauses once we prove all keys equal. */
+ if (++pks_known_equal == part_scheme->partnatts)
+ return true;
}
- /* Check whether every partition key has an equi-join condition. */
- for (cnt_pks = 0; cnt_pks < part_scheme->partnatts; cnt_pks++)
+ /*
+ * Also check to see if any keys are known equal by equivclass.c. In most
+ * cases there would have been a join restriction clause generated from
+ * any EC that had such knowledge, but there might be no such clause, or
+ * it might happen to constrain other members of the ECs than the ones we
+ * are looking for.
+ */
+ for (int ipk = 0; ipk < part_scheme->partnatts; ipk++)
{
- if (!pk_has_clause[cnt_pks])
- return false;
+ Oid btree_opfamily;
+
+ /* Ignore if we already proved these keys equal. */
+ if (pk_has_clause[ipk])
+ continue;
+
+ /*
+ * We need a btree opfamily to ask equivclass.c about. If the
+ * partopfamily is a hash opfamily, look up its equality operator, and
+ * select some btree opfamily that that operator is part of. (Any
+ * such opfamily should be good enough, since equivclass.c will track
+ * multiple opfamilies as appropriate.)
+ */
+ if (part_scheme->strategy == PARTITION_STRATEGY_HASH)
+ {
+ Oid eq_op;
+ List *eq_opfamilies;
+
+ eq_op = get_opfamily_member(part_scheme->partopfamily[ipk],
+ part_scheme->partopcintype[ipk],
+ part_scheme->partopcintype[ipk],
+ HTEqualStrategyNumber);
+ if (!OidIsValid(eq_op))
+ break; /* we're not going to succeed */
+ eq_opfamilies = get_mergejoin_opfamilies(eq_op);
+ if (eq_opfamilies == NIL)
+ break; /* we're not going to succeed */
+ btree_opfamily = linitial_oid(eq_opfamilies);
+ }
+ else
+ btree_opfamily = part_scheme->partopfamily[ipk];
+
+ /*
+ * We consider only non-nullable partition keys here; nullable ones
+ * would not be treated as part of the same equivalence classes as
+ * non-nullable ones.
+ */
+ foreach(lc, rel1->partexprs[ipk])
+ {
+ Node *expr1 = (Node *) lfirst(lc);
+ ListCell *lc2;
+
+ foreach(lc2, rel2->partexprs[ipk])
+ {
+ Node *expr2 = (Node *) lfirst(lc2);
+
+ if (exprs_known_equal(root, expr1, expr2, btree_opfamily))
+ {
+ pk_has_clause[ipk] = true;
+ break;
+ }
+ }
+ if (pk_has_clause[ipk])
+ break;
+ }
+
+ if (pk_has_clause[ipk])
+ {
+ /* We can stop examining keys once we prove all keys equal. */
+ if (++pks_known_equal == part_scheme->partnatts)
+ return true;
+ }
+ else
+ break; /* no chance to succeed, give up */
}
- return true;
+ return false;
}
/*
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index bec357f..f75bfe4 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -3264,10 +3264,11 @@ add_unique_group_var(PlannerInfo *root, List *varinfos,
/*
* Drop known-equal vars, but only if they belong to different
- * relations (see comments for estimate_num_groups)
+ * relations (see comments for estimate_num_groups). We aren't too
+ * fussy about the semantics of "equal" here.
*/
if (vardata->rel != varinfo->rel &&
- exprs_known_equal(root, var, varinfo->var))
+ exprs_known_equal(root, var, varinfo->var, InvalidOid))
{
if (varinfo->ndistinct <= ndistinct)
{
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 8a4c6f8..a866d66 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -146,7 +146,8 @@ extern List *generate_join_implied_equalities_for_ecs(PlannerInfo *root,
Relids join_relids,
Relids outer_relids,
RelOptInfo *inner_rel);
-extern bool exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2);
+extern bool exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2,
+ Oid opfamily);
extern EquivalenceClass *match_eclasses_to_foreign_key_col(PlannerInfo *root,
ForeignKeyOptInfo *fkinfo,
int colno);
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 0057f41..d8c7584 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -62,6 +62,45 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
450 | 0450 | 450 | 0450
(4 rows)
+-- inner join with partially-redundant join clauses
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Merge Join
+ Merge Cond: (t1_1.a = t2_1.a)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on prt2_p1 t2_1
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2_2.a)
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_2
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_3.a = t2_3.a)
+ -> Seq Scan on prt1_p3 t1_3
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_3
+ Filter: (a = b)
+(22 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ a | c | b | c
+----+------+----+------
+ 0 | 0000 | 0 | 0000
+ 6 | 0006 | 6 | 0006
+ 12 | 0012 | 12 | 0012
+ 18 | 0018 | 18 | 0018
+ 24 | 0024 | 24 | 0024
+(5 rows)
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index d97b5b6..6b7d689 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -34,6 +34,11 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
+-- inner join with partially-redundant join clauses
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2020-11-27 12:05 Ashutosh Bapat <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 2 replies; 32+ messages in thread
From: Ashutosh Bapat @ 2020-11-27 12:05 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; Pg Hackers <[email protected]>; Richard Guo <[email protected]>; [email protected]
On Tue, Nov 10, 2020 at 2:43 PM Richard Guo <[email protected]> wrote:
>
>
> On Fri, Nov 6, 2020 at 11:26 PM Anastasia Lubennikova <[email protected]> wrote:
>>
>> Status update for a commitfest entry.
>>
>> According to CFbot this patch fails to apply. Richard, can you send an update, please?
>>
>> Also, I see that the thread was inactive for a while.
>> Are you going to continue this work? I think it would be helpful, if you could write a short recap about current state of the patch and list open questions for reviewers.
>>
>> The new status of this patch is: Waiting on Author
>
>
> Thanks Anastasia. I've rebased the patch with latest master.
>
> To recap, the problem we are fixing here is when generating join clauses
> from equivalence classes, we only select the joinclause with the 'best
> score', or the first joinclause with a score of 3. This may cause us to
> miss some joinclause on partition keys and thus fail to generate
> partitionwise join.
>
> The initial idea for the fix is to create all the RestrictInfos from ECs
> in order to check whether there exist equi-join conditions involving
> pairs of matching partition keys of the relations being joined for all
> partition keys. And then Tom proposed a much better idea which leverages
> function exprs_known_equal() to tell whether the partkeys can be found
> in the same eclass, which is the current implementation in the latest
> patch.
>
In the example you gave earlier, the equi join on partition key was
there but it was replaced by individual constant assignment clauses.
So if we keep the original restrictclause in there with a new flag
indicating that it's redundant, have_partkey_equi_join will still be
able to use it without much change. Depending upon where all we need
to use avoid restrictclauses with the redundant flag, this might be an
easier approach. However, with Tom's idea partition-wise join may be
used even when there is no equi-join between partition keys but there
are clauses like pk = const for all tables involved and const is the
same for all such tables.
In the spirit of small improvement made to the performance of
have_partkey_equi_join(), pk_has_clause should be renamed as
pk_known_equal and pks_known_equal as num_equal_pks.
The loop traversing the partition keys at a given position, may be
optimized further if we pass lists to exprs_known_equal() which in
turns checks whether one expression from each list is member of a
given EC. This will avoid traversing all equivalence classes for each
partition key expression, which can be a huge improvement when there
are many ECs. But I think if one of the partition key expression at a
given position is member of an equivalence class all the other
partition key expressions at that position should be part of that
equivalence class since there should be an equi-join between those. So
the loop in loop may not be required to start with.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2021-03-09 16:22 David Steele <[email protected]>
parent: Ashutosh Bapat <[email protected]>
1 sibling, 0 replies; 32+ messages in thread
From: David Steele @ 2021-03-09 16:22 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; Richard Guo <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; Pg Hackers <[email protected]>; Richard Guo <[email protected]>; [email protected]
On 11/27/20 7:05 AM, Ashutosh Bapat wrote:
> On Tue, Nov 10, 2020 at 2:43 PM Richard Guo <[email protected]> wrote:
>>
>> To recap, the problem we are fixing here is when generating join clauses
>> from equivalence classes, we only select the joinclause with the 'best
>> score', or the first joinclause with a score of 3. This may cause us to
>> miss some joinclause on partition keys and thus fail to generate
>> partitionwise join.
>>
>> The initial idea for the fix is to create all the RestrictInfos from ECs
>> in order to check whether there exist equi-join conditions involving
>> pairs of matching partition keys of the relations being joined for all
>> partition keys. And then Tom proposed a much better idea which leverages
>> function exprs_known_equal() to tell whether the partkeys can be found
>> in the same eclass, which is the current implementation in the latest
>> patch.
>
> In the example you gave earlier, the equi join on partition key was
> there but it was replaced by individual constant assignment clauses.
> So if we keep the original restrictclause in there with a new flag
> indicating that it's redundant, have_partkey_equi_join will still be
> able to use it without much change. Depending upon where all we need
> to use avoid restrictclauses with the redundant flag, this might be an
> easier approach. However, with Tom's idea partition-wise join may be
> used even when there is no equi-join between partition keys but there
> are clauses like pk = const for all tables involved and const is the
> same for all such tables.
>
> In the spirit of small improvement made to the performance of
> have_partkey_equi_join(), pk_has_clause should be renamed as
> pk_known_equal and pks_known_equal as num_equal_pks.
>
> The loop traversing the partition keys at a given position, may be
> optimized further if we pass lists to exprs_known_equal() which in
> turns checks whether one expression from each list is member of a
> given EC. This will avoid traversing all equivalence classes for each
> partition key expression, which can be a huge improvement when there
> are many ECs. But I think if one of the partition key expression at a
> given position is member of an equivalence class all the other
> partition key expressions at that position should be part of that
> equivalence class since there should be an equi-join between those. So
> the loop in loop may not be required to start with.
Richard, any thoughts on Ashutosh's comments?
Regards,
--
-David
[email protected]
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2021-07-21 08:44 Richard Guo <[email protected]>
parent: Ashutosh Bapat <[email protected]>
1 sibling, 1 reply; 32+ messages in thread
From: Richard Guo @ 2021-07-21 08:44 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; Pg Hackers <[email protected]>; Richard Guo <[email protected]>; [email protected]
On Fri, Nov 27, 2020 at 8:05 PM Ashutosh Bapat <[email protected]>
wrote:
> On Tue, Nov 10, 2020 at 2:43 PM Richard Guo <[email protected]>
> wrote:
> > Thanks Anastasia. I've rebased the patch with latest master.
> >
> > To recap, the problem we are fixing here is when generating join clauses
> > from equivalence classes, we only select the joinclause with the 'best
> > score', or the first joinclause with a score of 3. This may cause us to
> > miss some joinclause on partition keys and thus fail to generate
> > partitionwise join.
> >
> > The initial idea for the fix is to create all the RestrictInfos from ECs
> > in order to check whether there exist equi-join conditions involving
> > pairs of matching partition keys of the relations being joined for all
> > partition keys. And then Tom proposed a much better idea which leverages
> > function exprs_known_equal() to tell whether the partkeys can be found
> > in the same eclass, which is the current implementation in the latest
> > patch.
> >
>
> In the example you gave earlier, the equi join on partition key was
> there but it was replaced by individual constant assignment clauses.
> So if we keep the original restrictclause in there with a new flag
> indicating that it's redundant, have_partkey_equi_join will still be
> able to use it without much change. Depending upon where all we need
> to use avoid restrictclauses with the redundant flag, this might be an
> easier approach. However, with Tom's idea partition-wise join may be
> used even when there is no equi-join between partition keys but there
> are clauses like pk = const for all tables involved and const is the
> same for all such tables.
>
Correct. So with Tom's idea partition-wise join can cope with clauses
such as 'foo.k1 = bar.k1 and foo.k2 = 16 and bar.k2 = 16'.
>
> In the spirit of small improvement made to the performance of
> have_partkey_equi_join(), pk_has_clause should be renamed as
> pk_known_equal and pks_known_equal as num_equal_pks.
>
Thanks for the suggestion. Will do that in the new version of patch.
>
> The loop traversing the partition keys at a given position, may be
> optimized further if we pass lists to exprs_known_equal() which in
> turns checks whether one expression from each list is member of a
> given EC. This will avoid traversing all equivalence classes for each
> partition key expression, which can be a huge improvement when there
> are many ECs. But I think if one of the partition key expression at a
> given position is member of an equivalence class all the other
> partition key expressions at that position should be part of that
> equivalence class since there should be an equi-join between those. So
> the loop in loop may not be required to start with.
>
Good point. Quote from one of Tom's earlier emails,
"It seems at least plausible that in the cases we care about, all the
partkeys on each side would be in the same eclasses anyway, so that
comparing the first members of each list would be sufficient."
But I'm not sure if this holds true in all cases. However, since each
base relation within the join contributes only one partexpr, the number
of partexprs would only be equal to the join degree. Thus the loop in
loop may not be a big problem?
PS. Sorry for delaying so long time!
Thanks
Richard
Attachments:
[application/octet-stream] v5-0001-Fix-up-partitionwise-join.patch (14.6K, ../../CAMbWs48C0ux9mc+AS7D7DP6CyJxjQS=2mxQTpMJJKEeTua5wbQ@mail.gmail.com/3-v5-0001-Fix-up-partitionwise-join.patch)
download | inline diff:
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 6f1abbe47d..aa05c60bd8 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2375,15 +2375,17 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo)
* Detect whether two expressions are known equal due to equivalence
* relationships.
*
- * Actually, this only shows that the expressions are equal according
- * to some opfamily's notion of equality --- but we only use it for
- * selectivity estimation, so a fuzzy idea of equality is OK.
+ * If opfamily is given, the expressions must be known equal per the semantics
+ * of that opfamily (note it has to be a btree opfamily, since those are the
+ * only opfamilies equivclass.c deals with). If opfamily is InvalidOid, we'll
+ * return true if they're equal according to any opfamily, which is fuzzy but
+ * OK for estimation purposes.
*
* Note: does not bother to check for "equal(item1, item2)"; caller must
* check that case if it's possible to pass identical items.
*/
bool
-exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
+exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily)
{
ListCell *lc1;
@@ -2398,6 +2400,17 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
if (ec->ec_has_volatile)
continue;
+ /*
+ * It's okay to consider ec_broken ECs here. Brokenness just means we
+ * couldn't derive all the implied clauses we'd have liked to; it does
+ * not invalidate our knowledge that the members are equal.
+ */
+
+ /* Ignore if this EC doesn't use specified opfamily */
+ if (OidIsValid(opfamily) &&
+ !list_member_oid(ec->ec_opfamilies, opfamily))
+ continue;
+
foreach(lc2, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
@@ -2426,8 +2439,7 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2)
* (In principle there might be more than one matching eclass if multiple
* collations are involved, but since collation doesn't matter for equality,
* we ignore that fine point here.) This is much like exprs_known_equal,
- * except that we insist on the comparison operator matching the eclass, so
- * that the result is definite not approximate.
+ * except for the format of the input.
*
* On success, we also set fkinfo->eclass[colno] to the matching eclass,
* and set fkinfo->fk_eclass_member[colno] to the eclass member for the
@@ -2468,7 +2480,7 @@ match_eclasses_to_foreign_key_col(PlannerInfo *root,
/* Never match to a volatile EC */
if (ec->ec_has_volatile)
continue;
- /* Note: it seems okay to match to "broken" eclasses here */
+ /* It's okay to consider "broken" ECs here, see exprs_known_equal */
foreach(lc2, ec->ec_members)
{
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index e105a4d5f1..40b47ca85e 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -56,10 +56,10 @@ static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
static void set_foreign_rel_properties(RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel);
static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
-static void build_joinrel_partition_info(RelOptInfo *joinrel,
+static void build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
List *restrictlist, JoinType jointype);
-static bool have_partkey_equi_join(RelOptInfo *joinrel,
+static bool have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *rel1, RelOptInfo *rel2,
JoinType jointype, List *restrictlist);
static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
@@ -718,8 +718,8 @@ build_join_rel(PlannerInfo *root,
joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel);
/* Store the partition information. */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
- sjinfo->jointype);
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel,
+ restrictlist, sjinfo->jointype);
/*
* Set estimates of the joinrel's size.
@@ -884,8 +884,8 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->has_eclass_joins = parent_joinrel->has_eclass_joins;
/* Is the join between partitions itself partitioned? */
- build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
- jointype);
+ build_joinrel_partition_info(root, joinrel, outer_rel, inner_rel,
+ restrictlist, jointype);
/* Child joinrel is parallel safe if parent is parallel safe. */
joinrel->consider_parallel = parent_joinrel->consider_parallel;
@@ -1642,9 +1642,9 @@ find_param_path_info(RelOptInfo *rel, Relids required_outer)
* partitioned join relation.
*/
static void
-build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
- RelOptInfo *inner_rel, List *restrictlist,
- JoinType jointype)
+build_joinrel_partition_info(PlannerInfo *root, RelOptInfo *joinrel,
+ RelOptInfo *outer_rel, RelOptInfo *inner_rel,
+ List *restrictlist, JoinType jointype)
{
PartitionScheme part_scheme;
@@ -1670,7 +1670,7 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
!outer_rel->consider_partitionwise_join ||
!inner_rel->consider_partitionwise_join ||
outer_rel->part_scheme != inner_rel->part_scheme ||
- !have_partkey_equi_join(joinrel, outer_rel, inner_rel,
+ !have_partkey_equi_join(root, joinrel, outer_rel, inner_rel,
jointype, restrictlist))
{
Assert(!IS_PARTITIONED_REL(joinrel));
@@ -1713,15 +1713,14 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
* partition keys.
*/
static bool
-have_partkey_equi_join(RelOptInfo *joinrel,
+have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *rel1, RelOptInfo *rel2,
JoinType jointype, List *restrictlist)
{
PartitionScheme part_scheme = rel1->part_scheme;
+ bool pk_known_equal[PARTITION_MAX_KEYS];
+ int num_equal_pks;
ListCell *lc;
- int cnt_pks;
- bool pk_has_clause[PARTITION_MAX_KEYS];
- bool strict_op;
/*
* This function must only be called when the joined relations have same
@@ -1730,13 +1729,19 @@ have_partkey_equi_join(RelOptInfo *joinrel,
Assert(rel1->part_scheme == rel2->part_scheme);
Assert(part_scheme);
- memset(pk_has_clause, 0, sizeof(pk_has_clause));
+ /* We use a bool array to track which partkey columns are known equal */
+ memset(pk_known_equal, 0, sizeof(pk_known_equal));
+ /* ... as well as a count of how many are known equal */
+ num_equal_pks = 0;
+
+ /* First, look through the join's restriction clauses */
foreach(lc, restrictlist)
{
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
OpExpr *opexpr;
Expr *expr1;
Expr *expr2;
+ bool strict_op;
int ipk1;
int ipk2;
@@ -1796,11 +1801,15 @@ have_partkey_equi_join(RelOptInfo *joinrel,
if (ipk1 != ipk2)
continue;
+ /* Ignore clause if we already proved these keys equal. */
+ if (pk_known_equal[ipk1])
+ continue;
+
/*
* The clause allows partitionwise join only if it uses the same
* operator family as that specified by the partition key.
*/
- if (rel1->part_scheme->strategy == PARTITION_STRATEGY_HASH)
+ if (part_scheme->strategy == PARTITION_STRATEGY_HASH)
{
if (!OidIsValid(rinfo->hashjoinoperator) ||
!op_in_opfamily(rinfo->hashjoinoperator,
@@ -1812,17 +1821,89 @@ have_partkey_equi_join(RelOptInfo *joinrel,
continue;
/* Mark the partition key as having an equi-join clause. */
- pk_has_clause[ipk1] = true;
+ pk_known_equal[ipk1] = true;
+
+ /* We can stop examining clauses once we prove all keys equal. */
+ if (++num_equal_pks == part_scheme->partnatts)
+ return true;
}
- /* Check whether every partition key has an equi-join condition. */
- for (cnt_pks = 0; cnt_pks < part_scheme->partnatts; cnt_pks++)
+ /*
+ * Also check to see if any keys are known equal by equivclass.c. In most
+ * cases there would have been a join restriction clause generated from
+ * any EC that had such knowledge, but there might be no such clause, or
+ * it might happen to constrain other members of the ECs than the ones we
+ * are looking for.
+ */
+ for (int ipk = 0; ipk < part_scheme->partnatts; ipk++)
{
- if (!pk_has_clause[cnt_pks])
- return false;
+ Oid btree_opfamily;
+
+ /* Ignore if we already proved these keys equal. */
+ if (pk_known_equal[ipk])
+ continue;
+
+ /*
+ * We need a btree opfamily to ask equivclass.c about. If the
+ * partopfamily is a hash opfamily, look up its equality operator, and
+ * select some btree opfamily that that operator is part of. (Any
+ * such opfamily should be good enough, since equivclass.c will track
+ * multiple opfamilies as appropriate.)
+ */
+ if (part_scheme->strategy == PARTITION_STRATEGY_HASH)
+ {
+ Oid eq_op;
+ List *eq_opfamilies;
+
+ eq_op = get_opfamily_member(part_scheme->partopfamily[ipk],
+ part_scheme->partopcintype[ipk],
+ part_scheme->partopcintype[ipk],
+ HTEqualStrategyNumber);
+ if (!OidIsValid(eq_op))
+ break; /* we're not going to succeed */
+ eq_opfamilies = get_mergejoin_opfamilies(eq_op);
+ if (eq_opfamilies == NIL)
+ break; /* we're not going to succeed */
+ btree_opfamily = linitial_oid(eq_opfamilies);
+ }
+ else
+ btree_opfamily = part_scheme->partopfamily[ipk];
+
+ /*
+ * We consider only non-nullable partition keys here; nullable ones
+ * would not be treated as part of the same equivalence classes as
+ * non-nullable ones.
+ */
+ foreach(lc, rel1->partexprs[ipk])
+ {
+ Node *expr1 = (Node *) lfirst(lc);
+ ListCell *lc2;
+
+ foreach(lc2, rel2->partexprs[ipk])
+ {
+ Node *expr2 = (Node *) lfirst(lc2);
+
+ if (exprs_known_equal(root, expr1, expr2, btree_opfamily))
+ {
+ pk_known_equal[ipk] = true;
+ break;
+ }
+ }
+ if (pk_known_equal[ipk])
+ break;
+ }
+
+ if (pk_known_equal[ipk])
+ {
+ /* We can stop examining keys once we prove all keys equal. */
+ if (++num_equal_pks == part_scheme->partnatts)
+ return true;
+ }
+ else
+ break; /* no chance to succeed, give up */
}
- return true;
+ return false;
}
/*
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 0c8c05f6c2..090ce8656e 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -3265,10 +3265,11 @@ add_unique_group_var(PlannerInfo *root, List *varinfos,
/*
* Drop known-equal vars, but only if they belong to different
- * relations (see comments for estimate_num_groups)
+ * relations (see comments for estimate_num_groups). We aren't too
+ * fussy about the semantics of "equal" here.
*/
if (vardata->rel != varinfo->rel &&
- exprs_known_equal(root, var, varinfo->var))
+ exprs_known_equal(root, var, varinfo->var, InvalidOid))
{
if (varinfo->ndistinct <= ndistinct)
{
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f1d111063c..e6488d3f37 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -157,7 +157,8 @@ extern List *generate_join_implied_equalities_for_ecs(PlannerInfo *root,
Relids join_relids,
Relids outer_relids,
RelOptInfo *inner_rel);
-extern bool exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2);
+extern bool exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2,
+ Oid opfamily);
extern EquivalenceClass *match_eclasses_to_foreign_key_col(PlannerInfo *root,
ForeignKeyOptInfo *fkinfo,
int colno);
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 27f7525b3e..2bdee1f804 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -62,6 +62,45 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
450 | 0450 | 450 | 0450
(4 rows)
+-- inner join with partially-redundant join clauses
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ QUERY PLAN
+---------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Merge Join
+ Merge Cond: (t1_1.a = t2_1.a)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on prt2_p1 t2_1
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_2.a = t2_2.a)
+ -> Seq Scan on prt1_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_2
+ Filter: (a = b)
+ -> Hash Join
+ Hash Cond: (t1_3.a = t2_3.a)
+ -> Seq Scan on prt1_p3 t1_3
+ -> Hash
+ -> Seq Scan on prt2_p3 t2_3
+ Filter: (a = b)
+(22 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+ a | c | b | c
+----+------+----+------
+ 0 | 0000 | 0 | 0000
+ 6 | 0006 | 6 | 0006
+ 12 | 0012 | 12 | 0012
+ 18 | 0018 | 18 | 0018
+ 24 | 0024 | 24 | 0024
+(5 rows)
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index d97b5b69ff..6b7d6899dd 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -34,6 +34,11 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
+-- inner join with partially-redundant join clauses
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
+
-- left outer join, with whole-row reference; partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2021-10-05 17:19 Jaime Casanova <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Jaime Casanova @ 2021-10-05 17:19 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Anastasia Lubennikova <[email protected]>; Pg Hackers <[email protected]>; Richard Guo <[email protected]>; [email protected]
On Wed, Jul 21, 2021 at 04:44:53PM +0800, Richard Guo wrote:
> On Fri, Nov 27, 2020 at 8:05 PM Ashutosh Bapat <[email protected]>
> wrote:
>
> >
> > In the example you gave earlier, the equi join on partition key was
> > there but it was replaced by individual constant assignment clauses.
> > So if we keep the original restrictclause in there with a new flag
> > indicating that it's redundant, have_partkey_equi_join will still be
> > able to use it without much change. Depending upon where all we need
> > to use avoid restrictclauses with the redundant flag, this might be an
> > easier approach. However, with Tom's idea partition-wise join may be
> > used even when there is no equi-join between partition keys but there
> > are clauses like pk = const for all tables involved and const is the
> > same for all such tables.
> >
>
> Correct. So with Tom's idea partition-wise join can cope with clauses
> such as 'foo.k1 = bar.k1 and foo.k2 = 16 and bar.k2 = 16'.
>
>
> >
> > In the spirit of small improvement made to the performance of
> > have_partkey_equi_join(), pk_has_clause should be renamed as
> > pk_known_equal and pks_known_equal as num_equal_pks.
> >
>
> Thanks for the suggestion. Will do that in the new version of patch.
>
Hi Richard,
We are marking this CF entry as "Returned with Feedback", which means
you are encouraged to send a new patch (and create a new entry for a
future CF for it) with the suggested changes.
--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2021-11-22 07:04 Richard Guo <[email protected]>
parent: Jaime Casanova <[email protected]>
0 siblings, 1 reply; 32+ messages in thread
From: Richard Guo @ 2021-11-22 07:04 UTC (permalink / raw)
To: Jaime Casanova <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Anastasia Lubennikova <[email protected]>; Pg Hackers <[email protected]>; Richard Guo <[email protected]>; [email protected]
On Wed, Oct 6, 2021 at 1:19 AM Jaime Casanova <[email protected]>
wrote:
> On Wed, Jul 21, 2021 at 04:44:53PM +0800, Richard Guo wrote:
> > On Fri, Nov 27, 2020 at 8:05 PM Ashutosh Bapat <
> [email protected]>
> > wrote:
> >
> > >
> > > In the example you gave earlier, the equi join on partition key was
> > > there but it was replaced by individual constant assignment clauses.
> > > So if we keep the original restrictclause in there with a new flag
> > > indicating that it's redundant, have_partkey_equi_join will still be
> > > able to use it without much change. Depending upon where all we need
> > > to use avoid restrictclauses with the redundant flag, this might be an
> > > easier approach. However, with Tom's idea partition-wise join may be
> > > used even when there is no equi-join between partition keys but there
> > > are clauses like pk = const for all tables involved and const is the
> > > same for all such tables.
> > >
> >
> > Correct. So with Tom's idea partition-wise join can cope with clauses
> > such as 'foo.k1 = bar.k1 and foo.k2 = 16 and bar.k2 = 16'.
> >
> >
> > >
> > > In the spirit of small improvement made to the performance of
> > > have_partkey_equi_join(), pk_has_clause should be renamed as
> > > pk_known_equal and pks_known_equal as num_equal_pks.
> > >
> >
> > Thanks for the suggestion. Will do that in the new version of patch.
> >
>
> Hi Richard,
>
> We are marking this CF entry as "Returned with Feedback", which means
> you are encouraged to send a new patch (and create a new entry for a
> future CF for it) with the suggested changes.
>
Hi,
The suggested changes have already been included in v5 patch. Sorry for
the confusion.
Verified that the patch still applies and works on latest master. So I'm
moving it to the next CF (which is Commitfest 2022-01). Please correct
me if this is not the right thing to do.
Thanks
Richard
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: A problem about partitionwise join
@ 2022-08-01 20:24 Jacob Champion <[email protected]>
parent: Richard Guo <[email protected]>
0 siblings, 0 replies; 32+ messages in thread
From: Jacob Champion @ 2022-08-01 20:24 UTC (permalink / raw)
To: Richard Guo <[email protected]>; Jaime Casanova <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; Anastasia Lubennikova <[email protected]>; Pg Hackers <[email protected]>; [email protected]
As discussed in [1], we're taking this opportunity to return some
patchsets that don't appear to be getting enough reviewer interest.
This is not a rejection, since we don't necessarily think there's
anything unacceptable about the entry, but it differs from a standard
"Returned with Feedback" in that there's probably not much actionable
feedback at all. Rather than code changes, what this patch needs is more
community interest. You might
- ask people for help with your approach,
- see if there are similar patches that your code could supplement,
- get interested parties to agree to review your patch in a CF, or
- possibly present the functionality in a way that's easier to review
overall.
(Doing these things is no guarantee that there will be interest, but
it's hopefully better than endlessly rebasing a patchset that is not
receiving any feedback from the community.)
Once you think you've built up some community support and the patchset
is ready for review, you (or any interested party) can resurrect the
patch entry by visiting
https://commitfest.postgresql.org/38/2266/
and changing the status to "Needs Review", and then changing the
status again to "Move to next CF". (Don't forget the second step;
hopefully we will have streamlined this in the near future!)
Thanks,
--Jacob
[1]
https://postgr.es/m/flat/0ab66589-2f71-69b3-2002-49e821740b0d%40timescale.com
^ permalink raw reply [nested|flat] 32+ messages in thread
end of thread, other threads:[~2022-08-01 20:24 UTC | newest]
Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-08-26 09:32 A problem about partitionwise join Richard Guo <[email protected]>
2019-08-27 00:51 ` Amit Langote <[email protected]>
2019-08-27 07:56 ` Richard Guo <[email protected]>
2019-08-28 10:49 ` Etsuro Fujita <[email protected]>
2019-08-29 09:44 ` Richard Guo <[email protected]>
2019-08-29 18:08 ` Etsuro Fujita <[email protected]>
2019-08-30 03:15 ` Richard Guo <[email protected]>
2019-08-30 06:13 ` Etsuro Fujita <[email protected]>
2019-11-26 11:35 ` Etsuro Fujita <[email protected]>
2019-11-29 03:03 ` Michael Paquier <[email protected]>
2019-11-29 03:07 ` Richard Guo <[email protected]>
2019-11-29 03:35 ` Etsuro Fujita <[email protected]>
2020-01-19 04:01 ` Richard Guo <[email protected]>
2020-04-04 20:37 ` Tom Lane <[email protected]>
2020-04-08 04:58 ` Richard Guo <[email protected]>
2020-04-08 17:07 ` Tom Lane <[email protected]>
2020-04-09 04:14 ` Richard Guo <[email protected]>
2020-04-09 05:24 ` Tom Lane <[email protected]>
2020-04-09 14:06 ` Ashutosh Bapat <[email protected]>
2020-11-06 15:25 ` Anastasia Lubennikova <[email protected]>
2020-11-10 09:12 ` Richard Guo <[email protected]>
2020-11-27 12:05 ` Ashutosh Bapat <[email protected]>
2021-03-09 16:22 ` David Steele <[email protected]>
2021-07-21 08:44 ` Richard Guo <[email protected]>
2021-10-05 17:19 ` Jaime Casanova <[email protected]>
2021-11-22 07:04 ` Richard Guo <[email protected]>
2022-08-01 20:24 ` Jacob Champion <[email protected]>
2019-09-10 20:48 ` Alvaro Herrera from 2ndQuadrant <[email protected]>
2019-09-11 03:56 ` Richard Guo <[email protected]>
2019-09-20 04:48 ` Dilip Kumar <[email protected]>
2019-09-20 09:02 ` Richard Guo <[email protected]>
2019-09-21 06:28 ` Dilip Kumar <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox