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 20cbc1d128 [AQUMV] Answer Aggregation Query Directly. (#705)
20cbc1d128 is described below
commit 20cbc1d1289e0396903859f3676fc1959d34bf05
Author: Zhang Mingli <[email protected]>
AuthorDate: Wed Dec 11 15:33:13 2024 +0800
[AQUMV] Answer Aggregation Query Directly. (#705)
This commits enable answer query which has aggregation directly. Use the
results of view has aggregations to avoid compute those from origin
table.
This may lead to significant efficiency gains if the SQL has a large
amount of data.
AQUMV will always return results immediately.
If we have a valid view like:
create materialized view mv as
select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as
mc4
from t where c1 > 90;
SQL:
select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t
where c1 > 90;
Could be rewritten to:
select mc4, mc1, mc2, mc3, abs((mc4 - 21)) from mv;
Plan:
explain(verbose, costs off)
select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t
where c1 > 90;
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: mc4, mc1, mc2, mc3, (abs((mc4 - 21)))
-> Seq Scan on mv
Output: mc4, mc1, mc2, mc3, abs((mc4 - 21))
Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
Optimizer: Postgres query optimizer
(6 rows)
View query with Group By is not supported yet.
If some HAVING quals only exist in origin query and they could be
computed from view query's target list, then we could keep them like
post_quals.But as the view has aggregations, the additional quals should
be moved to WHERE instead of HAVING.
create table t(c1 int, c2 int, c3 int, c4 int);
create materialized view mv as
select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as
mc4
from t where c1 > 90;
SQL:
select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21)
> 0 and 2 > 1 and avg(c3) > 97;
Could be rewritten to (The HAVING clause has been rewritten to WHERE
clause):
select mc4, mc1 from mv where mc3 > 97 and abs(mc4 - 21) > 0;
Plan:
explain(verbose, costs off)
select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21)
> 0 and 2 > 1 and avg(c3) > 97;
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: mc4, mc1
-> Seq Scan on aqumv.mv
Output: mc4, mc1
Filter: ((mv.mc3 > '97'::numeric) AND (abs((mv.mc4 - 21)) > 0))
Optimizer: Postgres query optimizer
(7 rows)
There are two additional HAVING quals:
Expression: 2 > 1 (would be eliminated during planner). Expression:
abs(count(*) - 21) > 0, it could be computed from view as:
abs(mc4 - 21) > 0
And the new one is put to WHERE clause and acts as a Filter finally.
There is a trick for ORDER BY for both origin query and view query. As
we has no Groupy By curretly, the aggregation results would be either
one or zero rows that make the Order By clause pointless.
We could avoid considering the sort columns if it's a junk for view
matching.
As we have no group by for view with aggs now, the final result would be
either one or zero row.
LIMIT, OFFSET clause of origin query could be applied to view if there
are consts.
create incremental materialized view mv as
select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as
mc4
from t where c1 > 90;
Query:
select count(*), sum(c1) from t where c1 > 90 limit 2;
Could be rewritten to:
select mc4, mc1 from mv limit 2;
Authored-by: Zhang Mingli [email protected]
---
src/backend/catalog/gp_matview_aux.c | 6 +
src/backend/optimizer/README.cbdb.aqumv | 1 -
src/backend/optimizer/plan/aqumv.c | 290 +++++++++++++++------
src/backend/optimizer/plan/planner.c | 24 +-
src/backend/optimizer/plan/transform.c | 7 +-
src/include/optimizer/aqumv.h | 16 ++
src/include/optimizer/planmain.h | 5 -
src/include/optimizer/transform.h | 2 +
src/test/regress/expected/aqumv.out | 435 ++++++++++++++++++++++++++++++++
src/test/regress/sql/aqumv.sql | 114 +++++++++
10 files changed, 818 insertions(+), 82 deletions(-)
diff --git a/src/backend/catalog/gp_matview_aux.c
b/src/backend/catalog/gp_matview_aux.c
index a43bd27cd4..7fd036f9af 100644
--- a/src/backend/catalog/gp_matview_aux.c
+++ b/src/backend/catalog/gp_matview_aux.c
@@ -32,6 +32,7 @@
#include "utils/lsyscache.h"
#include "storage/lockdefs.h"
#include "optimizer/optimizer.h"
+#include "optimizer/transform.h"
#include "parser/parsetree.h"
static void InsertMatviewTablesEntries(Oid mvoid, List *relids);
@@ -73,6 +74,11 @@ GetViewBaseRelids(const Query *viewQuery, bool *has_foreign)
return NIL;
}
+ if (tlist_has_srf(viewQuery))
+ {
+ return NIL;
+ }
+
/* As we will use views, make it strict to unmutable. */
if (contain_mutable_functions((Node*)viewQuery))
return NIL;
diff --git a/src/backend/optimizer/README.cbdb.aqumv
b/src/backend/optimizer/README.cbdb.aqumv
index 99dc217d6e..7933b9322b 100644
--- a/src/backend/optimizer/README.cbdb.aqumv
+++ b/src/backend/optimizer/README.cbdb.aqumv
@@ -220,7 +220,6 @@ AQUMV_MVP
---------
Support SELECT FROM a single relation both for view_query and the origin_query.
Below are not supported now:
- Aggregation (on view_query)
Subquery
Join
Sublink
diff --git a/src/backend/optimizer/plan/aqumv.c
b/src/backend/optimizer/plan/aqumv.c
index ee84395623..3513792ee5 100644
--- a/src/backend/optimizer/plan/aqumv.c
+++ b/src/backend/optimizer/plan/aqumv.c
@@ -30,6 +30,7 @@
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "optimizer/tlist.h"
+#include "optimizer/transform.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
#include "parser/parse_node.h"
@@ -43,11 +44,6 @@
#include "nodes/pathnodes.h"
#include "nodes/pg_list.h"
-RelOptInfo *answer_query_using_materialized_views(PlannerInfo *root,
-
RelOptInfo *current_rel,
-
query_pathkeys_callback qp_callback,
-
void *qp_extra);
-
typedef struct
{
int varno;
@@ -70,6 +66,7 @@ static aqumv_equivalent_transformation_context*
aqumv_init_context(List *view_tl
static bool aqumv_process_targetlist(aqumv_equivalent_transformation_context
*context, List *query_tlist, List **mv_final_tlist);
static void aqumv_sort_targetlist(aqumv_equivalent_transformation_context*
context);
static Node *aqumv_adjust_sub_matched_expr_mutator(Node *node,
aqumv_equivalent_transformation_context *context);
+static bool contain_var_or_aggstar_clause_walker(Node *node, void *context);
typedef struct
{
@@ -82,16 +79,51 @@ typedef struct
int count; /* Count of subnodes in this expression
*/
} expr_to_sort;
+static bool
+contain_var_or_aggstar_clause(Node *node)
+{
+ return contain_var_or_aggstar_clause_walker(node, NULL);
+}
+
+/* Copy from contain_var_clause_walker, but return true with aggstar. */
+static bool
+contain_var_or_aggstar_clause_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Aggref) && ((Aggref *) node)->aggstar)
+ return true;
+
+ if (IsA(node, Var))
+ {
+ if (((Var *) node)->varlevelsup == 0)
+ return true; /* abort the tree traversal and
return true */
+ return false;
+ }
+ if (IsA(node, CurrentOfExpr))
+ return true;
+ if (IsA(node, PlaceHolderVar))
+ {
+ if (((PlaceHolderVar *) node)->phlevelsup == 0)
+ return true; /* abort the tree traversal and
return true */
+ /* else fall through to check the contained expr */
+ }
+ return expression_tree_walker(node,
contain_var_or_aggstar_clause_walker, context);
+}
+
/*
* Answer Query Using Materialized Views(AQUMV).
* This function modifies root(parse and etc.), current_rel in-place.
*/
RelOptInfo*
-answer_query_using_materialized_views(PlannerInfo *root,
-
RelOptInfo *current_rel,
-
query_pathkeys_callback qp_callback,
- void
*qp_extra)
+answer_query_using_materialized_views(PlannerInfo *root, AqumvContext
aqumv_context)
{
+ RelOptInfo *current_rel = aqumv_context->current_rel;
+ query_pathkeys_callback qp_callback = aqumv_context->qp_callback;
+ void *qp_extra = aqumv_context->qp_extra;
+ Node *raw_havingQual = aqumv_context->raw_havingQual;
+
Query *parse = root->parse; /* Query of origin SQL. */
Query *viewQuery; /* Query of view. */
RelOptInfo *mv_final_rel = current_rel; /* Final rel after
rewritten. */
@@ -205,18 +237,18 @@ answer_query_using_materialized_views(PlannerInfo *root,
* The Seqscan on a heap-storaged mv seems ordered, but it's a
free lunch.
* A Parallel Seqscan breaks that hypothesis.
*/
- if(viewQuery->hasAggs ||
- viewQuery->hasWindowFuncs ||
+ if(viewQuery->hasWindowFuncs ||
viewQuery->hasDistinctOn ||
viewQuery->hasModifyingCTE ||
viewQuery->hasSubLinks ||
(limit_needed(viewQuery)) ||
- (viewQuery->groupClause != NIL) ||
/* IVM doesn't support belows now, just in case. */
(viewQuery->rowMarks != NIL) ||
(viewQuery->distinctClause != NIL) ||
(viewQuery->cteList != NIL) ||
(viewQuery->setOperations != NULL) ||
+ (!viewQuery->hasAggs && (viewQuery->groupClause !=
NIL)) ||
+ ((viewQuery->havingQual != NULL) &&
(viewQuery->groupClause == NIL)) ||
(viewQuery->scatterClause != NIL))
continue;
@@ -296,69 +328,185 @@ answer_query_using_materialized_views(PlannerInfo *root,
context = aqumv_init_context(viewQuery->targetList,
matviewRel->rd_att);
- /*
- * Process and rewrite target list, return false if failed.
- */
- if(!aqumv_process_targetlist(context, parse->targetList,
&mv_final_tlist))
+ if (!parse->hasAggs && viewQuery->hasAggs)
continue;
- viewQuery->targetList = mv_final_tlist;
+ if (parse->hasAggs && viewQuery->hasAggs)
+ {
+ if (parse->hasDistinctOn ||
+ parse->distinctClause != NIL ||
+ parse->groupClause != NIL ||
+ 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. */
+ limit_needed(viewQuery)) /* LIMIT, OFFSET is
not supported on IMMV yet. */
+ continue;
+
+ if (tlist_has_srf(parse))
+ continue;
- /*
- * NB: Update processed_tlist again in case that tlist has been
changed.
- */
- preprocess_targetlist(subroot);
+ /*
+ * There is a trick for ORDER BY for both origin query
and view query.
+ * As we has no Groupy By here, the aggregation results
would be either one or
+ * zero rows that make the Order By clause pointless,
except that there were
+ * SRF.
+ */
+ if (parse->sortClause != NIL || viewQuery->sortClause
!= NIL)
+ {
+ /* Earse view's sort caluse, it's ok to let
alone view's target list. */
+ viewQuery->sortClause = NIL;
+ }
- /*
- * We have successfully processed target list, and all columns
in Aggrefs
- * could be computed from viewQuery.
- */
- viewQuery->hasAggs = parse->hasAggs;
- viewQuery->hasDistinctOn = parse->hasDistinctOn;
- /*
- * For HAVING quals have aggregations, we have already
processed them in
- * Aggrefs during aqumv_process_targetlist().
- * For HAVING quals don't have aggregations, they may be pushed
down to
- * jointree's quals and would be processed in post_quals later.
- * Set havingQual before we preprocess_aggrefs for that.
- */
- viewQuery->havingQual = parse->havingQual;
- if (viewQuery->hasAggs)
- {
- preprocess_aggrefs(subroot, (Node *)
subroot->processed_tlist);
- preprocess_aggrefs(subroot, viewQuery->havingQual);
+ /*
+ * Process Limit:
+ * The result would be one row at most.
+ * View may be useful even Limit clause is different,
ex:
+ * View:
+ * create incremental materialized view mv as
+ * select count(*) as mc1 from t;
+ * Query:
+ * select count(*) from t limit 1;
+ * Rewrite to:
+ * select mc1 from mv limit 1;
+ */
+ /* Below logic is based on view has no LIMIT/OFFSET. */
+ Assert(!limit_needed(viewQuery));
+ if (limit_needed(parse))
+ {
+ Node *node;
+ /*
+ * AQUMV don't support sublinks now.
+ * Use query's LIMIT/OFFSET if they are const
in case.
+ */
+ node = parse->limitCount;
+ if (node && !IsA(node, Const))
+ continue;
+
+ node = parse->limitOffset;
+ if (node && !IsA(node, Const))
+ continue;
+
+ viewQuery->limitCount =
copyObject(parse->limitCount);
+ viewQuery->limitOffset =
copyObject(parse->limitOffset);
+ viewQuery->limitOption = parse->limitOption;
+ }
+
+ preprocess_qual_conditions(subroot, (Node *)
viewQuery->jointree);
+
+ if(!aqumv_process_from_quals(parse->jointree->quals,
viewQuery->jointree->quals, &post_quals))
+ continue;
+
+ if (post_quals != NIL)
+ continue;
+
+ /* Move HAVING quals to WHERE quals. */
+ viewQuery->jointree->quals =
aqumv_adjust_sub_matched_expr_mutator(copyObject(raw_havingQual), context);
+ if (context->has_unmatched)
+ continue;
+ subroot->hasHavingQual = false;
+
+ if(!aqumv_process_targetlist(context,
aqumv_context->raw_processed_tlist, &mv_final_tlist))
+ continue;
+
+ 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);
}
- viewQuery->groupClause = parse->groupClause;
- viewQuery->groupingSets = parse->groupingSets;
- viewQuery->sortClause = parse->sortClause;
- viewQuery->distinctClause = parse->distinctClause;
- viewQuery->limitOption = parse->limitOption;
- viewQuery->limitCount = parse->limitCount;
- viewQuery->limitOffset = parse->limitOffset;
+ else
+ {
+ /*
+ * Process and rewrite target list, return false if
failed.
+ */
+ if(!aqumv_process_targetlist(context,
parse->targetList, &mv_final_tlist))
+ continue;
- /*
- * AQUMV
- * Process all quals to conjunctive normal form.
- *
- * We assume that the selection predicates of view and query
expressions
- * have been converted into conjunctive normal form(CNF) before
we process
- * them.
- */
- preprocess_qual_conditions(subroot, (Node *)
viewQuery->jointree);
+ viewQuery->targetList = mv_final_tlist;
- /*
- * Process quals, return false if failed.
- * Else, post_quals are filled if there were.
- * Like process target list, post_quals is used later to see if
we could
- * rewrite and apply it to mv relation.
- */
- if(!aqumv_process_from_quals(parse->jointree->quals,
viewQuery->jointree->quals, &post_quals))
- continue;
+ /*
+ * NB: Update processed_tlist again in case that tlist
has been changed.
+ */
+ preprocess_targetlist(subroot);
- /* Rewrite post_quals, return false if failed. */
- post_quals = (List
*)aqumv_adjust_sub_matched_expr_mutator((Node *)post_quals, context);
- if (context->has_unmatched)
- continue;
+ /*
+ * We have successfully processed target list, and all
columns in Aggrefs
+ * could be computed from viewQuery.
+ */
+ viewQuery->hasAggs = parse->hasAggs;
+ viewQuery->hasDistinctOn = parse->hasDistinctOn;
+ /*
+ * For HAVING quals don't have aggregations, they may
be pushed down to
+ * jointree's quals and would be processed in
post_quals later.
+ * Set havingQual before we preprocess_aggrefs for that.
+ */
+ viewQuery->havingQual = parse->havingQual;
+ if (viewQuery->hasAggs)
+ {
+ preprocess_aggrefs(subroot, (Node *)
subroot->processed_tlist);
+ preprocess_aggrefs(subroot,
viewQuery->havingQual);
+ }
+
+ viewQuery->havingQual =
aqumv_adjust_sub_matched_expr_mutator(viewQuery->havingQual, context);
+ if (context->has_unmatched)
+ continue;
+
+ /* SRF is not supported now, but correct the field. */
+ viewQuery->hasTargetSRFs = parse->hasTargetSRFs;
+ viewQuery->groupClause = parse->groupClause;
+ viewQuery->groupingSets = parse->groupingSets;
+ viewQuery->sortClause = parse->sortClause;
+ viewQuery->distinctClause = parse->distinctClause;
+ viewQuery->limitOption = parse->limitOption;
+ viewQuery->limitCount = parse->limitCount;
+ viewQuery->limitOffset = parse->limitOffset;
+
+ /*
+ * AQUMV
+ * Process all quals to conjunctive normal form.
+ *
+ * We assume that the selection predicates of view and
query expressions
+ * have been converted into conjunctive normal
form(CNF) before we process
+ * them.
+ */
+ preprocess_qual_conditions(subroot, (Node *)
viewQuery->jointree);
+
+ /*
+ * Process quals, return false if failed.
+ * Else, post_quals are filled if there were.
+ * Like process target list, post_quals is used later
to see if we could
+ * rewrite and apply it to mv relation.
+ */
+ if(!aqumv_process_from_quals(parse->jointree->quals,
viewQuery->jointree->quals, &post_quals))
+ continue;
+
+ /* Rewrite post_quals, return false if failed. */
+ post_quals = (List
*)aqumv_adjust_sub_matched_expr_mutator((Node *)post_quals, context);
+ if (context->has_unmatched)
+ continue;
+
+ viewQuery->jointree->quals = (Node *)post_quals;
+ }
/*
* AQUMV
@@ -371,7 +519,6 @@ answer_query_using_materialized_views(PlannerInfo *root,
mvrte->relkind = RELKIND_MATVIEW;
mvrte->relid = matviewRel->rd_rel->oid;
viewQuery->rtable = list_make1(mvrte); /* rewrite to SELECT
FROM mv itself. */
- viewQuery->jointree->quals = (Node *)post_quals; /* Could be
NULL, but doesn'y matter for now. */
/*
* Build a plan of new SQL.
@@ -399,6 +546,7 @@ answer_query_using_materialized_views(PlannerInfo *root,
/* CBDB specifical */
root->hasNonCombine = subroot->hasNonCombine;
root->numPureOrderedAggs = subroot->numPureOrderedAggs;
+ root->hasHavingQual = subroot->hasHavingQual;
/*
* Update pathkeys which may be changed by qp_callback.
@@ -457,8 +605,8 @@ aqumv_init_context(List *view_tlist, TupleDesc mv_tupledesc)
if (tle->resjunk)
continue;
- /* Avoid expression has no Vars. */
- if(!contain_var_clause((Node*)tle))
+ /* Avoid expression has no Vars, excpet for count(*). */
+ if(!contain_var_or_aggstar_clause((Node*)tle))
continue;
/* To be sorted later */
@@ -631,7 +779,7 @@ static Node *aqumv_adjust_sub_matched_expr_mutator(Node
*node, aqumv_equivalent_
* And if expr doesn't have Vars, return it to upper.
* Keep TargetEntry expr no changed in case for count(*).
*/
- if (!contain_var_clause((Node *)node_expr))
+ if (!contain_var_or_aggstar_clause((Node *)node_expr))
return is_targetEntry ? node : (Node *)node_expr;
/*
diff --git a/src/backend/optimizer/plan/planner.c
b/src/backend/optimizer/plan/planner.c
index 39252c4bff..e8ced6faa3 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -83,6 +83,7 @@
#include "cdb/cdbtargeteddispatch.h"
#include "cdb/cdbutil.h"
#include "cdb/cdbvars.h"
+#include "optimizer/aqumv.h" /* answer_query_using_materialized_views */
#include "optimizer/orca.h"
#include "storage/lmgr.h"
#include "utils/guc.h"
@@ -1740,6 +1741,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
List *activeWindows = NIL;
grouping_sets_data *gset_data = NULL;
standard_qp_extra qp_extra;
+ AqumvContext aqumv_context = (AqumvContext)
&(AqumvContextData){0};
/* A recursive query should always have setOperations */
Assert(!root->hasRecursion);
@@ -1765,6 +1767,19 @@ grouping_planner(PlannerInfo *root, double
tuple_fraction)
*/
preprocess_targetlist(root);
+ /*
+ * Used for AQUMV.
+ * tlist and having quals before process_aggrefs().
+ * Copy them for agg comparison between view and origin query
+ * in case different agg order.
+ */
+ if (Gp_role == GP_ROLE_DISPATCH &&
+ enable_answer_query_using_materialized_views)
+ {
+ aqumv_context->raw_havingQual =
copyObject(parse->havingQual);
+ aqumv_context->raw_processed_tlist =
copyObject(root->processed_tlist);
+ }
+
/*
* Collect statistics about aggregates for estimating costs,
and mark
* all the aggregates with resolved aggtranstypes. We must do
this
@@ -1890,7 +1905,14 @@ grouping_planner(PlannerInfo *root, double
tuple_fraction)
if (Gp_role == GP_ROLE_DISPATCH &&
enable_answer_query_using_materialized_views)
{
- current_rel =
answer_query_using_materialized_views(root, current_rel, standard_qp_callback,
&qp_extra);
+ /* Now it's ok to set other fields. */
+ aqumv_context->current_rel = current_rel;
+ aqumv_context->qp_callback = standard_qp_callback;
+ aqumv_context->qp_extra = &qp_extra;
+
+ /* Do the real work. */
+ current_rel =
answer_query_using_materialized_views(root, aqumv_context);
+ /* parse tree may be rewriten. */
parse = root->parse;
}
diff --git a/src/backend/optimizer/plan/transform.c
b/src/backend/optimizer/plan/transform.c
index 1a8776c6ab..d77e3855a6 100644
--- a/src/backend/optimizer/plan/transform.c
+++ b/src/backend/optimizer/plan/transform.c
@@ -40,7 +40,6 @@ static SubLink *make_sirvf_subselect(FuncExpr *fe);
static Query *make_sirvf_subquery(FuncExpr *fe);
static bool safe_to_replace_sirvf_tle(Query *query);
static bool safe_to_replace_sirvf_rte(Query *query);
-static bool tlist_has_srf(Query *query);
/**
* Normalize query before planning.
@@ -526,15 +525,15 @@ replace_sirvf_rte(Query *query, RangeTblEntry *rte)
/*
* Does target list have SRFs?
*/
-static
-bool tlist_has_srf(Query *query)
+bool
+tlist_has_srf(const Query *query)
{
if (query->hasTargetSRFs)
{
return true;
}
- if (expression_returns_set( (Node *) query->targetList))
+ if (expression_returns_set((Node *) query->targetList))
{
return true;
}
diff --git a/src/include/optimizer/aqumv.h b/src/include/optimizer/aqumv.h
index 3aff8d6865..a67c59d067 100644
--- a/src/include/optimizer/aqumv.h
+++ b/src/include/optimizer/aqumv.h
@@ -14,7 +14,23 @@
#ifndef AQUMV_H
#define AQUMV_H
+#include "nodes/nodes.h"
#include "nodes/parsenodes.h"
+#include "nodes/pathnodes.h"
+#include "nodes/pg_list.h"
+#include "optimizer/planmain.h"
+
+typedef struct AqumvContextData {
+ RelOptInfo *current_rel;
+ List *raw_processed_tlist;
+ Node *raw_havingQual;
+ void *qp_extra;
+ query_pathkeys_callback qp_callback;
+} AqumvContextData;
+
+typedef AqumvContextData *AqumvContext;
+
+extern RelOptInfo* answer_query_using_materialized_views(PlannerInfo *root,
AqumvContextData *aqumv_context);
/*
* Adjust parse tree storaged in view's actions.
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 9fcc979b34..cc01f0f4e6 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -166,9 +166,4 @@ extern void cdb_extract_plan_dependencies(PlannerInfo
*root, Plan *plan);
extern void add_proc_oids_for_dump(Oid funcid);
-extern RelOptInfo* answer_query_using_materialized_views(PlannerInfo *root,
-
RelOptInfo *current_rel,
-
query_pathkeys_callback qp_callback,
-
void *qp_extra);
-
#endif /* PLANMAIN_H */
diff --git a/src/include/optimizer/transform.h
b/src/include/optimizer/transform.h
index d53c393769..a059594852 100644
--- a/src/include/optimizer/transform.h
+++ b/src/include/optimizer/transform.h
@@ -25,4 +25,6 @@ extern Query *remove_distinct_sort_clause(Query *query);
extern bool query_has_srf(Query *query);
+extern bool tlist_has_srf(const Query *query);
+
#endif /* TRANSFORM_H */
diff --git a/src/test/regress/expected/aqumv.out
b/src/test/regress/expected/aqumv.out
index ba4772e826..a978409f8e 100644
--- a/src/test/regress/expected/aqumv.out
+++ b/src/test/regress/expected/aqumv.out
@@ -1560,6 +1560,10 @@ create incremental materialized view aqumv_mvt4_0 as
select c1 as mc1, c2 as mc2, c3 as mc3
from aqumv_t4 where c1 > 90;
analyze aqumv_mvt4_0;
+create incremental materialized view aqumv_mvt4_1 as
+ select c2 as mc2, c1 as mc1
+ from aqumv_t4 where c1 > 95;
+analyze aqumv_mvt4_1;
-- HAVING clause pushed down to where quals.
set local enable_answer_query_using_materialized_views = off;
explain(costs off, verbose)
@@ -1742,6 +1746,122 @@ select c1, c3, avg(c2), random() from aqumv_t4 where c1
> 90 group by (c1, c3);
Optimizer: Postgres query optimizer
(10 rows)
+-- Test having quals have aggs.
+-- Could not use AQUMV.
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c1, (avg(c2))
+ -> HashAggregate
+ Output: c1, avg(c2)
+ Group Key: aqumv_t4.c1
+ Filter: (avg(aqumv_t4.c3) > '96'::numeric)
+ -> Seq Scan on aqumv.aqumv_t4
+ Output: c1, c2, c3
+ Filter: (aqumv_t4.c1 > 95)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+ c1 | avg
+-----+----------------------
+ 99 | 100.0000000000000000
+ 97 | 98.0000000000000000
+ 100 | 101.0000000000000000
+ 96 | 97.0000000000000000
+ 98 | 99.0000000000000000
+(5 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c1, (avg(c2))
+ -> HashAggregate
+ Output: c1, avg(c2)
+ Group Key: aqumv_t4.c1
+ Filter: (avg(aqumv_t4.c3) > '96'::numeric)
+ -> Seq Scan on aqumv.aqumv_t4
+ Output: c1, c2, c3
+ Filter: (aqumv_t4.c1 > 95)
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+ c1 | avg
+-----+----------------------
+ 99 | 100.0000000000000000
+ 97 | 98.0000000000000000
+ 98 | 99.0000000000000000
+ 100 | 101.0000000000000000
+ 96 | 97.0000000000000000
+(5 rows)
+
+-- Can use AQUMV.
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: c1, (avg(c2))
+ -> HashAggregate
+ Output: c1, avg(c2)
+ Group Key: aqumv_t4.c1
+ Filter: (avg(aqumv_t4.c3) > '96'::numeric)
+ -> Seq Scan on aqumv.aqumv_t4
+ Output: c1, c2, c3
+ Filter: (aqumv_t4.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+ c1 | avg
+-----+----------------------
+ 99 | 100.0000000000000000
+ 97 | 98.0000000000000000
+ 100 | 101.0000000000000000
+ 96 | 97.0000000000000000
+ 98 | 99.0000000000000000
+ 95 | 96.0000000000000000
+(6 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc1, (avg(mc2))
+ -> HashAggregate
+ Output: mc1, avg(mc2)
+ Group Key: aqumv_mvt4_0.mc1
+ Filter: (avg(aqumv_mvt4_0.mc3) > '96'::numeric)
+ -> Seq Scan on aqumv.aqumv_mvt4_0
+ Output: mc1, mc2, mc3
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(10 rows)
+
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+ c1 | avg
+-----+----------------------
+ 99 | 100.0000000000000000
+ 97 | 98.0000000000000000
+ 98 | 99.0000000000000000
+ 95 | 96.0000000000000000
+ 100 | 101.0000000000000000
+ 96 | 97.0000000000000000
+(6 rows)
+
abort;
-- Test Order By of origin query.
begin;
@@ -2732,6 +2852,321 @@ abort;
--
-- End of test external table
--
+-- Test view has aggs
+begin;
+create table t(c1 int, c2 int, c3 int, c4 int) distributed by (c1);
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+analyze t;
+create materialized view mv as
+ select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as mc4
+ from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'mc1' as the Cloudberry Database 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;
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(*), sum(c1), count(c2), avg(c3), abs((count(*) - 21))
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*)), (PARTIAL sum(c1)), (PARTIAL count(c2)),
(PARTIAL avg(c3))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(c1), PARTIAL count(c2),
PARTIAL avg(c3)
+ -> Seq Scan on aqumv.t
+ Output: c1, c2, c3, c4
+ Filter: (t.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+ count | sum | count | avg | abs
+-------+------+-------+---------------------+-----
+ 20 | 1910 | 20 | 97.5000000000000000 | 1
+(1 row)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc4, mc1, mc2, mc3, (abs((mc4 - 21)))
+ -> Seq Scan on aqumv.mv
+ Output: mc4, mc1, mc2, mc3, abs((mc4 - 21))
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+ count | sum | count | avg | abs
+-------+------+-------+---------------------+-----
+ 20 | 1910 | 20 | 97.5000000000000000 | 1
+(1 row)
+
+-- with HAVING quals
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(*), sum(c1)
+ Filter: ((avg(t.c3) > '97'::numeric) AND (abs((count(*) - 21)) > 0))
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*)), (PARTIAL sum(c1)), (PARTIAL avg(c3))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(c1), PARTIAL avg(c3)
+ -> Seq Scan on aqumv.t
+ Output: c1, c2, c3, c4
+ Filter: (t.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(12 rows)
+
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc4, mc1
+ -> Seq Scan on aqumv.mv
+ Output: mc4, mc1
+ Filter: ((mv.mc3 > '97'::numeric) AND (abs((mv.mc4 - 21)) > 0))
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(7 rows)
+
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+-- Test Order By elimination.
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(*), sum(c1)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*)), (PARTIAL sum(c1))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(c1)
+ -> Seq Scan on aqumv.t
+ Output: c1, c2, c3, c4
+ Filter: (t.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc4, mc1
+ -> Seq Scan on aqumv.mv
+ Output: mc4, mc1
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+truncate t;
+set local enable_answer_query_using_materialized_views = off;
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+ count | sum
+-------+-----
+ 0 |
+(1 row)
+
+set local enable_answer_query_using_materialized_views = on;
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+ count | sum
+-------+-----
+ 0 |
+(1 row)
+
+abort;
+-- Test query has limit while view has aggs.
+begin;
+create table t(c1 int, c2 int, c3 int, c4 int) distributed by (c1);
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+analyze t;
+create materialized view mv as
+ select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as mc4
+ from t where c1 > 90;
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'mc1' as the Cloudberry Database 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;
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Limit
+ Output: (count(*)), (sum(c1))
+ -> Finalize Aggregate
+ Output: count(*), sum(c1)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*)), (PARTIAL sum(c1))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(c1)
+ -> Seq Scan on aqumv.t
+ Output: c1, c2, c3, c4
+ Filter: (t.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Limit
+ Output: mc4, mc1
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc4, mc1
+ -> Limit
+ Output: mc4, mc1
+ -> Seq Scan on aqumv.mv
+ Output: mc4, mc1
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(10 rows)
+
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+-- offset
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Limit
+ Output: (count(*)), (sum(c1))
+ -> Finalize Aggregate
+ Output: count(*), sum(c1)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*)), (PARTIAL sum(c1))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(c1)
+ -> Seq Scan on aqumv.t
+ Output: c1, c2, c3, c4
+ Filter: (t.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(13 rows)
+
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+ count | sum
+-------+-----
+(0 rows)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Limit
+ Output: mc4, mc1
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc4, mc1
+ -> Limit
+ Output: mc4, mc1
+ -> Seq Scan on aqumv.mv
+ Output: mc4, mc1
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(10 rows)
+
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+ count | sum
+-------+-----
+(0 rows)
+
+-- no real limit
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit all;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: count(*), sum(c1)
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ Output: (PARTIAL count(*)), (PARTIAL sum(c1))
+ -> Partial Aggregate
+ Output: PARTIAL count(*), PARTIAL sum(c1)
+ -> Seq Scan on aqumv.t
+ Output: c1, c2, c3, c4
+ Filter: (t.c1 > 90)
+ Settings: enable_answer_query_using_materialized_views = 'off', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+select count(*), sum(c1) from t where c1 > 90 limit all;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit all;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Output: mc4, mc1
+ -> Seq Scan on aqumv.mv
+ Output: mc4, mc1
+ Settings: enable_answer_query_using_materialized_views = 'on', optimizer =
'off'
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+select count(*), sum(c1) from t where c1 > 90 limit all;
+ count | sum
+-------+------
+ 20 | 1910
+(1 row)
+
+abort;
reset optimizer;
reset enable_answer_query_using_materialized_views;
-- start_ignore
diff --git a/src/test/regress/sql/aqumv.sql b/src/test/regress/sql/aqumv.sql
index 8fee68f311..708c268c7b 100644
--- a/src/test/regress/sql/aqumv.sql
+++ b/src/test/regress/sql/aqumv.sql
@@ -418,6 +418,11 @@ create incremental materialized view aqumv_mvt4_0 as
from aqumv_t4 where c1 > 90;
analyze aqumv_mvt4_0;
+create incremental materialized view aqumv_mvt4_1 as
+ select c2 as mc2, c1 as mc1
+ from aqumv_t4 where c1 > 95;
+analyze aqumv_mvt4_1;
+
-- HAVING clause pushed down to where quals.
set local enable_answer_query_using_materialized_views = off;
explain(costs off, verbose)
@@ -452,6 +457,25 @@ select c1, c3, avg(c2) from aqumv_t4 where c1 > 90 group
by (c1, c3) having rand
explain(costs off, verbose)
select c1, c3, avg(c2), random() from aqumv_t4 where c1 > 90 group by (c1, c3);
+-- Test having quals have aggs.
+-- Could not use AQUMV.
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+select c1, avg(c2) from aqumv_t4 where c1 > 95 group by c1 having avg(c3) > 96;
+-- Can use AQUMV.
+set local enable_answer_query_using_materialized_views = off;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+set local enable_answer_query_using_materialized_views = on;
+explain(costs off, verbose)
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
+select c1, avg(c2) from aqumv_t4 where c1 > 90 group by c1 having avg(c3) > 96;
abort;
-- Test Order By of origin query.
@@ -680,6 +704,96 @@ abort;
--
-- End of test external table
--
+-- Test view has aggs
+begin;
+create table t(c1 int, c2 int, c3 int, c4 int) distributed by (c1);
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+analyze t;
+
+create materialized view mv as
+ select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as mc4
+ from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+analyze mv;
+
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+select count(*), sum(c1), count(c2), avg(c3), abs(count(*) - 21) from t where
c1 > 90;
+
+-- with HAVING quals
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+select count(*), sum(c1) from t where c1 > 90 having abs(count(*) - 21) > 0
and 2 > 1 and avg(c3) > 97;
+
+-- Test Order By elimination.
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+truncate t;
+set local enable_answer_query_using_materialized_views = off;
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+set local enable_answer_query_using_materialized_views = on;
+select count(*), sum(c1) from t where c1 > 90 order by 1, sum(c1 - 1) ASC;
+
+abort;
+
+-- Test query has limit while view has aggs.
+begin;
+create table t(c1 int, c2 int, c3 int, c4 int) distributed by (c1);
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+insert into t select i, i+1, i+2, i+3 from generate_series(1, 100) i;
+analyze t;
+
+create materialized view mv as
+ select sum(c1) as mc1, count(c2) as mc2, avg(c3) as mc3, count(*) as mc4
+ from t where c1 > 90;
+analyze mv;
+
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+select count(*), sum(c1) from t where c1 > 90 limit 2;
+
+-- offset
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+select count(*), sum(c1) from t where c1 > 90 limit 1 offset 1 ;
+
+-- no real limit
+set local enable_answer_query_using_materialized_views = off;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit all;
+select count(*), sum(c1) from t where c1 > 90 limit all;
+set local enable_answer_query_using_materialized_views = on;
+explain(verbose, costs off)
+select count(*), sum(c1) from t where c1 > 90 limit all;
+select count(*), sum(c1) from t where c1 > 90 limit all;
+
+abort;
reset optimizer;
reset enable_answer_query_using_materialized_views;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
