Skip to content

Commit 3c90dcd

Browse files
tglsfdctglsfdc
committed
Fix calculation of relid sets for partitionwise child joins.
Applying add_outer_joins_to_relids() to a child join doesn't actually work, even if we've built a SpecialJoinInfo specialized to the child, because that function will also compare the join's relids to elements of the main join_info_list, which only deal in regular relids not child relids. This mistake escaped detection by the existing partitionwise join tests because they didn't test any cases where add_outer_joins_to_relids() needs to add additional OJ relids (that is, any cases where join reordering per identity 3 is possible). Instead, let's apply adjust_child_relids() to the relids of the parent join. This requires minor code reordering to collect the relevant AppendRelInfo structures first, but that's work we'd do shortly anyway. Report and fix by Richard Guo; cosmetic changes by me Discussion: https://postgr.es/m/CAMbWs49NCNbyubZWgci3o=_OTY=snCfAPtMnM-32f3mm-K-Ckw@mail.gmail.com
1 parent 7c7412c commit 3c90dcd

File tree

4 files changed

+75
-12
lines changed

4 files changed

+75
-12
lines changed

‎src/backend/optimizer/path/joinrels.c

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1640,13 +1640,15 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
16401640
child_rel1->relids,
16411641
child_rel2->relids);
16421642

1643-
/* Build correct join relids for child join */
1644-
child_joinrelids = bms_union(child_rel1->relids, child_rel2->relids);
1645-
child_joinrelids = add_outer_joins_to_relids(root, child_joinrelids,
1646-
child_sjinfo, NULL);
1647-
16481643
/* Find the AppendRelInfo structures */
1649-
appinfos = find_appinfos_by_relids(root, child_joinrelids, &nappinfos);
1644+
appinfos = find_appinfos_by_relids(root,
1645+
bms_union(child_rel1->relids,
1646+
child_rel2->relids),
1647+
&nappinfos);
1648+
1649+
/* Build correct join relids for child join */
1650+
child_joinrelids = adjust_child_relids(joinrel->relids,
1651+
nappinfos, appinfos);
16501652

16511653
/*
16521654
* Construct restrictions applicable to the child join from those

‎src/backend/optimizer/util/relnode.c

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -871,10 +871,19 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
871871
/* The parent joinrel should have consider_partitionwise_join set. */
872872
Assert(parent_joinrel->consider_partitionwise_join);
873873

874+
/*
875+
* Find the AppendRelInfo structures for the child baserels. We'll need
876+
* these for computing the child join's relid set, and later for mapping
877+
* Vars to the child rel.
878+
*/
879+
appinfos = find_appinfos_by_relids(root,
880+
bms_union(outer_rel->relids,
881+
inner_rel->relids),
882+
&nappinfos);
883+
874884
joinrel->reloptkind = RELOPT_OTHER_JOINREL;
875-
joinrel->relids = bms_union(outer_rel->relids, inner_rel->relids);
876-
joinrel->relids = add_outer_joins_to_relids(root, joinrel->relids, sjinfo,
877-
NULL);
885+
joinrel->relids = adjust_child_relids(parent_joinrel->relids,
886+
nappinfos, appinfos);
878887
joinrel->rows = 0;
879888
/* cheap startup cost is interesting iff not all tuples to be retrieved */
880889
joinrel->consider_startup = (root->tuple_fraction > 0);
@@ -935,9 +944,6 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
935944
/* Compute information relevant to foreign relations. */
936945
set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
937946

938-
/* Compute information needed for mapping Vars to the child rel */
939-
appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos);
940-
941947
/* Set up reltarget struct */
942948
build_child_join_reltarget(root, parent_joinrel, joinrel,
943949
nappinfos, appinfos);

‎src/test/regress/expected/partition_join.out

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,52 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
6262
450 | 0450 | 450 | 0450
6363
(4 rows)
6464

65+
-- left outer join, 3-way
66+
EXPLAIN (COSTS OFF)
67+
SELECT COUNT(*) FROM prt1 t1
68+
LEFT JOIN prt1 t2 ON t1.a = t2.a
69+
LEFT JOIN prt1 t3 ON t2.a = t3.a;
70+
QUERY PLAN
71+
--------------------------------------------------------
72+
Aggregate
73+
-> Append
74+
-> Hash Left Join
75+
Hash Cond: (t2_1.a = t3_1.a)
76+
-> Hash Left Join
77+
Hash Cond: (t1_1.a = t2_1.a)
78+
-> Seq Scan on prt1_p1 t1_1
79+
-> Hash
80+
-> Seq Scan on prt1_p1 t2_1
81+
-> Hash
82+
-> Seq Scan on prt1_p1 t3_1
83+
-> Hash Left Join
84+
Hash Cond: (t2_2.a = t3_2.a)
85+
-> Hash Left Join
86+
Hash Cond: (t1_2.a = t2_2.a)
87+
-> Seq Scan on prt1_p2 t1_2
88+
-> Hash
89+
-> Seq Scan on prt1_p2 t2_2
90+
-> Hash
91+
-> Seq Scan on prt1_p2 t3_2
92+
-> Hash Left Join
93+
Hash Cond: (t2_3.a = t3_3.a)
94+
-> Hash Left Join
95+
Hash Cond: (t1_3.a = t2_3.a)
96+
-> Seq Scan on prt1_p3 t1_3
97+
-> Hash
98+
-> Seq Scan on prt1_p3 t2_3
99+
-> Hash
100+
-> Seq Scan on prt1_p3 t3_3
101+
(29 rows)
102+
103+
SELECT COUNT(*) FROM prt1 t1
104+
LEFT JOIN prt1 t2 ON t1.a = t2.a
105+
LEFT JOIN prt1 t3 ON t2.a = t3.a;
106+
count
107+
-------
108+
300
109+
(1 row)
110+
65111
-- left outer join, with whole-row reference; partitionwise join does not apply
66112
EXPLAIN (COSTS OFF)
67113
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;

‎src/test/regress/sql/partition_join.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,6 +34,15 @@ EXPLAIN (COSTS OFF)
3434
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;
3535
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;
3636

37+
-- left outer join, 3-way
38+
EXPLAIN (COSTS OFF)
39+
SELECT COUNT(*) FROM prt1 t1
40+
LEFT JOIN prt1 t2 ON t1.a = t2.a
41+
LEFT JOIN prt1 t3 ON t2.a = t3.a;
42+
SELECT COUNT(*) FROM prt1 t1
43+
LEFT JOIN prt1 t2 ON t1.a = t2.a
44+
LEFT JOIN prt1 t3 ON t2.a = t3.a;
45+
3746
-- left outer join, with whole-row reference; partitionwise join does not apply
3847
EXPLAIN (COSTS OFF)
3948
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;

0 commit comments

Comments
 (0)