($INBOX_DIR/description missing)
help / color / mirror / Atom feedFrom: Antonin Houska <[email protected]>
Subject: [PATCH 3/3] Use also partial paths as the input for grouped paths.
Date: Fri, 4 Nov 2022 15:02:57 +0100
---
src/backend/commands/trigger.c | 2 +-
src/backend/optimizer/path/allpaths.c | 46 +++++-
src/backend/optimizer/util/relnode.c | 46 +++---
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/test/regress/expected/agg_pushdown.out | 156 ++++++++++++++++++
src/test/regress/expected/triggers.out | 2 +-
src/test/regress/sql/agg_pushdown.sql | 65 ++++++++
7 files changed, 286 insertions(+), 32 deletions(-)
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 182e6161e0..e64145e710 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -264,7 +264,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("\"%s\" is a partitioned table",
RelationGetRelationName(rel)),
- errdetail("Triggers on partitioned tables cannot have transition tables.")));
+ errdetail("ROW triggers with transition tables are not supported on partitioned tables.")));
}
}
else if (rel->rd_rel->relkind == RELKIND_VIEW)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index f00f900ff4..32b3dedc71 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -130,7 +130,7 @@ static void set_worktable_pathlist(PlannerInfo *root, RelOptInfo *rel,
RangeTblEntry *rte);
static void add_grouped_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath, AggStrategy aggstrategy,
- RelAggInfo *agg_info);
+ RelAggInfo *agg_info, bool partial);
static RelOptInfo *make_rel_from_joinlist(PlannerInfo *root, List *joinlist);
static bool subquery_is_pushdown_safe(Query *subquery, Query *topquery,
pushdown_safety_info *safetyInfo);
@@ -3341,6 +3341,7 @@ generate_grouping_paths(PlannerInfo *root, RelOptInfo *rel_grouped,
RelOptInfo *rel_plain, RelAggInfo *agg_info)
{
ListCell *lc;
+ Path *path;
if (IS_DUMMY_REL(rel_plain))
{
@@ -3350,7 +3351,7 @@ generate_grouping_paths(PlannerInfo *root, RelOptInfo *rel_grouped,
foreach(lc, rel_plain->pathlist)
{
- Path *path = (Path *) lfirst(lc);
+ path = (Path *) lfirst(lc);
/*
* Since the path originates from the non-grouped relation which is
@@ -3364,7 +3365,8 @@ generate_grouping_paths(PlannerInfo *root, RelOptInfo *rel_grouped,
* add_grouped_path() will check whether the path has suitable
* pathkeys.
*/
- add_grouped_path(root, rel_grouped, path, AGG_SORTED, agg_info);
+ add_grouped_path(root, rel_grouped, path, AGG_SORTED, agg_info,
+ false);
/*
* Repeated creation of hash table (for new parameter values) should
@@ -3372,12 +3374,38 @@ generate_grouping_paths(PlannerInfo *root, RelOptInfo *rel_grouped,
* efficiency.
*/
if (path->param_info == NULL)
- add_grouped_path(root, rel_grouped, path, AGG_HASHED, agg_info);
+ add_grouped_path(root, rel_grouped, path, AGG_HASHED, agg_info,
+ false);
}
/* Could not generate any grouped paths? */
if (rel_grouped->pathlist == NIL)
+ {
mark_dummy_rel(rel_grouped);
+ return;
+ }
+
+ /*
+ * Almost the same for partial paths.
+ *
+ * The difference is that parameterized paths are never created, see
+ * add_partial_path() for explanation.
+ */
+ foreach(lc, rel_plain->partial_pathlist)
+ {
+ path = (Path *) lfirst(lc);
+
+ if (path->param_info != NULL)
+ continue;
+
+ path = (Path *) create_projection_path(root, rel_grouped, path,
+ agg_info->agg_input);
+
+ add_grouped_path(root, rel_grouped, path, AGG_SORTED, agg_info,
+ true);
+ add_grouped_path(root, rel_grouped, path, AGG_HASHED, agg_info,
+ true);
+ }
}
/*
@@ -3385,7 +3413,8 @@ generate_grouping_paths(PlannerInfo *root, RelOptInfo *rel_grouped,
*/
static void
add_grouped_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
- AggStrategy aggstrategy, RelAggInfo *agg_info)
+ AggStrategy aggstrategy, RelAggInfo *agg_info,
+ bool partial)
{
Path *agg_path;
@@ -3401,7 +3430,12 @@ add_grouped_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,
/* Add the grouped path to the list of grouped base paths. */
if (agg_path != NULL)
- add_path(rel, (Path *) agg_path);
+ {
+ if (!partial)
+ add_path(rel, (Path *) agg_path);
+ else
+ add_partial_path(rel, (Path *) agg_path);
+ }
}
/*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 1f124b9713..ce2e267e91 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -961,33 +961,12 @@ build_join_rel(PlannerInfo *root,
build_joinrel_partition_info(joinrel, outer_rel, inner_rel,
restrictlist, sjinfo->jointype);
+ /*
+ * Set estimates of the joinrel's size.
+ */
if (!grouped)
- {
- /*
- * Set estimates of the joinrel's size.
- */
set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel,
sjinfo, restrictlist);
-
- /*
- * Set the consider_parallel flag if this joinrel could potentially be
- * scanned within a parallel worker. If this flag is false for either
- * inner_rel or outer_rel, then it must be false for the joinrel also.
- * Even if both are true, there might be parallel-restricted
- * expressions in the targetlist or quals.
- *
- * Note that if there are more than two rels in this relation, they
- * could be divided between inner_rel and outer_rel in any arbitrary
- * way. We assume this doesn't matter, because we should hit all the
- * same baserels and joinclauses while building up to this joinrel no
- * matter which we take; therefore, we should make the same decision
- * here however we get here.
- */
- if (inner_rel->consider_parallel && outer_rel->consider_parallel &&
- is_parallel_safe(root, (Node *) restrictlist) &&
- is_parallel_safe(root, (Node *) joinrel->reltarget->exprs))
- joinrel->consider_parallel = true;
- }
else
{
/*
@@ -1003,6 +982,25 @@ build_join_rel(PlannerInfo *root,
agg_info->input_rows, NULL, NULL);
}
+ /*
+ * Set the consider_parallel flag if this joinrel could potentially be
+ * scanned within a parallel worker. If this flag is false for either
+ * inner_rel or outer_rel, then it must be false for the joinrel also.
+ * Even if both are true, there might be parallel-restricted expressions
+ * in the targetlist or quals.
+ *
+ * Note that if there are more than two rels in this relation, they could
+ * be divided between inner_rel and outer_rel in any arbitrary way. We
+ * assume this doesn't matter, because we should hit all the same baserels
+ * and joinclauses while building up to this joinrel no matter which we
+ * take; therefore, we should make the same decision here however we get
+ * here.
+ */
+ if (inner_rel->consider_parallel && outer_rel->consider_parallel &&
+ is_parallel_safe(root, (Node *) restrictlist) &&
+ is_parallel_safe(root, (Node *) joinrel->reltarget->exprs))
+ joinrel->consider_parallel = true;
+
/* Add the joinrel to the PlannerInfo. */
if (!grouped)
add_join_rel(root, joinrel);
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 868d21c351..89f944d83a 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -385,6 +385,7 @@
#enable_partition_pruning = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
+#enable_agg_pushdown = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
diff --git a/src/test/regress/expected/agg_pushdown.out b/src/test/regress/expected/agg_pushdown.out
index 03a5ccf571..66d36d122e 100644
--- a/src/test/regress/expected/agg_pushdown.out
+++ b/src/test/regress/expected/agg_pushdown.out
@@ -214,3 +214,159 @@ c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
-> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
(13 rows)
+-- Most of the tests above with parallel query processing enforced.
+SET min_parallel_index_scan_size = 0;
+SET min_parallel_table_scan_size = 0;
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+-- Partially aggregate a single relation.
+--
+-- Nestloop join.
+SET enable_nestloop TO on;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS off)
+SELECT p.x, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1
+AS c1 ON c1.parent = p.i GROUP BY p.i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Group Key: p.i
+ -> Gather Merge
+ Workers Planned: 1
+ -> Nested Loop
+ -> Partial GroupAggregate
+ Group Key: c1.parent
+ -> Parallel Index Scan using agg_pushdown_child1_parent_idx on agg_pushdown_child1 c1
+ -> Index Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
+ Index Cond: (i = c1.parent)
+(10 rows)
+
+-- Hash join.
+SET enable_nestloop TO off;
+SET enable_hashjoin TO on;
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1
+AS c1 ON c1.parent = p.i GROUP BY p.i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Group Key: p.i
+ -> Sort
+ Sort Key: p.i
+ -> Gather
+ Workers Planned: 1
+ -> Parallel Hash Join
+ Hash Cond: (c1.parent = p.i)
+ -> Partial GroupAggregate
+ Group Key: c1.parent
+ -> Parallel Index Scan using agg_pushdown_child1_parent_idx on agg_pushdown_child1 c1
+ -> Parallel Hash
+ -> Parallel Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
+(13 rows)
+
+-- Merge join.
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO on;
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1
+AS c1 ON c1.parent = p.i GROUP BY p.i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Group Key: p.i
+ -> Gather Merge
+ Workers Planned: 1
+ -> Merge Join
+ Merge Cond: (c1.parent = p.i)
+ -> Partial GroupAggregate
+ Group Key: c1.parent
+ -> Parallel Index Scan using agg_pushdown_child1_parent_idx on agg_pushdown_child1 c1
+ -> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
+(10 rows)
+
+SET enable_nestloop TO on;
+SET enable_hashjoin TO on;
+-- Perform nestloop join between agg_pushdown_child1 and agg_pushdown_child2
+-- and aggregate the result.
+SET enable_nestloop TO on;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
+agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
+c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Group Key: p.i
+ -> Gather Merge
+ Workers Planned: 2
+ -> Sort
+ Sort Key: p.i
+ -> Nested Loop
+ -> Partial HashAggregate
+ Group Key: c1.parent
+ -> Nested Loop
+ -> Parallel Index Scan using agg_pushdown_child1_pkey on agg_pushdown_child1 c1
+ -> Index Scan using agg_pushdown_child2_pkey on agg_pushdown_child2 c2
+ Index Cond: ((k = c1.j) AND (parent = c1.parent))
+ -> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
+ Index Cond: (i = c1.parent)
+(15 rows)
+
+-- The same for hash join.
+SET enable_nestloop TO off;
+SET enable_hashjoin TO on;
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
+agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
+c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Group Key: p.i
+ -> Gather Merge
+ Workers Planned: 1
+ -> Sort
+ Sort Key: p.i
+ -> Parallel Hash Join
+ Hash Cond: (c1.parent = p.i)
+ -> Partial HashAggregate
+ Group Key: c1.parent
+ -> Parallel Hash Join
+ Hash Cond: ((c1.parent = c2.parent) AND (c1.j = c2.k))
+ -> Parallel Index Scan using agg_pushdown_child1_parent_idx on agg_pushdown_child1 c1
+ -> Parallel Hash
+ -> Parallel Index Scan using agg_pushdown_child2_pkey on agg_pushdown_child2 c2
+ -> Parallel Hash
+ -> Parallel Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
+(17 rows)
+
+-- The same for merge join.
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO on;
+SET enable_seqscan TO off;
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
+agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
+c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Group Key: p.i
+ -> Gather Merge
+ Workers Planned: 2
+ -> Merge Join
+ Merge Cond: (c1.parent = p.i)
+ -> Sort
+ Sort Key: c1.parent
+ -> Partial HashAggregate
+ Group Key: c1.parent
+ -> Merge Join
+ Merge Cond: ((c1.j = c2.k) AND (c1.parent = c2.parent))
+ -> Parallel Index Scan using agg_pushdown_child1_pkey on agg_pushdown_child1 c1
+ -> Index Scan using agg_pushdown_child2_pkey on agg_pushdown_child2 c2
+ -> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p
+(15 rows)
+
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 8b8eadd181..6d80ab1a6d 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2046,7 +2046,7 @@ create trigger failed after update on parted_trig
referencing old table as old_table
for each row execute procedure trigger_nothing();
ERROR: "parted_trig" is a partitioned table
-DETAIL: Triggers on partitioned tables cannot have transition tables.
+DETAIL: ROW triggers with transition tables are not supported on partitioned tables.
drop table parted_trig;
--
-- Verify trigger creation for partitioned tables, and drop behavior
diff --git a/src/test/regress/sql/agg_pushdown.sql b/src/test/regress/sql/agg_pushdown.sql
index 0a4614592b..49ba6dd67c 100644
--- a/src/test/regress/sql/agg_pushdown.sql
+++ b/src/test/regress/sql/agg_pushdown.sql
@@ -113,3 +113,68 @@ EXPLAIN (COSTS off)
SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
+
+-- Most of the tests above with parallel query processing enforced.
+SET min_parallel_index_scan_size = 0;
+SET min_parallel_table_scan_size = 0;
+SET parallel_setup_cost = 0;
+SET parallel_tuple_cost = 0;
+
+-- Partially aggregate a single relation.
+--
+-- Nestloop join.
+SET enable_nestloop TO on;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS off)
+SELECT p.x, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1
+AS c1 ON c1.parent = p.i GROUP BY p.i;
+
+-- Hash join.
+SET enable_nestloop TO off;
+SET enable_hashjoin TO on;
+
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1
+AS c1 ON c1.parent = p.i GROUP BY p.i;
+
+-- Merge join.
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO on;
+
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1
+AS c1 ON c1.parent = p.i GROUP BY p.i;
+
+SET enable_nestloop TO on;
+SET enable_hashjoin TO on;
+
+-- Perform nestloop join between agg_pushdown_child1 and agg_pushdown_child2
+-- and aggregate the result.
+SET enable_nestloop TO on;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
+agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
+c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
+
+-- The same for hash join.
+SET enable_nestloop TO off;
+SET enable_hashjoin TO on;
+
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
+agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
+c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
+
+-- The same for merge join.
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO on;
+SET enable_seqscan TO off;
+
+EXPLAIN (COSTS off)
+SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN
+agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON
+c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i;
--
2.31.1
--=-=-=--
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH 3/3] Use also partial paths as the input for grouped paths.
In-Reply-To: <no-message-id-1859050@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox