物化视图自动查询改写

功能介绍

早期使用普通物化视图和实时物化视图时，通常需要人工修改查询SQL，显式在SQL中指定使用物化视图。支持自动查询修改功能后，物化视图自动查询改写功能将自动、透明的重写查询SQL，使查询SQL可以使用物化视图（即使查询SQL本身指定的是查询基表而非物化视图），从而加速查询SQL的运行。

• 如果查询SQL与物化视图SQL使用的SELECT语句完全相同（全匹配），AnalyticDB PostgreSQL版就会触发自动查询改写功能，从而使用物化视图中的数据加速查询。更多关于全匹配的介绍，请参见全匹配。
• 如果查询SQL与物化视图SQL使用的SELECT语句不完全相同，但是物化视图SQL部分命中查询SQL的场景，自动查询改写会尝试通过对物化视图进行补偿操作完成查询，具体补偿能力的支持，请参见查询补偿。

使用限制

• SELECT FOR UPDATE语句不会参与自动查询改写。
• 包含递归CTE的语句不会参与自动查询改写。
• 包含随机函数的查询（例如random()，now()等）不会参与自动查询改写。
• 当查询SQL与物化视图SQL不完全相同，且不在本文中说明的查询补偿支持的情况范围内的场景，不支持自动查询改写。关于查询补偿相关信息，请参见查询补偿。
• 内核小版本为V6.3.6.0及以上版本。
  说明

  • 查看实例的内核版本，请参见查看内核小版本。
  • 升级实例的内核版本，请参见版本升级。

开启或关闭自动查询改写

• 实时物化视图

  默认情况下，实时物化视图的自动查询改写功能为开启状态，您可以通过以下命令关闭该功能。

  SET enable_incremental_matview_query_rewrite TO off;

• 普通物化视图

  默认情况下，普通物化视图的自动查询改写功能为关闭状态，您可以通过以下命令开启该功能。

  SET enable_matview_query_rewrite TO on;

全匹配

AnalyticDB PostgreSQL版自动查询改写是基于语法树的匹配。全匹配会自动忽略空格、换行、注释、别名等影响。只要查询SQL和物化视图SQL在语法层面完全相同，就会优先使用物化视图，从而加速查询。

查询补偿

AnalyticDB PostgreSQL版自动查询改写支持查询SQL与物化视图SQL不完全相同的场景。这种场景下，自动查询改写会尝试改写查询SQL，补偿物化视图SQL和查询SQL之间缺少的计算动作，并返回最终结果以保证查询的正确性。

目前查询SQL中仅SELECT列、JOIN表、GROUP BY列、WHERE子句、HAVING子句、ORDER BY列、LIMIT子句支持查询补偿，除了上述部分，SQL中其它部分需要和物化视图SQL完全相同，否则不会发生自动查询改写。

• SELECT列

  当SQL查询的SELECT列与物化视图SQL的SELECT列不完全相同时，自动查询改写对SELECT列支持情况如下：

  • 支持查询SQL的SELECT列的顺序与物化视图SQL不同。
  • 支持物化视图SQL中的SELECT列没有出现在查询SQL中。
  • 支持查询SQL中的SELECT列不在物化视图SQL的SELECT列中，但是可以由物化视图SQL的SELECT列组合计算得到。
  • 不支持查询SQL中的SELECT列不在物化视图SQL的SELECT列中，且无法由物化视图SQL的SELECT列组合计算得到。
• GROUP BY列

  当SQL查询的GROUP BY列与物化视图SQL不完全相同时，自动查询改写对GROUP BY列的支持情况如下：

  • 如果物化视图SQL不包含GROUP BY和聚合函数：
    • 支持查询SQL包含聚合函数。
    • 支持查询SQL包含GROUP BY。
    • 支持查询SQL包含GROUP BY和聚合函数。
  • 如果物化视图SQL包含GROUP BY但是不包含聚合函数：
    • 支持物化视图SQL的GROUP BY列不在查询SQL中。
    • 不支持查询SQL的GROUP BY列不在物化视图SQL中。
    • 查询SQL仅支持count(distinct)聚合函数，其他聚合函数均不支持。
  • 如果物化视图SQL不包含GROUP BY但是包含聚合函数：
    • 不支持查询SQL包含GROUP BY列。
  • 如果物化视图SQL包含GROUP BY列和聚合函数：
    • 支持物化视图SQL的GROUP BY列不在查询SQL中。
    • 不支持查询SQL的GROUP BY列不在物化视图SQL中。
  说明

  • 当查询SQL的GROUP BY列比物化视图SQL少时，自动查询改写需要对聚合函数进行重聚和补偿，重聚和支持的聚合函数为count、sum、max、min、avg，当查询SQL包含其他聚合函数时，则不会发生自动查询改写。
  • 当查询SQL包含HAVING子句时，GROUP BY列不支持补偿。
• JOIN表

  当查询SQL的JOIN表或JOIN条件与物化视图SQL不完全相同时，自动查询改写对JOIN表的支持情况如下：

  • 对于INNER JOIN，支持表交换顺序，支持补偿额外的JOIN表与补偿额外的JOIN条件。
  • 对于OUTER JOIN，支持LEFT JOIN与RIGHT JOIN互相转换，支持FULL JOIN的左右表交换，不支持补偿额外的表与JOIN条件。

  当查询SQL的JOIN表与物化视图SQL相同时：

  • 支持INNER JOIN表的顺序交换，示例如下：
    • 物化视图SQL的SELECT部分如下：

      SELECT * FROM a, b WHERE a.i = b.i;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM b, a WHERE a.i = b.i;
      SELECT * FROM a INNER JOIN b ON a.i = b.i;

    • 物化视图SQL的SELECT部分如下：

      SELECT * FROM a INNER JOIN b ON a.i = b.i;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM b INNER JOIN a ON a.i = b.i;

  • 支持LEFT JOIN与RIGHT JOIN互相转换，示例如下：

    物化视图SQL的SELECT部分如下：

    SELECT * FROM a LEFT JOIN b ON a.i = b.i;

    可以被自动查询改写的查询SQL如下：

    SELECT * FROM b RIGHT JOIN a ON b.i = a.i;

  • 支持FULL OUTER JOIN表左右顺序交换，示例如下：

    物化视图SQL的SELECT部分如下：

    SELECT * FROM a FULL OUTER JOIN b ON a.i = b.i;

    可以被自动查询改写的查询SQL如下：

    SELECT * FROM b FULL OUTER JOIN a ON b.i = a.i;

  当查询SQL的JOIN表与物化视图SQL不完全相同时：

  支持补偿额外的INNER JOIN表。此时支持INNER JOIN与COMMON JOIN表的顺序交换，以及两者之间的转换。示例如下：

  • 物化视图SQL的SELECT部分如下：

    SELECT * FROM a, b;

    可以被自动查询改写的查询SQL如下：

    SELECT * FROM a, b, c;

  • 物化视图SQL的SELECT部分如下：

    SELECT * FROM a INNER JOIN b ON a.i = b.i;

    可以被自动查询改写的查询SQL如下：

    SELECT * FROM a INNER JOIN b ON a.i = b.i INNER JOIN c ON a.i = c.i;

• WHERE子句

  当查询SQL的WHERE子句与物化视图SQL不完全相同时，自动查询改写对WHERE子句支持情况如下：

  • 当查询SQL和物化视图SQL的WHERE都是由AND连接多个条件时：
    • 支持查询SQL的WHERE的多个条件的顺序与物化视图SQL的不同，示例如下：

      物化视图SQL的SELECT部分如下：

      SELECT * FROM t WHERE a > 100 AND a < 200;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM t WHERE a < 200 AND a > 100;

    • 支持查询SQL的WHERE子句没有出现在物化视图SQL的WHERE子句中，该场景自动查询改写会对物化视图中缺少的WHERE子句进行补偿，但是需要补偿的WHERE子句所涉及的列必须在物化视图中存在。示例如下：

      物化视图SQL的SELECT部分如下：

      SELECT * FROM t WHERE a > 100;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM t WHERE b > 200 AND a > 100;

  • 当查询SQL和物化视图SQL的WHERE都是由OR连接多个条件时：
    • 支持查询SQL的WHERE的多个条件的顺序与物化视图SQL不同，但是查询SQL中所有WHERE子句涉及的列都必须在物化视图中存在。示例如下：

      物化视图SQL的SELECT部分如下：

      SELECT * FROM t WHERE a > 100 OR a < 200;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM t WHERE a < 200 OR a > 100;

    • 支持物化视图SQL的WHERE子句没有出现在查询SQL的WHERE子句中，但是查询SQL中所有WHERE子句涉及的列都必须在物化视图中存在。示例如下：

      物化视图SQL的SELECT部分如下：

      SELECT * FROM t WHERE a > 100 OR a < 200;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM t WHERE a < 200;

  • 当物化视图SQL的WHERE子句包含查询SQL的WHERE子句时：
    • 支持查询SQL的WHERE子句为等值，而物化视图SQL的WHERE子句为范围，但是查询SQL中所有WHERE子句涉及的列都必须在物化视图中存在。示例如下：

      物化视图SQL的SELECT部分如下：

      SELECT * FROM t WHERE a < 200 AND a >= 100;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM t WHERE a = 102;

    • 支持查询SQL的WHERE子句和物化视图SQL的WHERE子句均为限制范围，但是查询SQL中所有WHERE子句涉及的列都必须在物化视图中存在。示例如下：

      物化视图SQL的SELECT部分如下：

      SELECT * FROM t WHERE a < 200 AND a >= 0;

      可以被自动查询改写的查询SQL如下：

      SELECT * FROM t WHERE a <= 100 AND a > 50;

• HAVING子句
  当查询SQL的HAVING子句与物化视图SQL不完全相同时，自动查询改写对HAVING子句支持情况如下：

  • 当GROUP BY列不需要补偿的情况下，自动查询改写对HAVING子句的补偿方式与WHERE子句的补偿方式类似，支持对物化视图缺少的AND条件进行补偿；支持对物化视图多余的OR条件进行裁剪；支持对物化视图的范围条件进一步缩小。
  • 当GROUP BY列需要补偿的情况下，仅支持查询SQL包含HAVING而物化视图SQL中不包含HAVING的改写。
• ORDER BY列

  无论物化视图SQL中是否包含ORDER BY，自动查询改写都会尝试进行ORDER BY补偿。所以查询SQL的ORDER BY列必须在物化视图SQL的SELECT列中，否则自动查询改写不会发生。

• LIMIT子句

  当物化视图SQL中不包含LIMIT子句时，自动查询改写会进行LIMIT补偿。如果物化视图SQL中包含LIMIT子句，那么查询SQL必须与物化视图SQL完全一致。

• 表达式补偿

  对于查询SQL中的普通表达式和聚合函数表达式，如果整个表达式无法与物化视图SQL中的表达式直接匹配，则会尝试使用它的子表达式来查找匹配项。子表达式按自上而下的顺序进行尝试以获得最大的表达式匹配。示例如下：

  物化视图SQL的SELECT部分如下：

  SELECT a+b, c FROM t;

  可以被自动查询改写的查询SQL如下：

  SELECT a+b, (a+b)+c, mod(a+b, c) FROM t;
  SELECT sum((a+b)*c) FROM t;

  自动查询改写对聚合函数表达式支持情况如下：

  • 支持通过物化视图SQL的sum()和count()聚合函数，计算出avg()聚合函数。
  • 支持查询SQL和物化视图SQL间count(*)和count(1)的互换匹配。

  聚合函数表达式示例如下：

  物化视图SQL的SELECT部分如下：

  SELECT sum(a), count(a), count(*) FROM t;

  可以被自动查询改写的查询SQL如下：

  SELECT avg(a), count(1) FROM;

CTE和子查询

自动查询改写处理CTE和子查询的方式类似，都是分为主查询和子查询来处理（CTE的WITH中的查询类似于子查询）。

• 当查询SQL仅包含一个子查询时：
  • 若查询SQL与物化视图SQL的主查询以及子查询完全相同，进行全匹配替换。
  • 若查询SQL与物化视图SQL的子查询完全相同，但是查询的主查询需要进行查询补偿，关于查询补偿，请参见查询补偿。
  • 若查询SQL包含子查询，而物化视图SQL不包含子查询：
    • 物化视图SQL与查询SQL的父查询相同或可以补偿，支持对物化视图补偿一个子查询来返回查询。需要注意，不支持补偿关联子查询。
    • 物化视图SQL与查询SQL的子查询相同或可以补偿，支持仅替换查询中的子查询。
    • 如果查询SQL的主查询或者子查询被一个物化视图替换后，自动查询改写还会继续尝试对剩余的部分进行替换。
  • 当前物化视图自动查询改写暂时不支持递归CTE（WITH RECURSIVE）的语句的替换。
• 当查询SQL包含多个子查询时：

  如果查询的主查询或某一个子查询被改写后，自动查询改写还会继续重复上述规则，尝试对剩余部分进行改写。

UNION、EXCEPT、INTERSECT

• 如果查询SQL与物化视图SQL都包含UNION、EXCEPT、INTERSECT，UNION、INTERSECT支持UNION、INTERSECT子句的顺序交换，以及额外补偿UNION、INTERSECT子句；EXCEPT仅支持额外补偿子句，不能进行顺序交换。
• 如果查询SQL包含UNION、EXCEPT、INTERSECT，而物化视图SQL不包含UNION、EXCEPT、INTERSECT，自动查询改写支持通过补偿UNION、EXCEPT、INTERSECT将多个物化视图作为UNION、EXCEPT、INTERSECT的子句连接起来。

匹配多个物化视图

如果一个查询SQL，有多个物化视图可以与之匹配，自动查询改写会按如下优先级选择物化视图：

• 优先全匹配，当不存在全匹配的物化视图时才会尝试选择需要进行补偿的物化视图。
• 当有多个需要进行补偿的物化视图时，查询SQL涉及的表与物化视图涉及的表匹配的数量越多，优先级越高。
• 当有多个需要进行补偿的物化视图与查询SQL涉及的表匹配数量相同时，根据物化视图的数据存储大小作为优先级标准，物化视图的数据存储大小越小，优先级越高。

示例

• 示例一：
  1. 创建基表。

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

  2. 向基表插入测试数据。

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

  3. 创建物化视图。

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

  4. 执行查询计划。

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

     返回信息如下，自动查询改写进行了全匹配替换返回了视图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)

• 示例二：
  1. 创建两张基表。

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

  2. 分别向两张基表插入测试数据。

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

  3. 创建物化视图。

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

  4. 执行查询计划。

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

     返回信息如下，自动查询改写补偿了JOIN表、WHERE子句并裁剪了GROUP BY列返回了视图mv的数据。

                                                 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)