GitHub user avamingli edited a comment on the discussion: [Feature] Dynamic
Tables
> I think we can leverage the combination of these techniques, including
> materialized views for external tables, dynamic tables (auto refreshing
> materialized views) and AQUMV to solve the problem often raised by customers
> who are big fans of a lakehouse architecture: how can we run queries on
> external tables as fast as internal tables?
See full example in 7a13989d24192bb471ff668a1754ae489e538ef5.
```sql
CREATE READABLE EXTERNAL TABLE ext_r(id int)
LOCATION('demoprot://dynamic_table_text_file.txt')
FORMAT 'text';
EXPLAIN(COSTS OFF, VERBOSE)
SELECT sum(id) FROM ext_r where id > 5;
QUERY PLAN
--------------------------------------------------------------
Finalize Aggregate
Output: sum(id)
-> Gather Motion 3:1 (slice1; segments: 3)
Output: (PARTIAL sum(id))
-> Partial Aggregate
Output: PARTIAL sum(id)
-> Foreign Scan on dynamic_table_schema.ext_r
Output: id
Filter: (ext_r.id > 5)
CREATE DYNAMIC TABLE dt_external AS
SELECT * FROM ext_r where id > 5;
ANALYZE dt_external;
SET optimizer = OFF;
SET enable_answer_query_using_materialized_views = ON;
SET aqumv_allow_foreign_table = ON;
EXPLAIN(COSTS OFF, VERBOSE)
SELECT sum(id) FROM ext_r where id > 5;
QUERY PLAN
---------------------------------------------------------------
Finalize Aggregate
Output: sum(id)
-> Gather Motion 3:1 (slice1; segments: 3)
Output: (PARTIAL sum(id))
-> Partial Aggregate
Output: PARTIAL sum(id)
-> Seq Scan on dynamic_table_schema.dt_external
Output: id
Settings: enable_answer_query_using_materialized_views = 'on',
optimizer = 'off'
Optimizer: Postgres query optimizer
(10 rows)
```
GitHub link:
https://github.com/apache/cloudberry/discussions/706#discussioncomment-11463761
----
This is an automatically sent email for dev@cloudberry.apache.org.
To unsubscribe, please send an email to: dev-unsubscr...@cloudberry.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@cloudberry.apache.org
For additional commands, e-mail: dev-h...@cloudberry.apache.org
