Query rewrite for materialized views - AnalyticDB for PostgreSQL

Features

Before query rewrite is supported, SELECT query statements must be manually modified to specify the use of materialized views. After query rewrite is supported, SELECT query statements can be automatically rewritten to use materialized views, even if the SELECT query statements reference base tables but not materialized views. This accelerates the execution of SELECT query statements.

If a SELECT query statement is completely identical to the SELECT statement in a CREATE MATERIALIZED VIEW statement, AnalyticDB for PostgreSQL triggers query rewrite to accelerate the query by using data in the materialized view. For more information, see the "Complete match" section of this topic.
If a SELECT query statement is partially identical to the SELECT statement in a CREATE MATERIALIZED VIEW statement, query rewrite supplements the SELECT statement in the CREATE MATERIALIZED VIEW statement. For more information, see the Query supplement" section of this topic.

Limits

Query rewrite is not supported for the SELECT FOR UPDATE statement.
Query rewrite is not supported for statements that contain recursive CTEs.
Query rewrite is not supported for queries that contain random functions such as RANDOM() and NOW().
If a SELECT query statement is partially identical to the SELECT statement in a CREATE MATERIALIZED VIEW statement but the requirements for query supplement are not met, query rewrite is not supported. For more information about query supplement, see the "Query supplement" section of this topic.
Query rewrite is supported only when the minor version of AnalyticDB for PostgreSQL is V6.3.6.0 or later.
Note
- For more information about how to view the minor version of an instance, see Query the minor engine version.
- For more information about how to update the minor version of an instance, see Upgrade the engine version.

Enable or disable query rewrite

Real-time materialized views
By default, query rewrite is enabled for real-time materialized views. You can execute the following statement to disable the feature:
```
SET enable_incremental_matview_query_rewrite TO off;
```
Standard materialized views
By default, query rewrite is disabled for standard materialized views. You can execute the following statement to enable the feature:
```
SET enable_matview_query_rewrite TO on;
```

Note You cannot enable or disable this feature for specific instances. To enable or disable query rewrite for a specific instance, Submit a ticket.

Complete match

In AnalyticDB for PostgreSQL, query rewrite checks whether the syntax tree is a complete match between a SELECT query statement and the SELECT statement in a CREATE MATERIALIZED VIEW statement regardless of spaces, line breaks, comments, or aliases. If a SELECT query statement is completely identical to the SELECT statement in a CREATE MATERIALIZED VIEW statement, the materialized view is preferentially used to accelerate queries.

Query supplement

In AnalyticDB for PostgreSQL, query rewrite can be used if a SELECT query statement is partially identical to the SELECT statement in a CREATE MATERIALIZED VIEW statement. In this scenario, query rewrite supplements the SELECT statement in the CREATE MATERIALIZED VIEW statement and returns query results based on the materialized view.

Query supplement is supported only for the following parts in a SELECT query statement: SELECT columns, JOIN tables, GROUP BY columns, WHERE clause, HAVING clause, ORDER BY columns, and LIMIT clause. To meet the requirements for query rewrite, make sure that other parts in a SELECT query statement are completely identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement.

SELECT columns
When the SELECT columns in a SELECT query statement are partially identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:
- Query rewrite is supported if the order of SELECT columns in a SELECT query statement is different from that in the SELECT statement of a CREATE MATERIALIZED VIEW statement.
- Query rewrite is supported if the SELECT columns in the SELECT statement of a CREATE MATERIALIZED VIEW statement are not included in a SELECT query statement.
- Query rewrite is supported if the SELECT columns in a SELECT query statement are not included in the SELECT statement of a CREATE MATERIALIZED VIEW statement but can be calculated from those in the SELECT statement of the CREATE MATERIALIZED VIEW statement.
- Query rewrite is not supported if the SELECT columns in a SELECT query statement are not included in the SELECT statement of a CREATE MATERIALIZED VIEW statement and cannot be calculated from those in the SELECT statement of the CREATE MATERIALIZED VIEW statement.
GROUP BY columns
When the GROUP BY columns in a SELECT query statement are partially identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:
- The SELECT statement in a CREATE MATERIALIZED VIEW statement does not include GROUP BY columns or aggregate functions:
  - Query rewrite is supported if a SELECT query statement includes aggregate functions.
  - Query rewrite is supported if a SELECT query statement includes GROUP BY columns.
  - Query rewrite is supported if a SELECT query statement includes GROUP BY columns and aggregate functions.
- The SELECT statement in a CREATE MATERIALIZED VIEW statement includes GROUP BY columns but not aggregate functions:
  - Query rewrite is supported if the GROUP BY columns in the SELECT statement of a CREATE MATERIALIZED VIEW statement are not included in a SELECT query statement.
  - Query rewrite is not supported if the GROUP BY columns in a SELECT query statement are not included in the SELECT statement of a CREATE MATERIALIZED VIEW statement.
  - Query rewrite is supported if the aggregate function in a SELECT query statement is count(distinct).
- The SELECT statement in a CREATE MATERIALIZED VIEW statement does not include GROUP BY columns but includes aggregate functions:
  - Query rewrite is not supported if a SELECT query statement includes GROUP BY columns.
- The SELECT statement in a CREATE MATERIALIZED VIEW statement includes GROUP BY columns and aggregate functions:
  - Query rewrite is supported if the GROUP BY columns in the SELECT statement of a CREATE MATERIALIZED VIEW statement are not included in a SELECT query statement.
  - Query rewrite is not supported if the GROUP BY columns in a SELECT query statement are not included in the SELECT statement of a CREATE MATERIALIZED VIEW statement.
Note
- If a SELECT query statement includes fewer GROUP BY columns than the SELECT statement in a CREATE MATERIALIZED VIEW statement, query rewrite supplements the SELECT statement in the CREATE MATERIALIZED VIEW statement by performing re-aggregation on aggregate functions. The following aggregate functions are supported for re-aggregation: COUNT, SUM, MAX, MIN, and AVG. Query rewrite is not supported if a SELECT query statement includes other aggregate functions.
- If a SELECT query statement includes a HAVING clause, GROUP BY columns cannot be used for supplement.
JOIN tables
When the JOIN tables or conditions in a SELECT query statement are partially identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:
- INNER JOIN tables can be interchanged, and additional JOIN tables or conditions can be used to supplement the SELECT statement in a CREATE MATERIALIZED VIEW statement.
- LEFT OUTER JOIN and RIGHT OUTER JOIN can be converted to each other, and the left and right tables of FULL OUTER JOIN can be interchanged. Additional JOIN tables or conditions cannot be used to supplement the SELECT statement in a CREATE MATERIALIZED VIEW statement.
When the JOIN tables in a SELECT query statement are completely identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:
- INNER JOIN tables can be interchanged. Examples:
  - SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM a, b WHERE a.i = b.i;
```
    SELECT query statements that support query rewrite:
```
SELECT * FROM b, a WHERE a.i = b.i;
SELECT * FROM a INNER JOIN b ON a.i = b.i;
```
  - SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM a INNER JOIN b ON a.i = b.i;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM b INNER JOIN a ON a.i = b.i;
```
- LEFT OUTER JOIN and RIGHT OUTER JOIN can be converted to each other. Examples:
  SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM a LEFT JOIN b ON a.i = b.i;
```
  SELECT query statement that supports query rewrite:
```
SELECT * FROM b RIGHT JOIN a ON b.i = a.i;
```
- The left and right tables of FULL OUTER JOIN can be interchanged. Examples:
  SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM a FULL OUTER JOIN b ON a.i = b.i;
```
  SELECT query statement that supports query rewrite:
```
SELECT * FROM b FULL OUTER JOIN a ON b.i = a.i;
```
When the JOIN tables in a SELECT query statement are partially identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:

Additional INNER JOIN tables can be supplemented. In this scenario, INNER JOIN or COMMON JOIN tables can be interchanged, and INNER JOIN and COMMON JOIN can be converted to each other. Examples:
- SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM a, b;
```
  SELECT query statement that supports query rewrite:
```
SELECT * FROM a, b, c;
```
- SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM a INNER JOIN b ON a.i = b.i;
```
  SELECT query statement that supports query rewrite:
```
SELECT * FROM a INNER JOIN b ON a.i = b.i INNER JOIN c ON a.i = c.i;
```
WHERE clause
When the WHERE clause in a SELECT query statement is partially identical to that in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:
- Both a SELECT query statement and the SELECT statement in a CREATE MATERIALIZED VIEW statement use AND to join multiple WHERE conditions:
  - Query rewrite is supported if the order of WHERE conditions in a SELECT query statement is different from that in the SELECT statement of a CREATE MATERIALIZED VIEW statement. Examples:
    SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM t WHERE a > 100 AND a < 200;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM t WHERE a < 200 AND a > 100;
```
  - Query rewrite is supported if a WHERE clause in a SELECT query statement is not included in the SELECT statement of a CREATE MATERIALIZED VIEW statement. In this scenario, query rewrite supplements the missing WHERE clause. The columns referenced in the WHERE clause to be supplemented must exist in the materialized view. Examples:
    SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM t WHERE a > 100;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM t WHERE b > 200 AND a > 100;
```
- Both a SELECT query statement and the SELECT statement in a CREATE MATERIALIZED VIEW statement use OR to join multiple WHERE conditions:
  - Query rewrite is supported if the order of WHERE conditions in a SELECT query statement is different from that in the SELECT statement of a CREATE MATERIALIZED VIEW statement. The columns referenced in all WHERE clauses must exist in the materialized view. Examples:
    SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM t WHERE a > 100 OR a < 200;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM t WHERE a < 200 OR a > 100;
```
  - Query rewrite is supported if a WHERE clause in a SELECT query statement is not included in the SELECT statement of a CREATE MATERIALIZED VIEW statement. The columns referenced in all WHERE clauses must exist in the materialized view. Examples:
    SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM t WHERE a > 100 OR a < 200;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM t WHERE a < 200;
```
- The WHERE clause in the SELECT statement of a CREATE MATERIALIZED VIEW statement includes that in a SELECT query statement:
  - Query rewrite is supported if the WHERE clause in a SELECT query statement consists of an equality condition but a range is specified in the WHERE clause in the SELECT statement of a CREATE MATERIALIZED VIEW statement. The columns referenced in all WHERE clauses must exist in the materialized view. Examples:
    SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM t WHERE a < 200 AND a >= 100;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM t WHERE a = 102;
```
  - Query rewrite is supported if a range is specified in the WHERE clauses of both a SELECT query statement and the SELECT statement in a CREATE MATERIALIZED VIEW statement. The columns referenced in all WHERE clauses must exist in the materialized view. Examples:
    SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT * FROM t WHERE a < 200 AND a >= 0;
```
    SELECT query statement that supports query rewrite:
```
SELECT * FROM t WHERE a <= 100 AND a > 50;
```
HAVING clause
When the HAVING clause in a SELECT query statement is partially identical to that in the SELECT statement of a CREATE MATERIALIZED VIEW statement, the following rules apply to query rewrite:
- If GROUP BY columns do not need to be supplemented, query rewrite supplements the HAVING clause in a manner similar to when it supplements the WHERE clause. In the SELECT statement of a CREATE MATERIALIZED VIEW statement, missing AND conditions can be supplemented, additional OR conditions can be removed, and the range can be narrowed down.
- If GROUP BY columns need to be supplemented, query rewrite is supported in the scenario where a SELECT query statement includes a HAVING clause but the SELECT statement in a CREATE MATERIALIZED VIEW statement does not.
ORDER BY columns
Regardless of whether the SELECT statement in a CREATE MATERIALIZED VIEW statement includes ORDER BY columns, query rewrite attempts to supplement ORDER BY columns. To meet the requirements for query rewrite, make sure that ORDER BY columns of a SELECT query statement are included in those of the SELECT statement in a CREATE MATERIALIZED VIEW statement.
LIMIT clause
If the SELECT statement in a CREATE MATERIALIZED VIEW statement does not include a LIMIT clause, query rewrite supplements the LIMIT clause. If the SELECT statement in a CREATE MATERIALIZED VIEW statement includes a LIMIT clause, the SELECT query statement must be completely identical to the SELECT statement in the CREATE MATERIALIZED VIEW statement.
Expression supplement
If the ordinary expression or aggregate function expression in a SELECT query statement does not match the expression in the SELECT statement of a CREATE MATERIALIZED VIEW statement, sub-expressions in the SELECT query statement are used from the top down to find the closest match. Examples:

SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT a+b, c FROM t;
```
SELECT query statements that support query rewrite:
```
SELECT a+b, (a+b)+c, mod(a+b, c) FROM t;
SELECT sum((a+b)*c) FROM t;
```
If aggregate function expressions are included, the following rules apply to query rewrite:
- Aggregate functions SUM() and COUNT() in the SELECT statement of a CREATE MATERIALIZED VIEW statement can be calculated into an AVG() aggregate function.
- Aggregate functions COUNT(*) and COUNT(1) can be interchanged between a SELECT query statement and the SELECT statement in a CREATE MATERIALIZED VIEW statement.
Aggregate function expression examples:

SELECT statement in a CREATE MATERIALIZED VIEW statement:
```
SELECT sum(a), count(a), count(*) FROM t;
```
SELECT query statement that supports query rewrite:
```
SELECT avg(a), count(1) FROM;
```

CTEs and subqueries

If CTEs and subqueries are included, the following rules apply to query rewrite based on primary queries and subqueries. A CTE in a WITH clause is equivalent to a subquery.

A SELECT query statement includes only a single subquery:
- If the primary query and subquery in a SELECT query statement are completely identical to those in the SELECT statement of a CREATE MATERIALIZED VIEW statement, query rewrite replaces the SELECT statement of the CREATE MATERIALIZED VIEW statement by using the complete match method.
- If the subquery in a SELECT query statement is completely identical to that in the SELECT statement of a CREATE MATERIALIZED VIEW statement but the primary query is different, query rewrite supplements the SELECT statement of the CREATE MATERIALIZED VIEW statement. For more information about query supplement, see the "Query supplement" section of this topic.
- A SELECT query statement includes a subquery but the SELECT statement in a CREATE MATERIALIZED VIEW statement does not:
  - If the SELECT statement in a CREATE MATERIALIZED VIEW statement is identical to the primary query in a SELECT query statement or can be supplemented, query rewrite supplements a subquery to the SELECT statement of the CREATE MATERIALIZED VIEW statement. Associated subqueries cannot be supplemented.
  - If the SELECT statement of a CREATE MATERIALIZED VIEW statement is identical to the subquery in a SELECT query statement or can be supplemented, query rewrite replaces the subquery in the SELECT query statement.
  - If the primary query or subquery in a SELECT query statement is replaced with a materialized view, query rewrite continues to replace other parts of the SELECT query statement.
- Query rewrite does not support replacement for recursive CTEs.
A SELECT query statement includes multiple subqueries:
If the primary query or a subquery in a SELECT query statement is rewritten, query rewrite continues to rewrite other parts of the SELECT query statement based on the preceding rules.

UNION, EXCEPT, and INTERSECT

If both a SELECT query statement and the SELECT statement in a CREATE MATERIALIZED VIEW statement include UNION, EXCEPT, or INTERSECT, two queries before and after UNION or INTERSECT can be interchanged, and the UNION or INTERSECT clause can be supplemented. The EXCEPT clause can be supplemented, but two queries before and after EXCEPT cannot be interchanged.
If a SELECT query statement includes UNION, EXCEPT, or INTERSECT but the SELECT statement in a CREATE MATERIALIZED VIEW statement does not, a UNION, EXCEPT, or INTERSECT clause can be supplemented to join multiple materialized views.

Match multiple materialized views

If a SELECT query statement matches multiple materialized views, query rewrite selects materialized views based on the following rules:

The materialized view that completely matches the SELECT query statement is preferentially selected. If this materialized view does not exist, a materialized view that can be supplemented is selected.
If multiple materialized views that can be supplemented exist, the materialized view that matches the most tables with those of the SELECT query statement is preferentially selected.
If multiple materialized views that can be supplemented reference the same number of tables as the SELECT query statement, the materialized view that references the least data is preferentially selected.

Examples

Example 1:

Execute the following statement to create a base table:
```
CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);
```

Execute the following statement to insert data to the base table:

INSERT INTO t1 VALUES (generate_series(1, 10), generate_series(1, 2));

Execute the following statement to create a materialized view:

CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT count(a), b FROM t1 GROUP BY b DISTRIBUTED BY (b);

Execute the following statement to run a query plan:

EXPLAIN SELECT count(a), b FROM t1 GROUP BY b;

The following result is returned. Query rewrite uses the complete match method to replace the SELECT statement in the CREATE MATERIALIZED VIEW statement and returns the data of the materialized view mv.

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.02 rows=2 width=12)
   ->  Seq Scan on mv  (cost=0.00..2.02 rows=1 width=12)
 Optimizer: Postgres query optimizer
(3 rows)

Example 2:

Execute the following statements to create two base tables:

CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);
CREATE TABLE t2 (i int, j int) DISTRIBUTED BY (i);

Execute the following statements to insert data to the base tables:

INSERT INTO t1 VALUES (generate_series(1, 10), generate_series(1, 2));
INSERT INTO t2 VALUES (generate_series(1, 10), generate_series(1, 2));

Execute the following statement to create a materialized view:

CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT count(a), a, b FROM t1 GROUP BY a, b DISTRIBUTED BY (a);

Execute the following statement to run a query plan:

EXPLAIN SELECT count(a) FROM t1 JOIN t2 ON t1.a = t2.i WHERE b > 3 GROUP BY a;

The following result is returned. Query rewrite returns the data of the materialized view mv after supplementing the JOIN and WHERE clauses and removing the GROUP BY clause.

                                            QUERY PLAN

----------------------------------------------------------------------------------
----------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..437.00 rows=1 width=8)
   ->  Result  (cost=0.00..437.00 rows=1 width=8)
         ->  GroupAggregate  (cost=0.00..437.00 rows=1 width=8)
               Group Key: mv.a
               ->  Sort  (cost=0.00..437.00 rows=1 width=12)
                     Sort Key: mv.a
                     ->  Hash Join  (cost=0.00..437.00 rows=1 width=12)
                           Hash Cond: (mv.a = t2.i)
                           ->  Index Scan using mv_index on mv  (cost=0.00..6.00 r
ows=1 width=12)
                                 Index Cond: (b > 3)
                           ->  Hash  (cost=431.00..431.00 rows=4 width=4)
                                 ->  Seq Scan on t2  (cost=0.00..431.00 rows=4 wid
th=4)
 Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
(13 rows)