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 7be4f7fe7c751120ae47257aba0f6972865281f0 Author: Zhang Mingli <[email protected]> AuthorDate: Wed Nov 6 16:07:54 2024 +0800 Enable answer query using Materialized View for external table. Allow answer query using materialized views which have external or foreign tables. Since we don't know if the data is up to date of externel table outside CBDB, introduce a new GUC: aqumv_allow_foreign_table Let user decide if they want to use matview instead of query on external tables. create materialized view aqumv_ext_mv as select * from aqumv_ext_r; explain (costs off, verbose) select * from aqumv_ext_r; QUERY PLAN ------------------------------------------ Gather Motion 3:1 (slice1; segments: 3) Output: id -> Seq Scan on aqumv.aqumv_ext_mv Output: id Optimizer: Postgres query optimizer Index could also be used if there were on matviews. create index on aqumv_ext_mv(id); explain (costs off, verbose) select * from aqumv_ext_r where id = 5; QUERY PLAN ---------------------------------------------------------------------- Gather Motion 1:1 (slice1; segments: 1) Output: id -> Index Only Scan using aqumv_ext_mv_id_idx on aqumv.aqumv_ext_mv Output: id Index Cond: (aqumv_ext_mv.id = 5) Optimizer: Postgres query optimizer --- src/backend/catalog/gp_matview_aux.c | 12 +++- src/backend/optimizer/plan/aqumv.c | 3 + src/backend/utils/misc/guc_gp.c | 15 ++++- src/include/utils/guc.h | 1 + src/include/utils/unsync_guc_name.h | 1 + src/test/regress/expected/aqumv.out | 105 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/aqumv.sql | 48 ++++++++++++++++ 7 files changed, 180 insertions(+), 5 deletions(-) diff --git a/src/backend/catalog/gp_matview_aux.c b/src/backend/catalog/gp_matview_aux.c index cddba123d6..3a3c1a0659 100644 --- a/src/backend/catalog/gp_matview_aux.c +++ b/src/backend/catalog/gp_matview_aux.c @@ -88,8 +88,16 @@ GetViewBaseRelids(const Query *viewQuery) if (rte->rtekind != RTE_RELATION) return NIL; - /* Only support normal relation now. */ - if (get_rel_relkind(rte->relid) != RELKIND_RELATION) + char relkind = get_rel_relkind(rte->relid); + + /* + * Allow foreign table here, however we don't know if the data is + * up to date or not of the view. + * But if users want to query matview instead of query foreign tables + * outside CBDB, let them decide with aqumv_allow_foreign_table. + */ + if (relkind != RELKIND_RELATION && + relkind != RELKIND_FOREIGN_TABLE) return NIL; /* diff --git a/src/backend/optimizer/plan/aqumv.c b/src/backend/optimizer/plan/aqumv.c index d8b4e93e5b..ee84395623 100644 --- a/src/backend/optimizer/plan/aqumv.c +++ b/src/backend/optimizer/plan/aqumv.c @@ -151,6 +151,9 @@ answer_query_using_materialized_views(PlannerInfo *root, has_subclass(origin_rel_oid)) return mv_final_rel; + if (get_rel_relkind(origin_rel_oid) == RELKIND_FOREIGN_TABLE && !aqumv_allow_foreign_table) + return mv_final_rel; + ruleDesc = table_open(RewriteRelationId, AccessShareLock); rcscan = systable_beginscan(ruleDesc, InvalidOid, false, diff --git a/src/backend/utils/misc/guc_gp.c b/src/backend/utils/misc/guc_gp.c index c1bac91236..35e7078ff9 100644 --- a/src/backend/utils/misc/guc_gp.c +++ b/src/backend/utils/misc/guc_gp.c @@ -446,7 +446,8 @@ bool gp_enable_predicate_pushdown; int gp_predicate_pushdown_sample_rows; bool enable_offload_entry_to_qe = false; -bool enable_answer_query_using_materialized_views = false; +bool enable_answer_query_using_materialized_views = false; +bool aqumv_allow_foreign_table = false; bool gp_log_endpoints = false; @@ -3114,8 +3115,16 @@ struct config_bool ConfigureNamesBool_gp[] = true, NULL, NULL, NULL }, - - + { + {"aqumv_allow_foreign_table", PGC_USERSET, DEVELOPER_OPTIONS, + gettext_noop("allow answer query using materialized views which have foreign or external tables."), + NULL, + GUC_EXPLAIN + }, + &aqumv_allow_foreign_table, + false, + NULL, NULL, NULL + }, { {"gp_log_suboverflow_statement", PGC_SUSET, LOGGING_WHAT, gettext_noop("Enable logging of statements that cause subtransaction overflow."), diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index 3eac20b101..f8a715632c 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -298,6 +298,7 @@ extern int gp_appendonly_insert_files_tuples_range; extern int gp_random_insert_segments; extern bool enable_answer_query_using_materialized_views; extern bool enable_offload_entry_to_qe; +extern bool aqumv_allow_foreign_table; /* * gp_enable_multiphase_limit is not cost based. * When set to false, the planner will not use multi-phase limit. diff --git a/src/include/utils/unsync_guc_name.h b/src/include/utils/unsync_guc_name.h index a064c3dd44..2e74fc61ea 100644 --- a/src/include/utils/unsync_guc_name.h +++ b/src/include/utils/unsync_guc_name.h @@ -11,6 +11,7 @@ /* items in this file should be ordered */ "enable_answer_query_using_materialized_views", "application_name", + "aqumv_allow_foreign_table", "archive_cleanup_command", "archive_command", "archive_mode", diff --git a/src/test/regress/expected/aqumv.out b/src/test/regress/expected/aqumv.out index 7304ca663e..552a477868 100644 --- a/src/test/regress/expected/aqumv.out +++ b/src/test/regress/expected/aqumv.out @@ -2581,6 +2581,111 @@ select c2, c3 from aqumv_t7 where c1 > 90 order by c2, c3 fetch first 3 rows wit (4 rows) abort; +-- +-- Test external table +-- +begin; +CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE NO SQL; +CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE NO SQL; +--start_ignore +DROP PROTOCOL IF EXISTS demoprot; +NOTICE: protocol "demoprot" does not exist, skipping +--end_ignore +CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed +CREATE WRITABLE EXTERNAL TABLE aqumv_ext_w(id int) + LOCATION('demoprot://aqumvtextfile.txt') +FORMAT 'text' +DISTRIBUTED BY (id); +INSERT INTO aqumv_ext_w SELECT * FROM generate_series(1, 10); +CREATE READABLE EXTERNAL TABLE aqumv_ext_r(id int) + LOCATION('demoprot://aqumvtextfile.txt') +FORMAT 'text'; +create materialized view aqumv_ext_mv as + select * from aqumv_ext_r; +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'id' 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 aqumv_ext_mv; +explain (costs off, verbose) +select * from aqumv_ext_r; + QUERY PLAN +---------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Output: id + -> Foreign Scan on aqumv.aqumv_ext_r + Output: id + Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off' + Optimizer: Postgres query optimizer +(6 rows) + +select * from aqumv_ext_r; + id +---- + 2 + 3 + 4 + 7 + 8 + 5 + 6 + 9 + 10 + 1 +(10 rows) + +set local enable_answer_query_using_materialized_views = on; +set local aqumv_allow_foreign_table = on; +explain (costs off, verbose) +select * from aqumv_ext_r; + QUERY PLAN +---------------------------------------------------------------------------------- + Gather Motion 3:1 (slice1; segments: 3) + Output: id + -> Seq Scan on aqumv.aqumv_ext_mv + Output: id + Settings: enable_answer_query_using_materialized_views = 'on', optimizer = 'off' + Optimizer: Postgres query optimizer +(6 rows) + +select * from aqumv_ext_r; + id +---- + 1 + 2 + 3 + 4 + 7 + 8 + 5 + 6 + 9 + 10 +(10 rows) + +create index on aqumv_ext_mv(id); +set local enable_seqscan = off; +explain (costs off, verbose) +select * from aqumv_ext_r where id = 5; + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Gather Motion 1:1 (slice1; segments: 1) + Output: id + -> Index Only Scan using aqumv_ext_mv_id_idx on aqumv.aqumv_ext_mv + Output: id + Index Cond: (aqumv_ext_mv.id = 5) + Settings: enable_answer_query_using_materialized_views = 'on', enable_seqscan = 'off', optimizer = 'off' + Optimizer: Postgres query optimizer +(7 rows) + +select * from aqumv_ext_r where id = 5; + id +---- + 5 +(1 row) + +abort; +-- +-- End of test external table +-- 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 1f98f86769..6fe1973355 100644 --- a/src/test/regress/sql/aqumv.sql +++ b/src/test/regress/sql/aqumv.sql @@ -626,6 +626,54 @@ select c2, c3 from aqumv_t7 where c1 > 90 order by c2, c3 fetch first 3 rows wit abort; +-- +-- Test external table +-- +begin; + +CREATE OR REPLACE FUNCTION write_to_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_export' LANGUAGE C STABLE NO SQL; +CREATE OR REPLACE FUNCTION read_from_file() RETURNS integer as '$libdir/gpextprotocol.so', 'demoprot_import' LANGUAGE C STABLE NO SQL; +--start_ignore +DROP PROTOCOL IF EXISTS demoprot; +--end_ignore +CREATE TRUSTED PROTOCOL demoprot (readfunc = 'read_from_file', writefunc = 'write_to_file'); -- should succeed + +CREATE WRITABLE EXTERNAL TABLE aqumv_ext_w(id int) + LOCATION('demoprot://aqumvtextfile.txt') + +FORMAT 'text' +DISTRIBUTED BY (id); + +INSERT INTO aqumv_ext_w SELECT * FROM generate_series(1, 10); + +CREATE READABLE EXTERNAL TABLE aqumv_ext_r(id int) + LOCATION('demoprot://aqumvtextfile.txt') +FORMAT 'text'; + +create materialized view aqumv_ext_mv as + select * from aqumv_ext_r; +analyze aqumv_ext_mv; + +explain (costs off, verbose) +select * from aqumv_ext_r; +select * from aqumv_ext_r; +set local enable_answer_query_using_materialized_views = on; +set local aqumv_allow_foreign_table = on; +explain (costs off, verbose) +select * from aqumv_ext_r; +select * from aqumv_ext_r; + +create index on aqumv_ext_mv(id); +set local enable_seqscan = off; +explain (costs off, verbose) +select * from aqumv_ext_r where id = 5; +select * from aqumv_ext_r where id = 5; + +abort; +-- +-- End of test external table +-- + reset optimizer; reset enable_answer_query_using_materialized_views; -- start_ignore --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
