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/01/10 02:28:19 UTC
(cloudberry) 04/18: Improve refresh materialized view with "no data" option
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
commit ec848dee4ac35157a90f9e186ddcdcd4a72458be
Author: wenru yan <yw...@vmware.com>
AuthorDate: Mon May 8 04:10:39 2023 +0000
Improve refresh materialized view with "no data" option
In postgres, with option "WITH NO DATA", the materialized view will be
truncated without run the query.
In GPDB, the refresh clause is dispatched to segments when execute the
query plan. But for WITH NO DATA option, it should be like a TRUNCATE
like in postgres, so it doesn't need to take long time to run the query.
By adding an constant-FALSE to the qual to generate a result plan with
One-Time Filter. We have hold matview's distribution policy into the
query->intoPolicy, planner will create a motion node on the top so that
the refresh clause will still dispatch to segments with the query plan
and the query can be execute quickly.
---
src/backend/commands/matview.c | 15 +++++++++++++
src/test/regress/expected/matview.out | 29 +++++++++++++++++++++++++
src/test/regress/expected/matview_optimizer.out | 29 +++++++++++++++++++++++++
src/test/regress/sql/matview.sql | 17 +++++++++++++++
4 files changed, 90 insertions(+)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 8448d2335f..a0180ce041 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -796,6 +796,21 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
if (list_length(rewritten) != 1)
elog(ERROR, "unexpected rewrite result for REFRESH MATERIALIZED VIEW");
query = (Query *) linitial(rewritten);
+ /*
+ * In GPDB, the refresh clause is dispatched to segments when execute the query plan.
+ * But for WITH NO DATA option, it's effectively like a TRUNCATE, so it doesn't need
+ * to take a long time to run the query.
+ *
+ * Add a constant-FALSE to the qual to simulate a plan like this, dispatch the refresh
+ * clause without run the long query:
+ * Motion
+ * Result (cost=0.00..0.01 rows=1 width=0)
+ * One-Time Filter: false
+ * Planner create the motion node on the top according to the matview's distribution
+ * policy in the query->intoPolicy.
+ */
+ if (refreshClause->skipData)
+ query->jointree->quals = (Node *) makeBoolConst(false, false);
/* Check for user-requested abort. */
CHECK_FOR_INTERRUPTS();
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index f0b1ad9c3d..22db3edd46 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -780,3 +780,32 @@ select count(*) from mv_fast_relfilenode_1 natural join mv_fast_relfilenode_2;
reset gp_enable_refresh_fast_path;
drop materialized view mv_fast;
+-- test REFRESH MATERIALIZED VIEW with 'WITH NO DATA' option can be executed immediately.
+DROP TABLE IF EXISTS mvtest_twn;
+CREATE TABLE mvtest_twn(a int);
+CREATE MATERIALIZED VIEW mat_view_twn as SELECT a.a as p, b.a as q, c.a as x, d.a as y FROM mvtest_twn a, mvtest_twn b, mvtest_twn c, mvtest_twn d;
+INSERT INTO mvtest_twn SELECT i FROM generate_series(1,10000)i;
+-- t1 contains 10000 tuples, after cross join it four times, the output is much too huge
+-- refresh with 'no data' should not actually execute the sql
+set statement_timeout = 5000;
+REFRESH MATERIALIZED VIEW mat_view_twn WITH NO DATA;
+reset statement_timeout;
+SELECT relispopulated FROM pg_class WHERE oid = 'mat_view_twn'::regclass;
+ relispopulated
+----------------
+ f
+(1 row)
+
+SELECT relispopulated FROM gp_dist_random('pg_class') WHERE oid = 'mat_view_twn'::regclass;
+ relispopulated
+----------------
+ f
+ f
+ f
+(3 rows)
+
+SELECT * FROM mat_view_twn;
+ERROR: materialized view "mat_view_twn" has not been populated
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+DROP MATERIALIZED VIEW mat_view_twn;
+DROP TABLE mvtest_twn;
diff --git a/src/test/regress/expected/matview_optimizer.out b/src/test/regress/expected/matview_optimizer.out
index a0c61de98d..e5fd0ba751 100644
--- a/src/test/regress/expected/matview_optimizer.out
+++ b/src/test/regress/expected/matview_optimizer.out
@@ -797,3 +797,32 @@ select count(*) from mv_fast_relfilenode_1 natural join mv_fast_relfilenode_2;
reset gp_enable_refresh_fast_path;
drop materialized view mv_fast;
+-- test REFRESH MATERIALIZED VIEW with 'WITH NO DATA' option can be executed immediately.
+DROP TABLE IF EXISTS mvtest_twn;
+CREATE TABLE mvtest_twn(a int);
+CREATE MATERIALIZED VIEW mat_view_twn as SELECT a.a as p, b.a as q, c.a as x, d.a as y FROM mvtest_twn a, mvtest_twn b, mvtest_twn c, mvtest_twn d;
+INSERT INTO mvtest_twn SELECT i FROM generate_series(1,10000)i;
+-- t1 contains 10000 tuples, after cross join it four times, the output is much too huge
+-- refresh with 'no data' should not actually execute the sql
+set statement_timeout = 5000;
+REFRESH MATERIALIZED VIEW mat_view_twn WITH NO DATA;
+reset statement_timeout;
+SELECT relispopulated FROM pg_class WHERE oid = 'mat_view_twn'::regclass;
+ relispopulated
+----------------
+ f
+(1 row)
+
+SELECT relispopulated FROM gp_dist_random('pg_class') WHERE oid = 'mat_view_twn'::regclass;
+ relispopulated
+----------------
+ f
+ f
+ f
+(3 rows)
+
+SELECT * FROM mat_view_twn;
+ERROR: materialized view "mat_view_twn" has not been populated
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+DROP MATERIALIZED VIEW mat_view_twn;
+DROP TABLE mvtest_twn;
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index ead969f8e4..b95d34e96f 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -341,3 +341,20 @@ select count(*) from mv_fast_relfilenode_1 natural join mv_fast_relfilenode_2;
reset gp_enable_refresh_fast_path;
drop materialized view mv_fast;
+
+-- test REFRESH MATERIALIZED VIEW with 'WITH NO DATA' option can be executed immediately.
+DROP TABLE IF EXISTS mvtest_twn;
+CREATE TABLE mvtest_twn(a int);
+CREATE MATERIALIZED VIEW mat_view_twn as SELECT a.a as p, b.a as q, c.a as x, d.a as y FROM mvtest_twn a, mvtest_twn b, mvtest_twn c, mvtest_twn d;
+INSERT INTO mvtest_twn SELECT i FROM generate_series(1,10000)i;
+-- t1 contains 10000 tuples, after cross join it four times, the output is much too huge
+-- refresh with 'no data' should not actually execute the sql
+set statement_timeout = 5000;
+REFRESH MATERIALIZED VIEW mat_view_twn WITH NO DATA;
+reset statement_timeout;
+SELECT relispopulated FROM pg_class WHERE oid = 'mat_view_twn'::regclass;
+SELECT relispopulated FROM gp_dist_random('pg_class') WHERE oid = 'mat_view_twn'::regclass;
+SELECT * FROM mat_view_twn;
+
+DROP MATERIALIZED VIEW mat_view_twn;
+DROP TABLE mvtest_twn;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org