Skip to content

Commit e2f1eb0

Browse files
robertmhaasrobertmhaas
committed
Implement partition-wise grouping/aggregation.
If the partition keys of input relation are part of the GROUP BY clause, all the rows belonging to a given group come from a single partition. This allows aggregation/grouping over a partitioned relation to be broken down * into aggregation/grouping on each partition. This should be no worse, and often better, than the normal approach. If the GROUP BY clause does not contain all the partition keys, we can still perform partial aggregation for each partition and then finalize aggregation after appending the partial results. This is less certain to be a win, but it's still useful. Jeevan Chalke, Ashutosh Bapat, Robert Haas. The larger patch series of which this patch is a part was also reviewed and tested by Antonin Houska, Rajkumar Raghuwanshi, David Rowley, Dilip Kumar, Konstantin Knizhnik, Pascal Legrand, and Rafia Sabih. Discussion: http://postgr.es/m/CAM2+6=V64_xhstVHie0Rz=KPEQnLJMZt_e314P0jaT_oJ9MR8A@mail.gmail.com
1 parent 2058d6a commit e2f1eb0

File tree

17 files changed

+2544
-206
lines changed

17 files changed

+2544
-206
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3821,6 +3821,26 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
38213821
</listitem>
38223822
</varlistentry>
38233823

3824+
<varlistentry id="guc-enable-partitionwise-aggregate" xreflabel="enable_partitionwise_aggregate">
3825+
<term><varname>enable_partitionwise_aggregate</varname> (<type>boolean</type>)
3826+
<indexterm>
3827+
<primary><varname>enable_partitionwise_aggregate</varname> configuration parameter</primary>
3828+
</indexterm>
3829+
</term>
3830+
<listitem>
3831+
<para>
3832+
Enables or disables the query planner's use of partitionwise grouping
3833+
or aggregation, which allows grouping or aggregation on a partitioned
3834+
tables performed separately for each partition. If the <literal>GROUP
3835+
BY</literal> clause does not include the partition keys, only partial
3836+
aggregation can be performed on a per-partition basis, and
3837+
finalization must be performed later. Because partitionwise grouping
3838+
or aggregation can use significantly more CPU time and memory during
3839+
planning, the default is <literal>off</literal>.
3840+
</para>
3841+
</listitem>
3842+
</varlistentry>
3843+
38243844
<varlistentry id="guc-enable-seqscan" xreflabel="enable_seqscan">
38253845
<term><varname>enable_seqscan</varname> (<type>boolean</type>)
38263846
<indexterm>

‎src/backend/optimizer/README

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1079,6 +1079,7 @@ busy for a long time to come.
10791079

10801080
Partitionwise joins
10811081
-------------------
1082+
10821083
A join between two similarly partitioned tables can be broken down into joins
10831084
between their matching partitions if there exists an equi-join condition
10841085
between the partition keys of the joining tables. The equi-join between
@@ -1102,3 +1103,16 @@ any two partitioned relations with same partitioning scheme point to the same
11021103
PartitionSchemeData object. This reduces memory consumed by
11031104
PartitionSchemeData objects and makes it easy to compare the partition schemes
11041105
of joining relations.
1106+
1107+
Partition-wise aggregates/grouping
1108+
----------------------------------
1109+
1110+
If the GROUP BY clause has contains all of the partition keys, all the rows
1111+
that belong to a given group must come from a single partition; therefore,
1112+
aggregation can be done completely separately for each partition. Otherwise,
1113+
partial aggregates can be computed for each partition, and then finalized
1114+
after appending the results from the individual partitions. This technique of
1115+
breaking down aggregation or grouping over a partitioned relation into
1116+
aggregation or grouping over its partitions is called partitionwise
1117+
aggregation. Especially when the partition keys match the GROUP BY clause,
1118+
this can be significantly faster than the regular method.

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

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -134,8 +134,6 @@ static void subquery_push_qual(Query *subquery,
134134
static void recurse_push_qual(Node *setOp, Query *topquery,
135135
RangeTblEntry *rte, Index rti, Node *qual);
136136
static void remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel);
137-
static void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
138-
List *live_childrels);
139137

140138

141139
/*
@@ -1326,7 +1324,7 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
13261324
* parameterization or ordering. Similarly it collects partial paths from
13271325
* non-dummy children to create partial append paths.
13281326
*/
1329-
static void
1327+
void
13301328
add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
13311329
List *live_childrels)
13321330
{
@@ -1413,8 +1411,12 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
14131411
* If child has an unparameterized cheapest-total path, add that to
14141412
* the unparameterized Append path we are constructing for the parent.
14151413
* If not, there's no workable unparameterized path.
1414+
*
1415+
* With partitionwise aggregates, the child rel's pathlist may be
1416+
* empty, so don't assume that a path exists here.
14161417
*/
1417-
if (childrel->cheapest_total_path->param_info == NULL)
1418+
if (childrel->pathlist != NIL &&
1419+
childrel->cheapest_total_path->param_info == NULL)
14181420
accumulate_append_subpath(childrel->cheapest_total_path,
14191421
&subpaths, NULL);
14201422
else
@@ -1682,6 +1684,13 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
16821684
RelOptInfo *childrel = (RelOptInfo *) lfirst(lcr);
16831685
Path *subpath;
16841686

1687+
if (childrel->pathlist == NIL)
1688+
{
1689+
/* failed to make a suitable path for this child */
1690+
subpaths_valid = false;
1691+
break;
1692+
}
1693+
16851694
subpath = get_cheapest_parameterized_child_path(root,
16861695
childrel,
16871696
required_outer);

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,7 @@ bool enable_mergejoin = true;
135135
bool enable_hashjoin = true;
136136
bool enable_gathermerge = true;
137137
bool enable_partitionwise_join = false;
138+
bool enable_partitionwise_aggregate = false;
138139
bool enable_parallel_append = true;
139140
bool enable_parallel_hash = true;
140141

‎src/backend/optimizer/plan/createplan.c

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1670,7 +1670,15 @@ create_sort_plan(PlannerInfo *root, SortPath *best_path, int flags)
16701670
subplan = create_plan_recurse(root, best_path->subpath,
16711671
flags | CP_SMALL_TLIST);
16721672

1673-
plan = make_sort_from_pathkeys(subplan, best_path->path.pathkeys, NULL);
1673+
/*
1674+
* make_sort_from_pathkeys() indirectly calls find_ec_member_for_tle(),
1675+
* which will ignore any child EC members that don't belong to the given
1676+
* relids. Thus, if this sort path is based on a child relation, we must
1677+
* pass its relids.
1678+
*/
1679+
plan = make_sort_from_pathkeys(subplan, best_path->path.pathkeys,
1680+
IS_OTHER_REL(best_path->subpath->parent) ?
1681+
best_path->path.parent->relids : NULL);
16741682

16751683
copy_generic_path_info(&plan->plan, (Path *) best_path);
16761684

0 commit comments

Comments
 (0)