Supporting Partitioned Tables in AQUMV(Answer Query Using Materialized Views)

[avamingli]

Description

Partitioned tables are essential for optimizing query performance in OLAP environments.
They allow for more efficient data management and retrieval by dividing large datasets into smaller, more manageable pieces.

Cloudberry currently offers the AQUMV (Answer Query Using Materialized Views) feature, which significantly improves query performance. However, at present, it only supports queries on a single base table or a foreign table.
Given the widespread use of partitioned tables in OLAP databases, I propose that we extend AQUMV to support partitioned tables.

This enhancement involves two main components:

1. Data Status Maintenance for Partitioned Tables:
   When writable operations such as INSERT, UPDATE, DELETE, or TRUNCATE occur on a base table, the data status of the
   associated materialized view changes.
   For partitioned tables, this process is more complex. Since a partitioned table
   typically has a partition tree, changes to a child partition can affect its parent table and potentially all ancestor tables.
   Consequently, materialized views that reference these tables may also need to update their data status.

   For example, consider a root partitioned table P0 with two child partitioned tables: P1 and P2. Each child table further has
   two sub-partitions: P1 has P1_1 and P1_2, while P2 has P2_1 and P2_2.
   
   If we insert rows into P1_1, the data status of both mv0 (based on P0) and mv1 (based on P1) will change, as P1_1's parent table P1 and the root table P0 now contain more rows.
   

   Conversely, if we insert rows into P2_1, only the data status of mv2_1
   (based on P2_1) and mv0 will be affected, while mv1 remains unchanged because the data in P1 has not changed.
   

2. SQL Query Rewriting in the Planner:
   The AQUMV should also be able to rewrite SQL queries to utilize materialized views instead of querying partitioned tables directly. This would enable users to take full advantage of the performance benefits offered by materialized views while working with partitioned data.

By supporting partitioned tables within the AQUMV feature, we can significantly enhance the usability and performance of Cloudberry in OLAP scenarios. This enhancement will cater to the growing needs of users who rely on partitioned tables for efficient data management and analysis.

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]

1. Data Status Maintenance for Partitioned Tables:

Done the first part: #786

[jianlirong]

WoW, this is a very dreamy feature.

I'd like to add a specific scenario that we need to pay close attention to when implementing this feature. It's a real-world requirement from one of our customers.

Let's continue using the aforementioned partitioned table P0, and materialized views mv1 and mv2 as examples. Generally speaking, when we insert rows into P1_1, the data status of both mv0 (based on P0) and mv1 (based on P1) will change, as P1_1's parent table P1 and the root table P0 now contain more rows. However, there's a scenario where, although mv0 is based on P0, a special WHERE clause in mv0's definition causes its state to be unrelated to P1's data but tied to P2's data instead. In this case, even if data in P1_1 changes, mv0's state remains unaffected. In other words, we don't need to refresh mv0's data in this situation.

This scenario typically occurs when a DBA projects data from specific partitions of a partitioned table into a materialized view. They then grant access to this materialized view to specific application users, rather than granting direct access to the entire partitioned table. This approach restricts different users' access to different data within the partitioned table.

[avamingli]

Sure, will invest time to explore this.