From 13a41f167abf78df684832aa9e748e2b433b7d0f Mon Sep 17 00:00:00 2001 From: Bharath Rupireddy Date: Mon, 8 Mar 2021 09:54:49 +0530 Subject: [PATCH v7] EXPLAIN/EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW Currently, explain/explain analyze refresh materialized view(RMV) is not allowed. We do plan the materialized view query before every refresh. I propose to show the explain/explain analyze of the select part of the materialized view. It will be useful for the user to know what exactly is being planned and executed as part of RMV. Please note that we already have explain/explain analyze CTAS/Create Mat View(CMV), where we show the explain/explain analyze of the select part. This proposal will do the same thing. The behaviour can be like this: EXPLAIN REFRESH MATERIALIZED VIEW mv1; --> will not refresh the mat view, but shows the select part's plan of mat view. EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW mv1; --> will refresh the mat view and shows the select part's plan of mat view. --- doc/src/sgml/ref/explain.sgml | 2 + src/backend/commands/explain.c | 50 +++++++++++----- src/backend/commands/matview.c | 36 +++++++++--- src/backend/commands/prepare.c | 3 +- src/backend/tcop/utility.c | 3 +- src/include/commands/explain.h | 30 +++++++++- src/include/commands/matview.h | 8 ++- src/test/regress/expected/matview.out | 83 +++++++++++++++++++++++++++ src/test/regress/sql/matview.sql | 27 +++++++++ 9 files changed, 217 insertions(+), 25 deletions(-) diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index c4512332a0..a301b6b53f 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -95,6 +95,8 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] statementEXPLAIN ANALYZE on an INSERT, UPDATE, DELETE, CREATE TABLE AS, + CREATE MATERIALIZED VIEW, + REFRESH MATERIALIZED VIEW, or EXECUTE statement without letting the command affect your data, use this approach: diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index afc45429ba..57f92e0806 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -17,6 +17,7 @@ #include "catalog/pg_type.h" #include "commands/createas.h" #include "commands/defrem.h" +#include "commands/matview.h" #include "commands/prepare.h" #include "executor/nodeHash.h" #include "foreign/fdwapi.h" @@ -53,10 +54,6 @@ explain_get_index_name_hook_type explain_get_index_name_hook = NULL; #define X_CLOSE_IMMEDIATE 2 #define X_NOWHITESPACE 4 -static void ExplainOneQuery(Query *query, int cursorOptions, - IntoClause *into, ExplainState *es, - const char *queryString, ParamListInfo params, - QueryEnvironment *queryEnv); static void ExplainPrintJIT(ExplainState *es, int jit_flags, JitInstrumentation *ji); static void report_triggers(ResultRelInfo *rInfo, bool show_relname, @@ -274,7 +271,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, { ExplainOneQuery(lfirst_node(Query, l), CURSOR_OPT_PARALLEL_OK, NULL, es, - pstate->p_sourcetext, params, pstate->p_queryEnv); + pstate->p_sourcetext, params, pstate->p_queryEnv, + NULL); /* Separate plans with an appropriate separator */ if (lnext(rewritten, l) != NULL) @@ -357,11 +355,11 @@ ExplainResultDesc(ExplainStmt *stmt) * * "into" is NULL unless we are explaining the contents of a CreateTableAsStmt. */ -static void +void ExplainOneQuery(Query *query, int cursorOptions, IntoClause *into, ExplainState *es, const char *queryString, ParamListInfo params, - QueryEnvironment *queryEnv) + QueryEnvironment *queryEnv, RefreshMatViewInfo *matviewInfo) { /* planner will not cope with utility statements */ if (query->commandType == CMD_UTILITY) @@ -402,7 +400,8 @@ ExplainOneQuery(Query *query, int cursorOptions, /* run it (if needed) and produce output */ ExplainOnePlan(plan, into, es, queryString, params, queryEnv, - &planduration, (es->buffers ? &bufusage : NULL)); + &planduration, (es->buffers ? &bufusage : NULL), + matviewInfo); } } @@ -455,7 +454,7 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, Assert(list_length(rewritten) == 1); ExplainOneQuery(linitial_node(Query, rewritten), CURSOR_OPT_PARALLEL_OK, ctas->into, es, - queryString, params, queryEnv); + queryString, params, queryEnv, NULL); } else if (IsA(utilityStmt, DeclareCursorStmt)) { @@ -474,7 +473,8 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, Assert(list_length(rewritten) == 1); ExplainOneQuery(linitial_node(Query, rewritten), dcs->options, NULL, es, - queryString, params, queryEnv); + queryString, params, queryEnv, + NULL); } else if (IsA(utilityStmt, ExecuteStmt)) ExplainExecuteQuery((ExecuteStmt *) utilityStmt, into, es, @@ -486,6 +486,16 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, else ExplainDummyGroup("Notify", NULL, es); } + else if(IsA(utilityStmt, RefreshMatViewStmt)) + { + RefreshMatViewExplainInfo explainInfo; + + explainInfo.es = es; + explainInfo.queryEnv = queryEnv; + + ExecRefreshMatView((RefreshMatViewStmt *) utilityStmt, + queryString, params, NULL, &explainInfo); + } else { if (es->format == EXPLAIN_FORMAT_TEXT) @@ -512,7 +522,7 @@ void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, const char *queryString, ParamListInfo params, QueryEnvironment *queryEnv, const instr_time *planduration, - const BufferUsage *bufusage) + const BufferUsage *bufusage, RefreshMatViewInfo *matviewInfo) { DestReceiver *dest; QueryDesc *queryDesc; @@ -553,6 +563,8 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, */ if (into) dest = CreateIntoRelDestReceiver(into); + else if (matviewInfo && OidIsValid(matviewInfo->OIDNewHeap)) + dest = CreateTransientRelDestReceiver(matviewInfo->OIDNewHeap); else dest = None_Receiver; @@ -577,8 +589,12 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, { ScanDirection dir; - /* EXPLAIN ANALYZE CREATE TABLE AS WITH NO DATA is weird */ - if (into && into->skipData) + /* + * EXPLAIN ANALYZE CREATE TABLE AS WITH NO DATA or EXPLAN ANALYZE + * REFRESH MATERIALIZED VIEW WITH NO DATA is weird. + */ + if ((into && into->skipData) || + (matviewInfo && matviewInfo->skipData)) dir = NoMovementScanDirection; else dir = ForwardScanDirection; @@ -586,6 +602,14 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, /* run the plan */ ExecutorRun(queryDesc, dir, 0L, true); + /* + * Collect the number of rows inserted in case of REFRESH MATERIALIZED + * VIEW which will be used while merging the newly generated data with + * the existing materialized view data in ExecRefreshMatView. + */ + if (matviewInfo) + matviewInfo->processed = queryDesc->estate->es_processed; + /* run cleanup too */ ExecutorFinish(queryDesc); diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 18e18fa627..c7d8c52712 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -146,7 +146,8 @@ SetMatViewPopulatedState(Relation relation, bool newstate) */ ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, - ParamListInfo params, QueryCompletion *qc) + ParamListInfo params, QueryCompletion *qc, + RefreshMatViewExplainInfo *explainInfo) { Oid matviewOid; Relation matviewRel; @@ -182,8 +183,11 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, save_sec_context | SECURITY_LOCAL_USERID_CHANGE); save_nestlevel = NewGUCNestLevel(); - OIDNewHeap = get_new_heap_oid(stmt, matviewRel, matviewOid, - &relpersistence); + if (explainInfo && !explainInfo->es->analyze) + OIDNewHeap = InvalidOid; + else + OIDNewHeap = get_new_heap_oid(stmt, matviewRel, matviewOid, + &relpersistence); /* * Now lock down security-restricted operations. @@ -192,7 +196,24 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, save_sec_context | SECURITY_RESTRICTED_OPERATION); /* Generate the data, if wanted. */ - if (!stmt->skipData) + if (explainInfo) + { + RefreshMatViewInfo matViewInfo; + + matViewInfo.OIDNewHeap = OIDNewHeap; + matViewInfo.skipData = stmt->skipData; + matViewInfo.processed = 0; + + dataQuery = rewrite_refresh_matview_query(dataQuery); + + ExplainOneQuery(dataQuery, + CURSOR_OPT_PARALLEL_OK, NULL, explainInfo->es, + queryString, params, explainInfo->queryEnv, + &matViewInfo); + + processed = matViewInfo.processed; + } + else if (!stmt->skipData) { dataQuery = rewrite_refresh_matview_query(dataQuery); @@ -200,9 +221,10 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, queryString); } - match_matview_with_new_data(stmt, matviewRel, matviewOid, OIDNewHeap, - relowner, save_sec_context, relpersistence, - processed); + if (OidIsValid(OIDNewHeap)) + match_matview_with_new_data(stmt, matviewRel, matviewOid, OIDNewHeap, + relowner, save_sec_context, relpersistence, + processed); table_close(matviewRel, NoLock); diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index f767751c71..56c7432879 100644 --- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -673,7 +673,8 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es, if (pstmt->commandType != CMD_UTILITY) ExplainOnePlan(pstmt, into, es, query_string, paramLI, queryEnv, - &planduration, (es->buffers ? &bufusage : NULL)); + &planduration, (es->buffers ? &bufusage : NULL), + NULL); else ExplainOneUtility(pstmt->utilityStmt, into, es, query_string, paramLI, queryEnv); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 05bb698cf4..20087addb4 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1621,7 +1621,8 @@ ProcessUtilitySlow(ParseState *pstate, PG_TRY(); { address = ExecRefreshMatView((RefreshMatViewStmt *) parsetree, - queryString, params, qc); + queryString, params, qc, + NULL); } PG_FINALLY(); { diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index e94d9e49cf..7f27d92df1 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -61,6 +61,27 @@ typedef struct ExplainState ExplainWorkersState *workers_state; /* needed if parallel plan */ } ExplainState; +/* + * Refresh Materialized View information passed across functions for EXPLAIN + * execution. + */ +typedef struct RefreshMatViewInfo +{ + /* Oid of the new heap created. */ + Oid OIDNewHeap; + /* Is WITH NO DATA clause specified? */ + bool skipData; + /* Number of rows inserted. */ + uint64 processed; +} RefreshMatViewInfo; + +/* EXPLAIN information shared to ExecRefreshMatView(). */ +typedef struct RefreshMatViewExplainInfo +{ + ExplainState *es; + QueryEnvironment *queryEnv; +} RefreshMatViewExplainInfo; + /* Hook for plugins to get control in ExplainOneQuery() */ typedef void (*ExplainOneQuery_hook_type) (Query *query, int cursorOptions, @@ -91,7 +112,14 @@ extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, const char *queryString, ParamListInfo params, QueryEnvironment *queryEnv, const instr_time *planduration, - const BufferUsage *bufusage); + const BufferUsage *bufusage, + RefreshMatViewInfo *matviewInfo); + +extern void ExplainOneQuery(Query *query, int cursorOptions, + IntoClause *into, ExplainState *es, + const char *queryString, ParamListInfo params, + QueryEnvironment *queryEnv, + RefreshMatViewInfo *matviewInfo); extern void ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc); extern void ExplainPrintTriggers(ExplainState *es, QueryDesc *queryDesc); diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h index 214b1c1df6..1d60180ebc 100644 --- a/src/include/commands/matview.h +++ b/src/include/commands/matview.h @@ -15,6 +15,7 @@ #define MATVIEW_H #include "catalog/objectaddress.h" +#include "commands/explain.h" #include "nodes/params.h" #include "nodes/parsenodes.h" #include "tcop/dest.h" @@ -23,8 +24,11 @@ extern void SetMatViewPopulatedState(Relation relation, bool newstate); -extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, - ParamListInfo params, QueryCompletion *qc); +extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, + const char *queryString, + ParamListInfo params, + QueryCompletion *qc, + RefreshMatViewExplainInfo *explainInfo); extern DestReceiver *CreateTransientRelDestReceiver(Oid oid); diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 4b3a2e0cb7..305e511fea 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -668,3 +668,86 @@ NOTICE: relation "matview_ine_tab" already exists, skipping (0 rows) DROP MATERIALIZED VIEW matview_ine_tab; +-- test cases for explain/explain analyze refresh materialized view +CREATE TABLE mv_exp_tbl (a) AS SELECT * FROM generate_series(1, 10); +CREATE MATERIALIZED VIEW mv_exp (a) AS + SELECT * FROM mv_exp_tbl WHERE a > 5; +EXPLAIN (COSTS OFF) + REFRESH MATERIALIZED VIEW mv_exp WITH NO DATA; + QUERY PLAN +------------------------ + Seq Scan on mv_exp_tbl + Filter: (a > 5) +(2 rows) + +REFRESH MATERIALIZED VIEW mv_exp WITH NO DATA; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + REFRESH MATERIALIZED VIEW mv_exp WITH NO DATA; + QUERY PLAN +----------------------------------------- + Seq Scan on mv_exp_tbl (never executed) + Filter: (a > 5) +(2 rows) + +SELECT * FROM mv_exp ORDER BY 1; -- ERROR +ERROR: materialized view "mv_exp" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +EXPLAIN (COSTS OFF) + REFRESH MATERIALIZED VIEW mv_exp; + QUERY PLAN +------------------------ + Seq Scan on mv_exp_tbl + Filter: (a > 5) +(2 rows) + +REFRESH MATERIALIZED VIEW mv_exp; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + REFRESH MATERIALIZED VIEW mv_exp; + QUERY PLAN +------------------------------------------------ + Seq Scan on mv_exp_tbl (actual rows=5 loops=1) + Filter: (a > 5) + Rows Removed by Filter: 5 +(3 rows) + +SELECT * FROM mv_exp ORDER BY 1; -- OK + a +---- + 6 + 7 + 8 + 9 + 10 +(5 rows) + +CREATE UNIQUE INDEX ON mv_exp (a); +EXPLAIN (COSTS OFF) + REFRESH MATERIALIZED VIEW CONCURRENTLY mv_exp; + QUERY PLAN +------------------------ + Seq Scan on mv_exp_tbl + Filter: (a > 5) +(2 rows) + +REFRESH MATERIALIZED VIEW CONCURRENTLY mv_exp; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + REFRESH MATERIALIZED VIEW CONCURRENTLY mv_exp; + QUERY PLAN +------------------------------------------------ + Seq Scan on mv_exp_tbl (actual rows=5 loops=1) + Filter: (a > 5) + Rows Removed by Filter: 5 +(3 rows) + +SELECT * FROM mv_exp ORDER BY 1; -- OK + a +---- + 6 + 7 + 8 + 9 + 10 +(5 rows) + +DROP MATERIALIZED VIEW mv_exp; +DROP TABLE mv_exp_tbl; diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 4a4bd0d6b6..67a45bbde7 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -287,3 +287,30 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS SELECT 1 / 0 WITH NO DATA; -- ok DROP MATERIALIZED VIEW matview_ine_tab; + +-- test cases for explain/explain analyze refresh materialized view +CREATE TABLE mv_exp_tbl (a) AS SELECT * FROM generate_series(1, 10); +CREATE MATERIALIZED VIEW mv_exp (a) AS + SELECT * FROM mv_exp_tbl WHERE a > 5; +EXPLAIN (COSTS OFF) + REFRESH MATERIALIZED VIEW mv_exp WITH NO DATA; +REFRESH MATERIALIZED VIEW mv_exp WITH NO DATA; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + REFRESH MATERIALIZED VIEW mv_exp WITH NO DATA; +SELECT * FROM mv_exp ORDER BY 1; -- ERROR +EXPLAIN (COSTS OFF) + REFRESH MATERIALIZED VIEW mv_exp; +REFRESH MATERIALIZED VIEW mv_exp; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + REFRESH MATERIALIZED VIEW mv_exp; +SELECT * FROM mv_exp ORDER BY 1; -- OK +CREATE UNIQUE INDEX ON mv_exp (a); +EXPLAIN (COSTS OFF) + REFRESH MATERIALIZED VIEW CONCURRENTLY mv_exp; +REFRESH MATERIALIZED VIEW CONCURRENTLY mv_exp; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + REFRESH MATERIALIZED VIEW CONCURRENTLY mv_exp; +SELECT * FROM mv_exp ORDER BY 1; -- OK + +DROP MATERIALIZED VIEW mv_exp; +DROP TABLE mv_exp_tbl; -- 2.25.1