Enable answer query using Materialized View for external table.

[my-ship-it]

Description

Currently, for inner table, planner knows to use MV to speed up query, for example:

create table tbl_heap(c1 int, c2 int);
insert into tbl_heap select generate_series(1,1000000);
create materialized view mv as select c1, c2 from tbl_heap where c1 > 10 and c2 < 20;
analyze mv;
set optimizer = off;
set enable_answer_query_using_materialized_views = on;

postgres=# explain select c1, c2 from tbl_heap where c1 > 10 and c2 < 20;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.03 rows=1 width=8)
   ->  Seq Scan on mv  (cost=0.00..1.01 rows=1 width=8)
 Optimizer: Postgres query optimizer
(3 rows)

But for external table, it doesn't work

create external table tbl_ext(c1 int, c2 int) location('gpfdist://localhost:8080/tbl_ext') format 'csv';
create materialized view mv_ext as select c1, c2 from tbl_ext where c1 > 10 and c2 < 20;
analyze mv_ext;
set optimizer = off;
set enable_answer_query_using_materialized_views = on;

postgres=# explain select c1, c2 from tbl_ext where c1 > 10 and c2 < 20;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..246940.11 rows=17320508 width=8)
   ->  Foreign Scan on tbl_ext  (cost=0.00..16000.00 rows=5773503 width=8)
         Filter: ((c1 > 10) AND (c2 < 20))
 Optimizer: Postgres query optimizer
(4 rows)

The reason why the replacement for external doesn't work is that, if external table changes, planner doesn't know that, and replacement of MV would produce wrong results.

But for some cases, access to external table(for example, Iceberg, Hudi, and etc) is much slower than inner table(maybe 10X slower or more). If user knows there is no data changes for external table, we could speed up query against external table, which is a big win in this scenario. Another approach is that user load external table into inner table for analysis, but there is latency, and loading process is also time consuming.

How about we introduce a GUC to control the behavior? If the user is 100% sure that the external table data has not been modified, they could turn on the GUC and speed up query.

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[avamingli]

But for some cases, access to external table(for example, Iceberg, Hudi, and etc) is much slower than inner table(maybe 10X slower or more). If user knows there is no data changes for external table, we could speed up query against external table, which is a big win in this scenario. Another approach is that user load external table into inner table for analysis, but there is latency, and loading process is also time consuming.

How about we introduce a GUC to control the behavior? If the user is 100% sure that the external table data has not been modified, they could turn on the GUC and speed up query.

+1, we could enable this feature if users are aware what the behavior is and they could accept the risk.

[leborchuk]

What will you do if there are two external tables, and one has been changed but the other hasn't?

Maybe we should add a per-table property, like the modification time. The user could manually set the modification time, and if the MV refresh time is later than the modification time then it could be used to answer the query.

[avamingli]

What will you do if there are two external tables, and one has been changed but the other hasn't?

Currently, AQUMV requires only one simple table, join is supported.

Maybe we should add a per-table property, like the modification time. The user could manually set the modification time, and if the MV refresh time is later than the modification time then it could be used to answer the query.

Already have that in gp_matview_aux.

[leborchuk]

Well, why then we need to add a special one GUC? Just rewrite query using matview ...

I will explain. I want to challenge the decision. Do we really need to add GUC or just does not sure and want user to decide if it could be used or not?

We already have ~425 GUCs in ./src/backend/utils/misc/guc_gp.c. The original GP7 had ~416. It's too hard to choose the right set of GUC values. An ordinary user can't do it.

[my-ship-it]

Well, there are too many GUCs, and it's difficult for user to decide what GUCs to use(as #699 mentioned). How about we design it as a feature, namely specific kind of materialized view(Eventual Consistency MV)?

The MV is maintained by a background worker, and would be refreshed periodically instead of synchronous approach.
If the external table changes rarely, the MV is always latest. Otherwise, there is a gap between content of MV and external table, but it is an acceptable behavior in special circumstances. Of course, after users update the external table, they can also refresh the materialized view in time to keep consistence.

[leborchuk]

Hm, sounds good. Eventual Consistency MV could have any source - external storage, internal very expensive query and so on.

The issue here that the result is also eventual, but how much eventual? Users can tolerate data lag, but they want to know how large the lag is? How could we help answer this query? By storing for each Eventual Consistency MV date when it was refreshed. And together with data result show that to get data we use:
Eventual Consistency MV1 refreshed
Eventual Consistency MV2 refreshed
Eventual Consistency MV3 refreshed

The user could store data in a new eventual consistency MV (2nd level of matviews). In that case, we most probably need to store refresh data for dependent objects too.

It all sounds like a complicated change. I'm not sure if we should add additional refresh data for dependent objects :-/ It could be great but need a lot of work to be done. To be honest, users can gather this information themselves (make additional query to gather metadata).

[avamingli]

The user could store data in a new eventual consistency MV (2nd level of matviews). In that case, we most probably need to store refresh data for dependent objects too.

There are no such cases, be clear that a view query is a SQL on tables user want to query.
A matview itself is a table too.
If you are querying a SQL based on a matview itself, that is, and not the base tables behind that matview.

Let's focus on the discussion itself, we have customers want to use AQUMV for external table and they know the risk that the AQUMV may give a wrong result because we know nothing about data outside CBDB. And they use a GUC to control that.
The default value is false, the feature doesn't have an impact if others don't have that demand.