This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new d0e9daf227c [AQUMV] Directly compute queries from materialized views
with GROUP BY.
d0e9daf227c is described below
commit d0e9daf227cfeca1266b228be1d15e482162100f
Author: Zhang Mingli <[email protected]>
AuthorDate: Thu Jun 5 14:19:35 2025 +0800
[AQUMV] Directly compute queries from materialized views with GROUP BY.
This commit enhances the AQUMV system by enabling it to compute queries
directly from materialized views that already contain a GROUP BY clause.
This improvement allows us to bypass additional GROUP BY operations
during query execution, resulting in faster and more efficient
performance.
For example, with a materialized view defined as follows:
```sql
CREATE MATERIALIZED VIEW mv_group_1 AS
SELECT c, b, COUNT(b) AS count_b FROM t0 WHERE a > 3 GROUP BY c, b;
```
An original query like:
```sql
SELECT COUNT(b), b, c FROM t0 WHERE a > 3 GROUP BY b, c;
```
is rewritten to:
```sql
SELECT count_b, b, c FROM mv_group_1;
```
The plan looks like:
```sql
explain(costs off, verbose)
select count(b), b, c from t0 where a > 3 group by b, c;
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: count, b, c
-> Seq Scan on aqumv.mv_group_1
Output: count, b, c
Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
Optimizer: Postgres query optimizer
(6 rows)
```
The two SQL queries yield equivalent results, even though the selected
columns are in a different order. Since mv_group_1 already contains the
aggregated results and all rows have a column a value greater than 3,
there is no need for additional filtering or GROUP BY operations.
This enhancement eliminates redundant computations, leading to
significant time savings. Fetching results directly from these views
reduces overall execution time, improving responsiveness for complex
queries. This is particularly beneficial for large datasets, allowing
efficient data analysis without performance degradation.
The feature also applies to Dynamic Tables and Incremental Materialized
Views.
Authored-by: Zhang Mingli [email protected]
---
src/backend/optimizer/plan/aqumv.c | 110 ++++++++-
src/test/regress/expected/aqumv.out | 471 ++++++++++++++++++++++++++++++++++++
src/test/regress/sql/aqumv.sql | 100 ++++++++
3 files changed, 676 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/plan/aqumv.c
b/src/backend/optimizer/plan/aqumv.c
index 63e82d759b0..4a576061780 100644
--- a/src/backend/optimizer/plan/aqumv.c
+++ b/src/backend/optimizer/plan/aqumv.c
@@ -74,6 +74,13 @@ static Node *aqumv_adjust_sub_matched_expr_mutator(Node
*node, aqumv_equivalent_
static bool contain_var_or_aggstar_clause_walker(Node *node, void *context);
static bool check_partition(Query *parse, Oid origin_rel_oid);
+static bool
+groupby_query_rewrite(PlannerInfo *subroot,
+ Query *parse,
+ Query *viewQuery,
+
aqumv_equivalent_transformation_context *context,
+ AqumvContext aqumv_context);
+
typedef struct
{
int complexity;
@@ -358,19 +365,23 @@ answer_query_using_materialized_views(PlannerInfo *root,
AqumvContext aqumv_cont
if (!parse->hasAggs && viewQuery->hasAggs)
continue;
- if (parse->hasAggs && viewQuery->hasAggs)
+ if (parse->groupClause != NIL && viewQuery->groupClause != NIL)
+ {
+ if (!groupby_query_rewrite(subroot, parse, viewQuery,
context, aqumv_context))
+ continue;
+ }
+ else if (parse->hasAggs && viewQuery->hasAggs)
{
+ /* Both don't have group by. */
+ {
if (parse->hasDistinctOn ||
parse->distinctClause != NIL ||
- parse->groupClause != NIL || /* TODO: GROUP BY
*/
parse->groupingSets != NIL ||
parse->groupDistinct)
continue;
- /* No Group by now. */
if (viewQuery->hasDistinctOn ||
viewQuery->distinctClause != NIL ||
- viewQuery->groupClause != NIL ||
viewQuery->groupingSets != NIL ||
viewQuery->groupDistinct ||
viewQuery->havingQual != NULL || /* HAVING
clause is not supported on IMMV yet. */
@@ -388,7 +399,7 @@ answer_query_using_materialized_views(PlannerInfo *root,
AqumvContext aqumv_cont
*/
if (parse->sortClause != NIL || viewQuery->sortClause
!= NIL)
{
- /* Earse view's sort caluse, it's ok to let
alone view's target list. */
+ /* Erase view's sort caluse, it's ok to let
alone view's target list. */
viewQuery->sortClause = NIL;
}
@@ -463,6 +474,7 @@ answer_query_using_materialized_views(PlannerInfo *root,
AqumvContext aqumv_cont
/* Select from a mv never have that.*/
subroot->append_rel_list = NIL;
+ }
}
else
{
@@ -896,3 +908,91 @@ check_partition(Query *parse, Oid origin_rel_oid)
}
return true;
}
+
+static bool
+groupby_query_rewrite(PlannerInfo *subroot,
+ Query *parse,
+ Query *viewQuery,
+
aqumv_equivalent_transformation_context *context,
+ AqumvContext aqumv_context)
+{
+ List *post_quals = NIL;
+ List *mv_final_tlist = NIL;
+
+ if (!parse->hasAggs || !viewQuery->hasAggs)
+ return false;
+
+ /* Both have Group by and aggregation. */
+ if (parse->groupClause == NIL || viewQuery->groupClause == NIL)
+ return false;
+
+ if (parse->hasDistinctOn ||
+ parse->distinctClause != NIL ||
+ parse->groupingSets != NIL ||
+ parse->sortClause != NIL ||
+ limit_needed(parse) ||
+ parse->havingQual != NULL ||
+ parse->groupDistinct)
+ return false;
+
+ if (viewQuery->hasDistinctOn ||
+ viewQuery->distinctClause != NIL ||
+ viewQuery->groupingSets != NIL ||
+ viewQuery->groupDistinct ||
+ viewQuery->havingQual != NULL ||
+ viewQuery->sortClause != NIL ||
+ limit_needed(viewQuery))
+ return false;
+
+ if (tlist_has_srf(parse))
+ return false;
+
+ preprocess_qual_conditions(subroot, (Node *) viewQuery->jointree);
+
+ if(!aqumv_process_from_quals(parse->jointree->quals,
viewQuery->jointree->quals, &post_quals))
+ return false;
+
+ if (post_quals != NIL)
+ return false;
+
+ /*
+ * There should be no post_quals for now, erase those from view.
+ */
+ viewQuery->jointree->quals = NULL;
+
+ if (list_difference(parse->groupClause, viewQuery->groupClause))
+ return false;
+
+ if (list_difference(viewQuery->groupClause, parse->groupClause))
+ return false;
+
+ /*
+ * Group By clauses are equal, erase those from view.
+ */
+ viewQuery->groupClause = NIL;
+
+ if(!aqumv_process_targetlist(context,
aqumv_context->raw_processed_tlist, &mv_final_tlist))
+ return false;
+
+ viewQuery->targetList = mv_final_tlist;
+ /* SRF is not supported now, but correct the field. */
+ viewQuery->hasTargetSRFs = parse->hasTargetSRFs;
+ viewQuery->hasAggs = false;
+ subroot->agginfos = NIL;
+ subroot->aggtransinfos = NIL;
+ subroot->hasNonPartialAggs = false;
+ subroot->hasNonSerialAggs = false;
+ subroot->numOrderedAggs = false;
+ /* CBDB specifical */
+ subroot->hasNonCombine = false;
+ subroot->numPureOrderedAggs = false;
+ /*
+ * NB: Update processed_tlist again in case that tlist has been changed.
+ */
+ subroot->processed_tlist = NIL;
+ preprocess_targetlist(subroot);
+
+ /* Select from a mv never have that.*/
+ subroot->append_rel_list = NIL;
+ return true;
+}
diff --git a/src/test/regress/expected/aqumv.out
b/src/test/regress/expected/aqumv.out
index 463b0b37032..926ef05cd32 100644
--- a/src/test/regress/expected/aqumv.out
+++ b/src/test/regress/expected/aqumv.out
@@ -3374,6 +3374,477 @@ select * from t_insert;
1000
(1 row)
+abort;
+-- Test view has Group By
+begin;
+create table t0 as select i as a, i+1 as b , i+2 as c, i+3 as d from
generate_series(1, 5) i;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'a' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+analyze t0;
+create materialized view mv_group_0 as select c, b, sum(a), count(b) from t0
group by b, c;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'b, c' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create materialized view mv_group_1 as select c, b, count(b) from t0 where a >
3 group by c, b;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'c, b' as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+analyze mv_group_0;
+analyze mv_group_1;
+-- no qual, exactly match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c, b, sum(a), count(b) from t0 group by b, c;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, b, (sum(a)), (count(b))
+ -> Finalize HashAggregate
+ Output: c, b, sum(a), count(b)
+ Group Key: t0.b, t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, b, (PARTIAL sum(a)), (PARTIAL count(b))
+ Hash Key: b, c
+ -> Partial HashAggregate
+ Output: c, b, PARTIAL sum(a), PARTIAL count(b)
+ Group Key: t0.b, t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select c, b, sum(a), count(b) from t0 group by b, c;
+ c | b | sum | count
+---+---+------+-------
+ 6 | 5 | 2048 | 512
+ 3 | 2 | 512 | 512
+ 5 | 4 | 1536 | 512
+ 4 | 3 | 1024 | 512
+ 7 | 6 | 2560 | 512
+(5 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c, b, sum(a), count(b) from t0 group by b, c;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, b, sum, count
+ -> Seq Scan on aqumv.mv_group_0
+ Output: c, b, sum, count
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select c, b, sum(a), count(b) from t0 group by b, c;
+ c | b | sum | count
+---+---+------+-------
+ 6 | 5 | 2048 | 512
+ 3 | 2 | 512 | 512
+ 5 | 4 | 1536 | 512
+ 4 | 3 | 1024 | 512
+ 7 | 6 | 2560 | 512
+(5 rows)
+
+-- no qual, different order
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select b, sum(a), c, count(b) from t0 group by c, b;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, (sum(a)), c, (count(b))
+ -> Finalize HashAggregate
+ Output: b, sum(a), c, count(b)
+ Group Key: t0.c, t0.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL sum(a)), (PARTIAL count(b))
+ Hash Key: c, b
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL sum(a), PARTIAL count(b)
+ Group Key: t0.c, t0.b
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select b, sum(a), c, count(b) from t0 group by c, b;
+ b | sum | c | count
+---+------+---+-------
+ 6 | 2560 | 7 | 512
+ 4 | 1536 | 5 | 512
+ 5 | 2048 | 6 | 512
+ 3 | 1024 | 4 | 512
+ 2 | 512 | 3 | 512
+(5 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select b, sum(a), c, count(b) from t0 group by c, b;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, sum, c, count
+ -> Seq Scan on aqumv.mv_group_0
+ Output: b, sum, c, count
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select b, sum(a), c, count(b) from t0 group by c, b;
+ b | sum | c | count
+---+------+---+-------
+ 5 | 2048 | 6 | 512
+ 2 | 512 | 3 | 512
+ 4 | 1536 | 5 | 512
+ 3 | 1024 | 4 | 512
+ 6 | 2560 | 7 | 512
+(5 rows)
+
+-- no qual, different expr
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (((b + c) + 1)), ((sum(a) + count(b))), c, b
+ -> Finalize HashAggregate
+ Output: ((b + c) + 1), (sum(a) + count(b)), c, b
+ Group Key: t0.c, t0.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, b, (PARTIAL sum(a)), (PARTIAL count(b))
+ Hash Key: c, b
+ -> Partial HashAggregate
+ Output: c, b, PARTIAL sum(a), PARTIAL count(b)
+ Group Key: t0.c, t0.b
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+ ?column? | ?column?
+----------+----------
+ 8 | 1536
+ 14 | 3072
+ 10 | 2048
+ 12 | 2560
+ 6 | 1024
+(5 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (((b + c) + 1)), ((sum + count)), c, b
+ -> Seq Scan on aqumv.mv_group_0
+ Output: ((b + c) + 1), (sum + count), c, b
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+ ?column? | ?column?
+----------+----------
+ 8 | 1536
+ 14 | 3072
+ 12 | 2560
+ 6 | 1024
+ 10 | 2048
+(5 rows)
+
+-- no qual, should not match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c, count(b) from t0 group by c ;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, (count(b))
+ -> Finalize HashAggregate
+ Output: c, count(b)
+ Group Key: t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, (PARTIAL count(b))
+ Hash Key: c
+ -> Partial HashAggregate
+ Output: c, PARTIAL count(b)
+ Group Key: t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select c, count(b) from t0 group by c ;
+ c | count
+---+-------
+ 4 | 512
+ 3 | 512
+ 7 | 512
+ 5 | 512
+ 6 | 512
+(5 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c, count(b) from t0 group by c ;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, (count(b))
+ -> Finalize HashAggregate
+ Output: c, count(b)
+ Group Key: t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, (PARTIAL count(b))
+ Hash Key: c
+ -> Partial HashAggregate
+ Output: c, PARTIAL count(b)
+ Group Key: t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(15 rows)
+
+select c, count(b) from t0 group by c ;
+ c | count
+---+-------
+ 4 | 512
+ 3 | 512
+ 7 | 512
+ 5 | 512
+ 6 | 512
+(5 rows)
+
+-- with qual, exactly match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c, b, count(b) from t0 where a > 3 group by c, b;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, b, (count(b))
+ -> Finalize HashAggregate
+ Output: c, b, count(b)
+ Group Key: t0.c, t0.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, b, (PARTIAL count(b))
+ Hash Key: c, b
+ -> Partial HashAggregate
+ Output: c, b, PARTIAL count(b)
+ Group Key: t0.c, t0.b
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Filter: (t0.a > 3)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select c, b, count(b) from t0 where a > 3 group by c, b;
+ c | b | count
+---+---+-------
+ 7 | 6 | 512
+ 6 | 5 | 512
+(2 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c, b, count(b) from t0 where a > 3 group by c, b;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c, b, count
+ -> Seq Scan on aqumv.mv_group_1
+ Output: c, b, count
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select c, b, count(b) from t0 where a > 3 group by c, b;
+ c | b | count
+---+---+-------
+ 7 | 6 | 512
+ 6 | 5 | 512
+(2 rows)
+
+-- with qual, different order
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select count(b), b, c from t0 where a > 3 group by b, c;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: (count(b)), b, c
+ -> Finalize HashAggregate
+ Output: count(b), b, c
+ Group Key: t0.b, t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(b))
+ Hash Key: b, c
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(b)
+ Group Key: t0.b, t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Filter: (t0.a > 3)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select count(b), b, c from t0 where a > 3 group by b, c;
+ count | b | c
+-------+---+---
+ 512 | 5 | 6
+ 512 | 6 | 7
+(2 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select count(b), b, c from t0 where a > 3 group by b, c;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: count, b, c
+ -> Seq Scan on aqumv.mv_group_1
+ Output: count, b, c
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(b), b, c from t0 where a > 3 group by b, c;
+ count | b | c
+-------+---+---
+ 512 | 6 | 7
+ 512 | 5 | 6
+(2 rows)
+
+-- with qual, different expr
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: ((count(b) + 1)), ((b + 1)), c, b
+ -> Finalize HashAggregate
+ Output: (count(b) + 1), (b + 1), c, b
+ Group Key: t0.b, t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: c, b, (PARTIAL count(b))
+ Hash Key: b, c
+ -> Partial HashAggregate
+ Output: c, b, PARTIAL count(b)
+ Group Key: t0.b, t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Filter: (t0.a > 3)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+ ?column? | ?column? | c
+----------+----------+---
+ 513 | 6 | 6
+ 513 | 7 | 7
+(2 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: ((count + 1)), ((b + 1)), c, b
+ -> Seq Scan on aqumv.mv_group_1
+ Output: (count + 1), (b + 1), c, b
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+ ?column? | ?column? | c
+----------+----------+---
+ 513 | 7 | 7
+ 513 | 6 | 6
+(2 rows)
+
+-- with qual, should not match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, c, (count(b))
+ -> Finalize HashAggregate
+ Output: b, c, count(b)
+ Group Key: t0.b, t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(b))
+ Hash Key: b, c
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(b)
+ Group Key: t0.b, t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Filter: ((t0.a > 3) AND (t0.b > 1))
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+ b | c | count
+---+---+-------
+ 5 | 6 | 512
+ 6 | 7 | 512
+(2 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: b, c, (count(b))
+ -> Finalize HashAggregate
+ Output: b, c, count(b)
+ Group Key: t0.b, t0.c
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Output: b, c, (PARTIAL count(b))
+ Hash Key: b, c
+ -> Partial HashAggregate
+ Output: b, c, PARTIAL count(b)
+ Group Key: t0.b, t0.c
+ -> Seq Scan on aqumv.t0
+ Output: a, b, c, d
+ Filter: ((t0.a > 3) AND (t0.b > 1))
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+ b | c | count
+---+---+-------
+ 5 | 6 | 512
+ 6 | 7 | 512
+(2 rows)
+
abort;
reset optimizer;
reset enable_answer_query_using_materialized_views;
diff --git a/src/test/regress/sql/aqumv.sql b/src/test/regress/sql/aqumv.sql
index 87fe401c2ab..35690d220e8 100644
--- a/src/test/regress/sql/aqumv.sql
+++ b/src/test/regress/sql/aqumv.sql
@@ -874,6 +874,106 @@ insert into t_insert select count(a) from t_select;
select * from t_insert;
abort;
+-- Test view has Group By
+begin;
+create table t0 as select i as a, i+1 as b , i+2 as c, i+3 as d from
generate_series(1, 5) i;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+insert into t0 select * from t0;
+analyze t0;
+create materialized view mv_group_0 as select c, b, sum(a), count(b) from t0
group by b, c;
+create materialized view mv_group_1 as select c, b, count(b) from t0 where a >
3 group by c, b;
+analyze mv_group_0;
+analyze mv_group_1;
+
+-- no qual, exactly match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c, b, sum(a), count(b) from t0 group by b, c;
+select c, b, sum(a), count(b) from t0 group by b, c;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c, b, sum(a), count(b) from t0 group by b, c;
+select c, b, sum(a), count(b) from t0 group by b, c;
+
+-- no qual, different order
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select b, sum(a), c, count(b) from t0 group by c, b;
+select b, sum(a), c, count(b) from t0 group by c, b;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select b, sum(a), c, count(b) from t0 group by c, b;
+select b, sum(a), c, count(b) from t0 group by c, b;
+
+-- no qual, different expr
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+select b + c + 1, sum(a) + count(b) from t0 group by c, b;
+
+-- no qual, should not match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c, count(b) from t0 group by c ;
+select c, count(b) from t0 group by c ;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c, count(b) from t0 group by c ;
+select c, count(b) from t0 group by c ;
+
+-- with qual, exactly match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c, b, count(b) from t0 where a > 3 group by c, b;
+select c, b, count(b) from t0 where a > 3 group by c, b;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c, b, count(b) from t0 where a > 3 group by c, b;
+select c, b, count(b) from t0 where a > 3 group by c, b;
+
+-- with qual, different order
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select count(b), b, c from t0 where a > 3 group by b, c;
+select count(b), b, c from t0 where a > 3 group by b, c;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select count(b), b, c from t0 where a > 3 group by b, c;
+select count(b), b, c from t0 where a > 3 group by b, c;
+
+-- with qual, different expr
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+select count(b) + 1, b + 1, c from t0 where a > 3 group by b, c;
+
+-- with qual, should not match
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+select b, c, count(b) from t0 where a > 3 and b > 1 group by b, c;
+
+abort;
+
reset optimizer;
reset enable_answer_query_using_materialized_views;
-- start_ignore
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
