Help Center/ Data Warehouse Service/ Developer Guide/ Developer Guide (9.1.1.x)/ DWS Materialized Views/ Query Rewriting/ Structure Matching Query Rewriting
Updated on 2026-07-02 GMT+08:00
View PDF
Share

Structure Matching Query Rewriting

In DWS, structure matching query rewriting does not require an exact match between the query and materialized view definitions. The focus is on deriving the query result by rewriting the materialized view result, which may involve adding projection columns, filter conditions, aggregation columns, sorting columns, and limits to match the original query result.

DWS facilitates four types of result matching query rewriting: condition rewriting, projection rewriting, aggregation rewriting, and JOIN rewriting.

Constraints

  • The statement for structure matching query rewriting is complex. Contact technical support before using the statement.
  • Only queries in the form of SELECT (condition) -PROJECTION (projection) -JOIN-GROUP BY (SPJG) are supported.
  • The query cannot contain objects that are not RELATION, VIEW, MATVIEW, or FOREIGN TABLE.
  • Materialized views cannot contain subqueries.
  • Materialized views cannot contain CTEs, window functions, set operations, synonyms, rowSecurity, and FOR UPDATE clause.
  • Queries can contain limit offset, having, and grouping sets, but materialized views cannot include these.
  • When a partition is specified for query, structure matching rewriting is not supported.
  • distinct on cannot be rewritten.
  • The following aggregation types are supported: sum, count, min, max, avg, and roaringbitmap.

Condition Rewriting

Both the materialized view and the query statement may contain filter criteria. Condition rewriting can be performed if the following conditions are met:

  • The query has filter criteria, but the materialized view does not have any. In this case, you can add filter criteria for rewriting.
  • Both the query and the materialized view have filter criteria, but the range defined by the filter criteria in the query is a subset of the range defined in the materialized view. To address this, additional filter criteria can be added to narrow down the data scope of the materialized view for rewriting.
  • The query and the materialized view both utilize filter criteria, but the filter criteria of the materialized view are included in the filter criteria of the query. In this case, the query can be rewritten by incorporating additional filter criteria beyond those in the materialized view.

Condition rewriting examples:

Create base table t1 and materialized views mv1_t1, mv2_t1, and mv3_t1.

1
2
3
4
 
create table t1(a int, b int, c int);
create materialized view mv1_t1 enable query rewrite as select c from t1;
create materialized view mv2_t1 enable query rewrite as select a, b, c from t1 where a > 2;
create materialized view mv3_t1 enable query rewrite as select a, b, c from t1 where a > 1 and b < 2;

Scenario 1: The query has filter criteria, but the materialized view does not have any.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
explain (costs off, verbose off, nodes off) select c from t1 where c > 2;
                  QUERY PLAN                   
-----------------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER) 
   2 |    ->  Seq Scan on mv1_t1    

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on mv1_t1
         Filter: (c >= 3)

   ====== Query Summary =====   
 -------------------------------
 System available mem: 4710400KB
 Query Max mem: 4710400KB
 Query estimated mem: 1024KB
(15 rows)

Scenario 2: The filter criteria of the query are a subset of the range represented by the filter criteria of the materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
explain (costs off, verbose on, nodes off) select a from t1 where a > 3;
                  QUERY PLAN                   
-----------------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER) 
   2 |    ->  Seq Scan on mv2_t1    

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on mv2_t1
         Filter: (a >= 4)

   ====== Query Summary =====   
 -------------------------------
 System available mem: 4710400KB
 Query Max mem: 4710400KB
 Query estimated mem: 1024KB
(15 rows)

Scenario 3: The filter criteria of the materialized view are in the filter criteria of the query.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
explain (costs off, verbose on, nodes off) select a, b, c from t1 where a > 1 and b < 2 and c > 1;
                  QUERY PLAN                   
-----------------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER) 
   2 |    ->  Seq Scan on mv3_t1    

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on mv3_t1
         Filter: (c >= 2)

   ====== Query Summary =====   
 -------------------------------
 System available mem: 4710400KB
 Query Max mem: 4710400KB
 Query estimated mem: 1024KB
(15 rows)

Constraint: The added filter criteria are not in the output columns of the materialized view.

1
2
 
create table t1(a int, b int, c int);
create materialized view mv1_t1 enable query rewrite as select c from t1;

If the extra filter condition is not about the materialized view output column c, mv1_t1 cannot be used for rewriting.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
 
explain (costs off, verbose on, nodes off) select c from t1 where a > 0;
                  QUERY PLAN                   
-----------------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER) 
   2 |    ->  Seq Scan on t1        

 Predicate Information (identified by plan id)
 ---------------------------------------------
   2 --Seq Scan on t1
         Filter: (a > 0)

   ====== Query Summary =====   
 -------------------------------
 System available mem: 4710400KB
 Query Max mem: 4710400KB
 Query estimated mem: 1024KB
(15 rows)

Projection Rewriting

Materialized view structure matching query rewriting does not require that the output columns of the query and materialized view be exactly the same. Rewriting can be performed in the following scenarios:

  • The output columns of a query are a combination of output columns of a materialized view.
  • The output column of a query is a function or expression added to the output column of a materialized view.
  • The output columns of a query can be replaced with the output columns of a materialized view through equivalence classes.

Condition rewriting examples:

Create the base table t2 and materialized views mv1_t2 and mv2_t2.

create table t2(a int, b int, c int);
create materialized view mv1_t2 enable query rewrite as select a, c from t2;
create materialized view mv2_t2 enable query rewrite as select a from t2 where a = b;

Scenario 1: The output columns of a query are a combination of output columns of a materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
explain (costs off, verbose on, nodes off) select a + c from t2;
                      QUERY PLAN                       
-------------------------------------------------------
  id |            operation             
 ----+----------------------------------
   1 | ->  Streaming (type: GATHER)     
   2 |    ->  Seq Scan on public.mv1_t2 

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: ((a + c))
   2 --Seq Scan on public.mv1_t2
         Output: (a + c)

Scenario 2: The output column of a query is a function or expression added to the output column of a materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
explain (costs off, verbose on, nodes off) select nvl(a, 10) from t2;
                      QUERY PLAN                       
-------------------------------------------------------
  id |            operation             
 ----+----------------------------------
   1 | ->  Streaming (type: GATHER)     
   2 |    ->  Seq Scan on public.mv1_t2 

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: (COALESCE(a, 10))
   2 --Seq Scan on public.mv1_t2
         Output: COALESCE(a, 10)

Scenario 3: The output columns of a query can be replaced with the output columns of a materialized view through equivalence classes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
explain (costs off, verbose on, nodes off) select b from t2 where a = b;
                      QUERY PLAN                       
-------------------------------------------------------
  id |            operation             
 ----+----------------------------------
   1 | ->  Streaming (type: GATHER)     
   2 |    ->  Seq Scan on public.mv2_t2 

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a
   2 --Seq Scan on public.mv2_t2
         Output: a

Aggregation Rewriting

Materialized view structure matching query rewriting does not require that the GROUP BY columns of the query and the materialized view be exactly the same. Rewriting can be performed in the following scenarios:

  • Ensure that the GROUP BY columns of the materialized view and query are match. If this condition is satisfied, the rewriting can proceed even if the sequence is different.
  • If the materialized view does not have the GROUP BY column but the query does, add the GROUP BY column to the materialized view for rewriting.
  • When both the materialized view and query have the GROUP BY column, and the query's column is a subset of the materialized view, the GROUP BY column can be rewritten by aggregating the data. Aggregate functions like min, max, count, and sum are adjusted accordingly.
  • If the materialized view's grouping by column does not include grouping sets, rollup, and cube, but the query involves these, the query can still be executed.

Aggregation rewriting examples:

Create the sample table t3.

1
 
CREATE TABLE t3(a int, b int, c int);

Scenario 1: The GROUP BY columns of the query and the materialized view are fully matched.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
create materialized view mv1_t3 enable query rewrite as select a,b,sum(c) from t3 group by a,b;
explain (costs off, verbose on, nodes off) select sum(c) from t3 group by b,a;
                      QUERY PLAN                       
-------------------------------------------------------
  id |            operation             
 ----+----------------------------------
   1 | ->  Streaming (type: GATHER)     
   2 |    ->  Seq Scan on public.mv1_t3 

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: sum
   2 --Seq Scan on public.mv1_t3
         Output: sum

Scenario 2: The materialized view does not have the GROUP BY column, but the query does.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
create materialized view mv2_t3 enable query rewrite as select a,b,c from t3
explain (costs off, verbose on, nodes off) select sum(a) from t3 group by b,c;
                      QUERY PLAN                       
-------------------------------------------------------
  id |               operation                
 ----+----------------------------------------
   1 | ->  HashAggregate                      
   2 |    ->  Streaming (type: GATHER)        
   3 |       ->  HashAggregate                
   4 |          ->  Seq Scan on public.mv2_t3 

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --HashAggregate
         Output: pg_catalog.sum((sum(a))), b, c
         Group By Key: mv2_t3.b, mv2_t3.c
   2 --Streaming (type: GATHER)
         Output: (sum(a)), b, c
   3 --HashAggregate
         Output: sum(a), b, c
         Group By Key: mv2_t3.b, mv2_t3.c
   4 --Seq Scan on public.mv2_t3
         Output: b, c, a

Scenario 3: The GROUP BY column of the query is a subset of the GROUP BY column of the materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
create materialized view mv3_t3 enable query rewrite as select sum(b),min(b),max(b),count(b),a,c from t3 group by a,c;
explain (costs off, verbose on, nodes off) select sum(b),min(b),max(b),count(b),avg(b) from t3 group by a;
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
  id |                operation                
 ----+-----------------------------------------
   1 | ->  Streaming (type: GATHER)            
   2 |    ->  HashAggregate                    
   3 |       ->  Streaming(type: REDISTRIBUTE) 
   4 |          ->  Seq Scan on public.mv3_t3  

                                                        Targetlist Information (identified by plan id)                                                       
 ------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: ((sum(sum))::bigint), (min(min)), (max(max)), ((sum(count))::bigint), ((((sum(sum))::bigint)::numeric / ((sum(count))::bigint)::numeric)), a
   2 --HashAggregate
         Output: (sum(sum))::bigint, min(min), max(max), (sum(count))::bigint, (((sum(sum))::bigint)::numeric / ((sum(count))::bigint)::numeric), a
         Group By Key: mv3_t3.a
   3 --Streaming(type: REDISTRIBUTE)
         Output: a, sum, min, max, count
         Distribute Key: a
   4 --Seq Scan on public.mv3_t3
         Output: a, sum, min, max, count

JOIN Rewriting

When multiple tables are associated between the query and the materialized view, the tables must be in one-to-one mapping in addition to other rewriting conditions. In addition, the restrictions on the association sequence, association type, and association conditions must be met in the JOIN part.

Multi-table association rewriting can be performed in the following scenarios:

  • If the association is an inner join, the restriction on the association sequence does not need to be met. If the two columns in the materialized view are equivalence classes, they must also be equivalence classes in the query.

    Assume that there is a condition where t1.a=t2.a in the query, t1.a and t2.a are considered equivalent. If t2.a=t3.a is also included, then t2.a and t3.a are considered equivalent. Ultimately, t1.a, t2.a, and t3.a are all equivalent, forming an equivalence class.

  • If the outer joins of the query and the materialized view are of the same type, the sequence of the inner and foreign tables in the materialized view must match the outer join sequence in the query. The association conditions and the sides where null may be padded must also be the same.
  • If the connection types are different, the conditions for derived rewriting must be met, as shown in Table 1. Furthermore, the positions of different join types cannot be the side where null may be padded.
  • If the number of tables is different, the tables involved in the query are the subsets of the tables in the materialized view, and the extra tables in the materialized view can be deleted through the primary and foreign key association, rewriting can be performed.
Table 1 Derived rewriting description for different JOIN operations

Query Join Type

Materialized View Join Type

Compensation Predicate

INNER JOIN

LEFT JOIN

Add is not null to the column in the right table of LEFT JOIN.

ANTI JOIN

LEFT JOIN

Add is null to the column in the right table of LEFT JOIN.

INNER JOIN

RIGHT JOIN

Add is not null to the column in the left table of RIGHT JOIN.

RIGHT ANTI JOIN

RIGHT JOIN

Add is null to the column in the left table of RIGHT JOIN.

SEMI JOIN

INNER JOIN

The primary key or unique key is directly converted, and no compensation predicate needs to be added.

RIGHT SEMI JOIN

INNER JOIN

The primary key or unique key is directly converted, and no compensation predicate needs to be added.

LEFT JOIN

FULL JOIN

Add is not null to the column in the left table of FULL JOIN.

RIGHT JOIN

FULL JOIN

Add is not null to the column in the right table of FULL JOIN.

INNER JOIN

FULL JOIN

Add is not null to the column in the tables on both sides of FULL JOIN.

JOIN rewriting examples:

Create base tables join_t1 and join_t2.

create table join_t1 (a1 int, b1 int, c1 int);
create table join_t2 (a2 int, b2 int, c2 int);
Scenario 1: Both the materialized view and query use inner joins. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
create materialized view mv_join_01 enable query rewrite as select * from join_t1 join join_t2 on join_t1.a1 = join_t2.a2;
explain (costs off, verbose on, nodes off) select sum(a1), b1 + c1 from join_t1 join join_t2 on join_t1.a1 = join_t2.a2 and join_t1.b1 = join_t2.b2 and join_t1.c1 = join_t2.c2 where c1 > 10 group by b1,c1;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
  id |                 operation                  
 ----+--------------------------------------------
   1 | ->  HashAggregate                          
   2 |    ->  Streaming (type: GATHER)            
   3 |       ->  HashAggregate                    
   4 |          ->  Seq Scan on public.mv_join_01 

                                  Predicate Information (identified by plan id)                                 
 ---------------------------------------------------------------------------------------------------------------
   4 --Seq Scan on public.mv_join_01
         Filter: ((mv_join_01.c1 >= 11) AND (mv_join_01.b1 = mv_join_01.b2) AND (mv_join_01.c1 = mv_join_01.c2))

         Targetlist Information (identified by plan id)        
 --------------------------------------------------------------
   1 --HashAggregate
         Output: pg_catalog.sum((sum(a1))), ((b1 + c1)), b1, c1
         Group By Key: mv_join_01.b1, mv_join_01.c1
   2 --Streaming (type: GATHER)
         Output: (sum(a1)), ((b1 + c1)), b1, c1
   3 --HashAggregate
         Output: sum(a1), (b1 + c1), b1, c1
         Group By Key: mv_join_01.b1, mv_join_01.c1
   4 --Seq Scan on public.mv_join_01
         Output: b1, c1, a1, b2, c2

Scenario 2: The materialized view and query have outer joins.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
create materialized view mv_join_02 enable query rewrite as select * from join_t1 left join join_t2 on join_t1.a1 = join_t2.a2;
explain (costs off, verbose on, nodes off) select sum(a1), b1 + c1 from join_t1 left join join_t2 on join_t1.a1 = join_t2.a2 where join_t1.b1 = join_t2.b2 and join_t1.c1 = join_t2.c2 and c1 > 10 group by b1,c1;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
  id |                 operation                  
 ----+--------------------------------------------
   1 | ->  HashAggregate                          
   2 |    ->  Streaming (type: GATHER)            
   3 |       ->  HashAggregate                    
   4 |          ->  Seq Scan on public.mv_join_02 

                                  Predicate Information (identified by plan id)                                 
 ---------------------------------------------------------------------------------------------------------------
   4 --Seq Scan on public.mv_join_02
         Filter: ((mv_join_02.c1 >= 11) AND (mv_join_02.b1 = mv_join_02.b2) AND (mv_join_02.c1 = mv_join_02.c2))

         Targetlist Information (identified by plan id)        
 --------------------------------------------------------------
   1 --HashAggregate
         Output: pg_catalog.sum((sum(a1))), ((b1 + c1)), b1, c1
         Group By Key: mv_join_02.b1, mv_join_02.c1
   2 --Streaming (type: GATHER)
         Output: (sum(a1)), ((b1 + c1)), b1, c1
   3 --HashAggregate
         Output: sum(a1), (b1 + c1), b1, c1
         Group By Key: mv_join_02.b1, mv_join_02.c1
   4 --Seq Scan on public.mv_join_02
         Output: b1, c1, a1, b2, c2

Scenario 3: The association types of the materialized view and query are different.

The materialized view uses FULL JOIN and the query uses RIGHT JOIN. The rewriting is successful by adding is not null to the right table.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
create materialized view mv_join_03 enable query rewrite as select * from join_t1 full join join_t2 on join_t1.a1 = join_t2.a2;
explain (costs off, verbose on, nodes off) select sum(a1), b1 + c1 from join_t1 right join join_t2 on join_t1.a1 = join_t2.a2 where join_t1.b1 = join_t2.b2 and join_t1.c1 = join_t2.c2 group by b1,c1;
                                                       QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
  id |                 operation                  
 ----+--------------------------------------------
   1 | ->  HashAggregate                          
   2 |    ->  Streaming (type: GATHER)            
   3 |       ->  HashAggregate                    
   4 |          ->  Seq Scan on public.mv_join_03 

                                     Predicate Information (identified by plan id)                                    
 ---------------------------------------------------------------------------------------------------------------------
   4 --Seq Scan on public.mv_join_03
         Filter: ((mv_join_03.a2 IS NOT NULL) AND (mv_join_03.b1 = mv_join_03.b2) AND (mv_join_03.c1 = mv_join_03.c2))

         Targetlist Information (identified by plan id)        
 --------------------------------------------------------------
   1 --HashAggregate
         Output: pg_catalog.sum((sum(a1))), ((b1 + c1)), b1, c1
         Group By Key: mv_join_03.b1, mv_join_03.c1
   2 --Streaming (type: GATHER)
         Output: (sum(a1)), ((b1 + c1)), b1, c1
   3 --HashAggregate
         Output: sum(a1), (b1 + c1), b1, c1
         Group By Key: mv_join_03.b1, mv_join_03.c1
   4 --Seq Scan on public.mv_join_03
         Output: b1, c1, a1, b2, c2

Scenario 4: The queried table is a subset of the materialized view table, and the association condition in the materialized view satisfies both the primary and foreign key relationships.

In practical terms, a wide table is one that links service-related KQIs/KPIs, dimensions, and attributes. Similar to a wide table, a materialized view can be created based on the association of multiple tables. Service query involves only several tables. After the query is rewritten using the materialized view, a multi-table association query can achieve the same outcome as querying the corresponding wide table. It is important to note that foreign key constraints are not validated during data import. Therefore, the service must ensure that the imported data adheres to the primary and foreign key constraints before utilizing materialized view rewriting in this scenario.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 
set info_constraint_options = 'foreign_key';
alter table join_t1 add constraint pkey_join_t1 primary key(a1);
alter table join_t2 add constraint fkey_join_t2 foreign key(a2) references join_t1(a1);
alter table join_t2 modify a2 not null;
explain (costs off, verbose on, nodes off) select a2 from join_t2;
                      QUERY PLAN                       
-------------------------------------------------------
  id |              operation               
 ----+--------------------------------------
   1 | ->  Streaming (type: GATHER)         
   2 |    ->  Seq Scan on public.mv_join_01 

 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Output: a1
   2 --Seq Scan on public.mv_join_01
         Output: a1

Using HINT to Specify a Materialized View for Rewriting

You can use hint mvrewrite to specify the name of a materialized view for rewriting. You can add multiple table names or schema.table-name after mvrewrite.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
create table t1(a int,b int);
create materialized view hintmv1 ENABLE QUERY REWRITE as select * from t1 where a > 1;
create materialized view hintmv2 ENABLE QUERY REWRITE as select * from t1 where a > 2;

explain (verbose on, costs off, nodes off) select /*+ mvrewrite (hintmv2) */ * from t1 where a>2;
                QUERY PLAN
-------------------------------------------
 Streaming (type: GATHER)
   Output: a, b, c
   ->  Seq Scan on public.hintmv2
         Output: a, b, c
         Distribute Key: a
(5 rows)
Parent Topic: Query Rewriting