マテリアライズドビューのクエリ書き換え - AnalyticDB - Alibaba Cloud ドキュメントセンター

AnalyticDB for PostgreSQL は、標準およびリアルタイムマテリアライズドビューのクエリ書き換え機能を提供します。この機能は、JOIN 操作、集計関数、サブクエリ、共通テーブル式（CTE）、および高並行 SQL 文のパフォーマンスを大幅に向上させることができます。

ベストプラクティス: 変数パラメータを含むクエリの高速化にリアルタイムマテリアライズドビューを使用する

機能説明

クエリ書き換えがサポートされる前は、SELECT クエリ文を手動で変更して、マテリアライズドビューの使用を指定する必要がありました。クエリ書き換えがサポートされた後では、SELECT クエリ文がマテリアライズドビューではなくベーステーブルを参照している場合でも、SELECT クエリ文を自動的に書き換えてマテリアライズドビューを使用できます。これにより、SELECT クエリ文の実行が高速化されます。

SELECT クエリ文が CREATE MATERIALIZED VIEW 文の SELECT 文と完全に同一である場合、AnalyticDB for PostgreSQL はクエリ書き換えをトリガーして、マテリアライズドビューのデータを使用してクエリを高速化します。詳細については、このトピックの「完全一致」セクションをご参照ください。
SELECT クエリ文が CREATE MATERIALIZED VIEW 文の SELECT 文と部分的に同一である場合、クエリ書き換えは CREATE MATERIALIZED VIEW 文の SELECT 文を補足します。詳細については、このトピックのクエリの補足」セクションをご参照ください。

サポートされているバージョン

AnalyticDB for PostgreSQL V6.0 インスタンス V6.3.6.0 以後。

説明

インスタンスのページでマイナーバージョンDELETE を表示できます。インスタンスが必要なバージョンを満たしていない場合は、インスタンスのマイナーバージョンを更新してください。

制限事項

クエリ書き換えは、SELECT FOR UPDATE 文ではサポートされていません。
クエリ書き換えは、再帰 CTE を含む文ではサポートされていません。
クエリ書き換えは、RANDOM() や NOW() などのランダム関数を含むクエリではサポートされていません。
SELECT クエリ文が CREATE MATERIALIZED VIEW 文の SELECT 文と部分的に同一であるが、クエリの補足の要件が満たされていない場合、クエリ書き換えはサポートされません。クエリの補足の詳細については、このトピックの「クエリの補足」セクションをご参照ください。

クエリ書き換えを有効または無効にする

リアルタイムマテリアライズドビュー
デフォルトでは、リアルタイムマテリアライズドビューのクエリ書き換えは有効になっています。次の文を実行して、この機能を無効にすることができます。
```
SET enable_incremental_matview_query_rewrite TO OFF;
```
標準マテリアライズドビュー
デフォルトでは、標準マテリアライズドビューのクエリ書き換えは無効になっています。次の文を実行して、この機能を有効にすることができます。
```
SET enable_matview_query_rewrite TO ON;
```

説明

特定のインスタンスに対してこの機能を有効または無効にすることはできません。特定のインスタンスのクエリ書き換えを有効または無効にするには、チケットを起票してください。

完全一致

AnalyticDB for PostgreSQL では、クエリ書き換えは、スペース、改行、コメント、またはエイリアスに関係なく、SELECT クエリ文と CREATE MATERIALIZED VIEW 文の SELECT 文の間で構文ツリーが完全に一致するかどうかを確認します。 SELECT クエリ文が CREATE MATERIALIZED VIEW 文の SELECT 文と完全に同一である場合、マテリアライズドビューが優先的に使用されてクエリが高速化されます。

クエリの補足

AnalyticDB for PostgreSQL では、SELECT クエリ文が CREATE MATERIALIZED VIEW 文の SELECT 文と部分的に同一である場合、クエリ書き換えを使用できます。このシナリオでは、クエリ書き換えは CREATE MATERIALIZED VIEW 文の SELECT 文を補足し、マテリアライズドビューに基づいてクエリ結果を返します。

クエリの補足は、SELECT クエリ文の次の部分についてのみサポートされています。SELECT 列、JOIN テーブル、GROUP BY 列、WHERE 句、HAVING 句、ORDER BY 列、および LIMIT 句。クエリ書き換えの要件を満たすには、SELECT クエリ文の他の部分が CREATE MATERIALIZED VIEW 文の SELECT 文の他の部分と完全に同一であることを確認してください。

SELECT 列
SELECT クエリ文の SELECT 列が CREATE MATERIALIZED VIEW 文の SELECT 文の SELECT 列と部分的に同一である場合、クエリ書き換えには次のルールが適用されます。
- SELECT クエリ文の SELECT 列の順序が CREATE MATERIALIZED VIEW 文の SELECT 文の SELECT 列の順序と異なる場合、クエリ書き換えはサポートされます。
- CREATE MATERIALIZED VIEW 文の SELECT 文の SELECT 列が SELECT クエリ文に含まれていない場合、クエリ書き換えはサポートされます。
- SELECT クエリ文の SELECT 列が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれていないが、CREATE MATERIALIZED VIEW 文の SELECT 文の SELECT 列から計算できる場合、クエリ書き換えはサポートされます。
- SELECT クエリ文の SELECT 列が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれておらず、CREATE MATERIALIZED VIEW 文の SELECT 文の SELECT 列から計算できない場合、クエリ書き換えはサポートされません。
GROUP BY 列
SELECT クエリ文の GROUP BY 列が CREATE MATERIALIZED VIEW 文の SELECT 文の GROUP BY 列と部分的に同一である場合、クエリ書き換えには次のルールが適用されます。
- CREATE MATERIALIZED VIEW 文の SELECT 文に GROUP BY 列または集計関数が含まれていない場合:
  - SELECT クエリ文に集計関数が含まれている場合、クエリ書き換えはサポートされます。
  - SELECT クエリ文に GROUP BY 列が含まれている場合、クエリ書き換えはサポートされます。
  - SELECT クエリ文に GROUP BY 列と集計関数が含まれている場合、クエリ書き換えはサポートされます。
- CREATE MATERIALIZED VIEW 文の SELECT 文に GROUP BY 列が含まれているが集計関数が含まれていない場合:
  - CREATE MATERIALIZED VIEW 文の SELECT 文の GROUP BY 列が SELECT クエリ文に含まれていない場合、クエリ書き換えはサポートされます。
  - SELECT クエリ文の GROUP BY 列が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれていない場合、クエリ書き換えはサポートされません。
  - SELECT クエリ文の集計関数が count(distinct) である場合、クエリ書き換えはサポートされます。
- CREATE MATERIALIZED VIEW 文の SELECT 文に GROUP BY 列が含まれていないが集計関数が含まれている場合:
  - SELECT クエリ文に GROUP BY 列が含まれている場合、クエリ書き換えはサポートされません。
- CREATE MATERIALIZED VIEW 文の SELECT 文に GROUP BY 列と集計関数が含まれている場合:
  - CREATE MATERIALIZED VIEW 文の SELECT 文の GROUP BY 列が SELECT クエリ文に含まれていない場合、クエリ書き換えはサポートされます。
  - SELECT クエリ文の GROUP BY 列が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれていない場合、クエリ書き換えはサポートされません。
説明
- SELECT クエリ文に含まれる GROUP BY 列の数が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれる GROUP BY 列の数よりも少ない場合、クエリ書き換えは、集計関数に対して再集計を実行することにより、CREATE MATERIALIZED VIEW 文の SELECT 文を補足します。再集計では、次の集計関数がサポートされています。COUNT、SUM、MAX、MIN、および AVG。 SELECT クエリ文に他の集計関数が含まれている場合、クエリ書き換えはサポートされません。
- SELECT クエリ文に HAVING 句が含まれている場合、GROUP BY 列を補足に使用することはできません。
JOIN テーブル
SELECT クエリ文の JOIN テーブルまたは条件が CREATE MATERIALIZED VIEW 文の SELECT 文の JOIN テーブルまたは条件と部分的に同一である場合、クエリ書き換えには次のルールが適用されます。
- INNER JOIN テーブルは交換でき、追加の JOIN テーブルまたは条件を使用して CREATE MATERIALIZED VIEW 文の SELECT 文を補足できます。
- LEFT OUTER JOIN と RIGHT OUTER JOIN は相互に変換でき、FULL OUTER JOIN の左テーブルと右テーブルは交換できます。追加の JOIN テーブルまたは条件を使用して CREATE MATERIALIZED VIEW 文の SELECT 文を補足することはできません。
SELECT クエリ文の JOIN テーブルが CREATE MATERIALIZED VIEW 文の SELECT 文の JOIN テーブルと完全に同一である場合、クエリ書き換えには次のルールが適用されます。
- INNER JOIN テーブルは交換できます。例:
  - CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM a, b WHERE a.i = b.i;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM b, a WHERE a.i = b.i;
SELECT * FROM a INNER JOIN b ON a.i = b.i;
```
  - CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM a INNER JOIN b ON a.i = b.i;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM b INNER JOIN a ON a.i = b.i;
```
- LEFT OUTER JOIN と RIGHT OUTER JOIN は相互に変換できます。例:
  CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM a LEFT JOIN b ON a.i = b.i;
```
  クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM b RIGHT JOIN a ON b.i = a.i;
```
- FULL OUTER JOIN の左テーブルと右テーブルは交換できます。例:
  CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM a FULL OUTER JOIN b ON a.i = b.i;
```
  クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM b FULL OUTER JOIN a ON b.i = a.i;
```
SELECT クエリ文の JOIN テーブルが CREATE MATERIALIZED VIEW 文の SELECT 文の JOIN テーブルと部分的に同一である場合、クエリ書き換えには次のルールが適用されます。
追加の INNER JOIN テーブルを補足できます。このシナリオでは、INNER JOIN テーブルまたは COMMON JOIN テーブルを交換でき、INNER JOIN と COMMON JOIN を相互に変換できます。例:
- CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM a, b;
```
  クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM a, b, c;
```
- CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM a INNER JOIN b ON a.i = b.i;
```
  クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM a INNER JOIN b ON a.i = b.i INNER JOIN c ON a.i = c.i;
```
WHERE 句
SELECT クエリ文の WHERE 句が CREATE MATERIALIZED VIEW 文の SELECT 文の WHERE 句と部分的に同一である場合、クエリ書き換えには次のルールが適用されます。
- SELECT クエリ文と CREATE MATERIALIZED VIEW 文の SELECT 文の両方で AND を使用して複数の WHERE 条件を結合する場合:
  - SELECT クエリ文の WHERE 条件の順序が CREATE MATERIALIZED VIEW 文の SELECT 文の WHERE 条件の順序と異なる場合、クエリ書き換えはサポートされます。例:
    CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM t WHERE a > 100 AND a < 200;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM t WHERE a < 200 AND a > 100;
```
  - SELECT クエリ文の WHERE 句が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれていない場合、クエリ書き換えはサポートされます。このシナリオでは、クエリ書き換えは見つからない WHERE 句を補足します。補足される WHERE 句で参照される列は、マテリアライズドビューに存在する必要があります。例:
    CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM t WHERE a > 100;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM t WHERE b > 200 AND a > 100;
```
- SELECT クエリ文と CREATE MATERIALIZED VIEW 文の SELECT 文の両方で OR を使用して複数の WHERE 条件を結合する場合:
  - SELECT クエリ文の WHERE 条件の順序が CREATE MATERIALIZED VIEW 文の SELECT 文の WHERE 条件の順序と異なる場合、クエリ書き換えはサポートされます。すべての WHERE 句で参照される列は、マテリアライズドビューに存在する必要があります。例:
    CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM t WHERE a > 100 OR a < 200;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM t WHERE a < 200 OR a > 100;
```
  - SELECT クエリ文の WHERE 句が CREATE MATERIALIZED VIEW 文の SELECT 文に含まれていない場合、クエリ書き換えはサポートされます。すべての WHERE 句で参照される列は、マテリアライズドビューに存在する必要があります。例:
    CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM t WHERE a > 100 OR a < 200;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM t WHERE a < 200;
```
- CREATE MATERIALIZED VIEW 文の SELECT 文の WHERE 句に SELECT クエリ文の WHERE 句が含まれている場合:
  - SELECT クエリ文の WHERE 句が等式条件で構成されているが、CREATE MATERIALIZED VIEW 文の SELECT 文の WHERE 句で範囲が指定されている場合、クエリ書き換えはサポートされます。すべての WHERE 句で参照される列は、マテリアライズドビューに存在する必要があります。例:
    CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM t WHERE a < 200 AND a >= 100;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM t WHERE a = 102;
```
  - SELECT クエリ文と CREATE MATERIALIZED VIEW 文の SELECT 文の両方の WHERE 句で範囲が指定されている場合、クエリ書き換えはサポートされます。すべての WHERE 句で参照される列は、マテリアライズドビューに存在する必要があります。例:
    CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT * FROM t WHERE a < 200 AND a >= 0;
```
    クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT * FROM t WHERE a <= 100 AND a > 50;
```
HAVING 句
SELECT クエリ文の HAVING 句が CREATE MATERIALIZED VIEW 文の SELECT 文の HAVING 句と部分的に同一である場合、クエリ書き換えには次のルールが適用されます。
- GROUP BY 列を補足する必要がない場合、クエリ書き換えは、WHERE 句を補足する場合と同様の方法で HAVING 句を補足します。 CREATE MATERIALIZED VIEW 文の SELECT 文では、見つからない AND 条件を補足したり、追加の OR 条件を削除したり、範囲を絞り込んだりできます。
- GROUP BY 列を補足する必要がある場合、SELECT クエリ文に HAVING 句が含まれているが、CREATE MATERIALIZED VIEW 文の SELECT 文に HAVING 句が含まれていないシナリオでは、クエリ書き換えがサポートされます。
ORDER BY 列
CREATE MATERIALIZED VIEW 文の SELECT 文に ORDER BY 列が含まれているかどうかに関係なく、クエリ書き換えは ORDER BY 列を補足しようとします。クエリ書き換えの要件を満たすには、SELECT クエリ文の ORDER BY 列が CREATE MATERIALIZED VIEW 文の SELECT 文の ORDER BY 列に含まれていることを確認してください。
LIMIT 句
CREATE MATERIALIZED VIEW 文の SELECT 文に LIMIT 句が含まれていない場合、クエリ書き換えは LIMIT 句を補足します。 CREATE MATERIALIZED VIEW 文の SELECT 文に LIMIT 句が含まれている場合、SELECT クエリ文は CREATE MATERIALIZED VIEW 文の SELECT 文と完全に同一である必要があります。
式の補足
SELECT クエリ文の通常式または集計関数式が CREATE MATERIALIZED VIEW 文の SELECT 文の式と一致しない場合、SELECT クエリ文のサブ式が上から下に向かって使用され、最も近い一致が検索されます。例:
CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT a+b, c FROM t;
```
クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT a+b, (a+b)+c, mod(a+b, c) FROM t;
SELECT sum((a+b)*c) FROM t;
```
集計関数式が含まれている場合、クエリ書き換えには次のルールが適用されます。
- CREATE MATERIALIZED VIEW 文の SELECT 文の集計関数 SUM() と COUNT() は、AVG() 集計関数に計算できます。
- 集計関数 COUNT(*) と COUNT(1) は、SELECT クエリ文と CREATE MATERIALIZED VIEW 文の SELECT 文の間で交換できます。
集計関数式の例:
CREATE MATERIALIZED VIEW 文の SELECT 文:
```
SELECT sum(a), count(a), count(*) FROM t;
```
クエリ書き換えをサポートする SELECT クエリ文:
```
SELECT avg(a), count(1) FROM;
```

CTE とサブクエリ

CTE とサブクエリが含まれている場合、プライマリクエリとサブクエリに基づいて、クエリ書き換えには次のルールが適用されます。 WITH 句の CTE はサブクエリと同等です。

SELECT クエリ文に単一のサブクエリのみが含まれている場合:
- SELECT クエリ文のプライマリクエリとサブクエリが CREATE MATERIALIZED VIEW 文の SELECT 文のプライマリクエリとサブクエリと完全に同一である場合、クエリ書き換えは完全一致方式を使用して CREATE MATERIALIZED VIEW 文の SELECT 文を置き換えます。
- SELECT クエリ文のサブクエリが CREATE MATERIALIZED VIEW 文の SELECT 文のサブクエリと完全に同一であるが、プライマリクエリが異なる場合、クエリ書き換えは CREATE MATERIALIZED VIEW 文の SELECT 文を補足します。クエリの補足の詳細については、このトピックの「クエリの補足」セクションをご参照ください。
- SELECT クエリ文にサブクエリが含まれているが、CREATE MATERIALIZED VIEW 文の SELECT 文にサブクエリが含まれていない場合:
  - CREATE MATERIALIZED VIEW 文の SELECT 文が SELECT クエリ文のプライマリクエリと同一であるか、または補足できる場合、クエリ書き換えは CREATE MATERIALIZED VIEW 文の SELECT 文にサブクエリを補足します。関連付けられたサブクエリを補足することはできません。
  - CREATE MATERIALIZED VIEW 文の SELECT 文が SELECT クエリ文のサブクエリと同一であるか、または補足できる場合、クエリ書き換えは SELECT クエリ文のサブクエリを置き換えます。
  - SELECT クエリ文のプライマリクエリまたはサブクエリがマテリアライズドビューに置き換えられた場合、クエリ書き換えは SELECT クエリ文の他の部分の置き換えを続けます。
- クエリ書き換えは、再帰 CTE の置き換えをサポートしていません。
SELECT クエリ文に複数のサブクエリが含まれている場合:
SELECT クエリ文のプライマリクエリまたはサブクエリが書き換えられた場合、クエリ書き換えは上記のルールに基づいて SELECT クエリ文の他の部分の書き換えを続けます。

UNION、EXCEPT、および INTERSECT

SELECT クエリ文と CREATE MATERIALIZED VIEW 文の SELECT 文の両方に UNION、EXCEPT、または INTERSECT が含まれている場合、UNION または INTERSECT の前後の 2 つのクエリを交換でき、UNION または INTERSECT 句を補足できます。 EXCEPT 句は補足できますが、EXCEPT の前後の 2 つのクエリを交換することはできません。
SELECT クエリ文に UNION、EXCEPT、または INTERSECT が含まれているが、CREATE MATERIALIZED VIEW 文の SELECT 文に UNION、EXCEPTまたは INTERSECT が含まれていない場合、UNION、EXCEPT、または INTERSECT 句を補足して複数のマテリアライズドビューを結合できます。

複数のマテリアライズドビューの照合

SELECT クエリ文が複数のマテリアライズドビューと一致する場合、クエリ書き換えは次のルールに基づいてマテリアライズドビューを選択します。

SELECT クエリ文と完全に一致するマテリアライズドビューが優先的に選択されます。このマテリアライズドビューが存在しない場合は、補足できるマテリアライズドビューが選択されます。
補足できるマテリアライズドビューが複数存在する場合は、SELECT クエリ文のテーブルと最も多く一致するマテリアライズドビューが優先的に選択されます。
補足できる複数のマテリアライズドビューが SELECT クエリ文と同じ数のテーブルを参照している場合は、最も少ないデータを参照するマテリアライズドビューが優先的に選択されます。

例

例 1:

次の文を実行して、ベーステーブルを作成します。
```
CREATE TABLE t1 (a int, b int) DISTRIBUTED BY (a);
```
次の文を実行して、ベーステーブルにデータを挿入します。
```
INSERT INTO t1 VALUES (generate_series(1, 10), generate_series(1, 2));
```

次の文を実行して、マテリアライズドビューを作成します。

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

次の文を実行して、クエリプランを実行します。

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

次の結果が返されます。クエリ書き換えは完全一致方式を使用して CREATE MATERIALIZED VIEW 文の SELECT 文を置き換え、マテリアライズドビュー 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)

例 2:

次の文を実行して、2 つのベーステーブルを作成します。

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

次の文を実行して、ベーステーブルにデータを挿入します。

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

次の文を実行して、マテリアライズドビューを作成します。

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

次の文を実行して、クエリプランを実行します。

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)