You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/05/23 09:41:09 UTC
(cloudberry) branch main updated: [AQUMV] Store view query in gp_matview_aux for view matching.
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 bb6fef2ea0 [AQUMV] Store view query in gp_matview_aux for view matching.
bb6fef2ea0 is described below
commit bb6fef2ea0c4331e03dec31f36cf7bbce0a72007
Author: Zhang Mingli <av...@gmail.com>
AuthorDate: Wed May 21 21:14:12 2025 +0800
[AQUMV] Store view query in gp_matview_aux for view matching.
Move materialized views SQLs for AQUMV from pg_rewrite to
gp_matview_aux. This would allow for quicker access and
filtering, bypassing the evaluation of the system's 200
built-in rule entries that are often irrelevant.
before this commit:
set enable_answer_query_using_materialized_views = off;
explain(analyze, costs off, verbose) select count(a) from
t_select;
QUERY PLAN
------------------------------------------------------------------
Finalize Aggregate (actual time=0.000..0.000 rows=1 loops=1)
Output: count(a)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=0.000..0.000 rows=3 loops=1)
Output: (PARTIAL count(a))
-> Partial Aggregate (actual time=0.000..0.000 rows=1 loops=1)
Output: PARTIAL count(a)
-> Seq Scan on public.t_select (actual time=0.000..0.000
rows=340 loops=1)
Output: a
Settings: enable_answer_query_using_materialized_views = 'off',
optimizer = 'off'
Planning Time: 0.462 ms
(slice0) Executor memory: 114K bytes.
(slice1) Executor memory: 112K bytes avg x 3x(0) workers, 112K
bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.898 ms
(15 rows)
enable_answer_query_using_materialized_views = off
Planning Time: 0.462 ms
after this commit:
explain(analyze, costs off, verbose) select count(a) from
t_select;
QUERY PLAN
-----------------------------------------------------------------------
Finalize Aggregate (actual time=4.000..4.000 rows=1 loops=1)
Output: count(a)
-> Gather Motion 3:1 (slice1; segments: 3) (actual
time=4.000..4.000 rows=3 loops=1)
Output: (PARTIAL count(a))
-> Partial Aggregate (actual time=4.000..4.000 rows=1 loops=1)
Output: PARTIAL count(a)
-> Seq Scan on public.t_select (actual time=4.000..4.000
rows=0 loops=1)
Output: a
Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
Planning Time: 0.623 ms
(slice0) Executor memory: 114K bytes.
(slice1) Executor memory: 112K bytes avg x 3x(0) workers, 112K
bytes max (seg0).
Memory used: 128000kB
Optimizer: Postgres query optimizer
Execution Time: 1.670 ms
(15 rows)
enable_answer_query_using_materialized_views = off
Planning Time: 0.623 ms
Authored-by: Zhang Mingli avamingli@gmail.com
---
.../src/test/regress/expected/misc_sanity.out | 3 +-
src/backend/catalog/gp_matview_aux.c | 11 +++++--
src/backend/optimizer/plan/aqumv.c | 36 ++++++++++++++--------
src/include/catalog/gp_matview_aux.h | 4 +++
src/test/regress/expected/misc_sanity.out | 3 +-
.../singlenode_regress/expected/misc_sanity.out | 3 +-
6 files changed, 43 insertions(+), 17 deletions(-)
diff --git a/contrib/pax_storage/src/test/regress/expected/misc_sanity.out b/contrib/pax_storage/src/test/regress/expected/misc_sanity.out
index 9faaa27fc1..c88ab2025e 100644
--- a/contrib/pax_storage/src/test/regress/expected/misc_sanity.out
+++ b/contrib/pax_storage/src/test/regress/expected/misc_sanity.out
@@ -104,6 +104,7 @@ ORDER BY 1, 2;
relname | attname | atttypid
--------------------------+--------------------+--------------
gp_configuration_history | desc | text
+ gp_matview_aux | view_query | pg_node_tree
gp_version_at_initdb | productversion | text
gp_warehouse | warehouse_name | text
pg_attribute | attacl | aclitem[]
@@ -137,7 +138,7 @@ ORDER BY 1, 2;
pg_task_run_history | return_message | text
pg_task_run_history | status | text
pg_task_run_history | username | text
-(34 rows)
+(35 rows)
-- start_ignore
-- system catalogs without primary keys
diff --git a/src/backend/catalog/gp_matview_aux.c b/src/backend/catalog/gp_matview_aux.c
index 190a668883..7fcd85c6d5 100644
--- a/src/backend/catalog/gp_matview_aux.c
+++ b/src/backend/catalog/gp_matview_aux.c
@@ -41,6 +41,7 @@
#include "catalog/pg_type.h"
#include "catalog/indexing.h"
#include "cdb/cdbvars.h"
+#include "commands/matview.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/rel.h"
@@ -153,7 +154,6 @@ add_view_dependency(Oid mvoid)
recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
}
-
/*
* InsertMatviewAuxEntry
* We also insert gp_matview_tables entry here to maintain view.
@@ -168,6 +168,8 @@ InsertMatviewAuxEntry(Oid mvoid, const Query *viewQuery, bool skipdata)
List *relids;
NameData mvname;
bool has_foreign = false;
+ Relation matviewRel;
+ char *viewsql;
Assert(OidIsValid(mvoid));
@@ -187,7 +189,12 @@ InsertMatviewAuxEntry(Oid mvoid, const Query *viewQuery, bool skipdata)
values[Anum_gp_matview_aux_mvname - 1] = NameGetDatum(&mvname);
values[Anum_gp_matview_aux_has_foreign - 1] = BoolGetDatum(has_foreign);
-
+
+ matviewRel = table_open(mvoid, NoLock);
+ viewsql = nodeToString((Node *) copyObject(get_matview_query(matviewRel)));
+ table_close(matviewRel, NoLock);
+ values[Anum_gp_matview_aux_view_query - 1] = CStringGetTextDatum(viewsql);
+
if (skipdata)
values[Anum_gp_matview_aux_datastatus - 1] = CharGetDatum(MV_DATA_STATUS_EXPIRED);
else
diff --git a/src/backend/optimizer/plan/aqumv.c b/src/backend/optimizer/plan/aqumv.c
index 1a8f48e066..d32c104923 100644
--- a/src/backend/optimizer/plan/aqumv.c
+++ b/src/backend/optimizer/plan/aqumv.c
@@ -147,11 +147,12 @@ answer_query_using_materialized_views(PlannerInfo *root, AqumvContext aqumv_cont
RangeTblEntry *rte;
Oid origin_rel_oid;
RangeTblEntry *mvrte;
- Relation ruleDesc;
Relation matviewRel;
- SysScanDesc rcscan;
+ Relation mvauxDesc;
+ TupleDesc mvaux_tupdesc;
+ SysScanDesc mvscan;
HeapTuple tup;
- Form_pg_rewrite rewrite_tup;
+ Form_gp_matview_aux mvaux_tup;
bool need_close = false;
PlannerInfo *subroot;
List *mv_final_tlist = NIL; /* Final target list we want to rewrite to. */
@@ -217,20 +218,24 @@ answer_query_using_materialized_views(PlannerInfo *root, AqumvContext aqumv_cont
if (relkind == RELKIND_FOREIGN_TABLE && !aqumv_allow_foreign_table)
return mv_final_rel;
- ruleDesc = table_open(RewriteRelationId, AccessShareLock);
+ mvauxDesc = table_open(GpMatviewAuxId, AccessShareLock);
+ mvaux_tupdesc = RelationGetDescr(mvauxDesc);
- rcscan = systable_beginscan(ruleDesc, InvalidOid, false,
+ mvscan = systable_beginscan(mvauxDesc, InvalidOid, false,
NULL, 0, NULL);
- while (HeapTupleIsValid(tup = systable_getnext(rcscan)))
+ while (HeapTupleIsValid(tup = systable_getnext(mvscan)))
{
+ Datum view_query_datum;
+ char *view_query_str;
+ bool is_null;
+
CHECK_FOR_INTERRUPTS();
if (need_close)
table_close(matviewRel, AccessShareLock);
- rewrite_tup = (Form_pg_rewrite) GETSTRUCT(tup);
-
- matviewRel = table_open(rewrite_tup->ev_class, AccessShareLock);
+ mvaux_tup = (Form_gp_matview_aux) GETSTRUCT(tup);
+ matviewRel = table_open(mvaux_tup->mvoid, AccessShareLock);
need_close = true;
if (!RelationIsPopulated(matviewRel))
@@ -251,7 +256,14 @@ answer_query_using_materialized_views(PlannerInfo *root, AqumvContext aqumv_cont
/*
* Get a copy of view query to rewrite.
*/
- viewQuery = copyObject(get_matview_query(matviewRel));
+ view_query_datum = heap_getattr(tup,
+ Anum_gp_matview_aux_view_query,
+ mvaux_tupdesc,
+ &is_null);
+
+ view_query_str = TextDatumGetCString(view_query_datum);
+ viewQuery = copyObject(stringToNode(view_query_str));
+ pfree(view_query_str);
Assert(IsA(viewQuery, Query));
/*
@@ -620,8 +632,8 @@ answer_query_using_materialized_views(PlannerInfo *root, AqumvContext aqumv_cont
}
if (need_close)
table_close(matviewRel, AccessShareLock);
- systable_endscan(rcscan);
- table_close(ruleDesc, AccessShareLock);
+ systable_endscan(mvscan);
+ table_close(mvauxDesc, AccessShareLock);
return current_rel;
}
diff --git a/src/include/catalog/gp_matview_aux.h b/src/include/catalog/gp_matview_aux.h
index 1ba06b5f74..b1033d909a 100644
--- a/src/include/catalog/gp_matview_aux.h
+++ b/src/include/catalog/gp_matview_aux.h
@@ -44,6 +44,10 @@ CATALOG(gp_matview_aux,7153,GpMatviewAuxId)
bool has_foreign; /* view query has foreign tables? */
/* view's data status */
char datastatus;
+
+#ifdef CATALOG_VARLEN /* variable-length fields start here */
+ pg_node_tree view_query BKI_FORCE_NOT_NULL;
+#endif
} FormData_gp_matview_aux;
diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index 88ae3d4e92..9870214fd0 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -104,6 +104,7 @@ ORDER BY 1, 2;
relname | attname | atttypid
--------------------------+--------------------+--------------
gp_configuration_history | desc | text
+ gp_matview_aux | view_query | pg_node_tree
gp_version_at_initdb | productversion | text
gp_warehouse | warehouse_name | text
pg_attribute | attacl | aclitem[]
@@ -137,7 +138,7 @@ ORDER BY 1, 2;
pg_task_run_history | return_message | text
pg_task_run_history | status | text
pg_task_run_history | username | text
-(34 rows)
+(35 rows)
-- system catalogs without primary keys
--
diff --git a/src/test/singlenode_regress/expected/misc_sanity.out b/src/test/singlenode_regress/expected/misc_sanity.out
index 88ae3d4e92..9870214fd0 100644
--- a/src/test/singlenode_regress/expected/misc_sanity.out
+++ b/src/test/singlenode_regress/expected/misc_sanity.out
@@ -104,6 +104,7 @@ ORDER BY 1, 2;
relname | attname | atttypid
--------------------------+--------------------+--------------
gp_configuration_history | desc | text
+ gp_matview_aux | view_query | pg_node_tree
gp_version_at_initdb | productversion | text
gp_warehouse | warehouse_name | text
pg_attribute | attacl | aclitem[]
@@ -137,7 +138,7 @@ ORDER BY 1, 2;
pg_task_run_history | return_message | text
pg_task_run_history | status | text
pg_task_run_history | username | text
-(34 rows)
+(35 rows)
-- system catalogs without primary keys
--
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org