Amazon Redshift マテリアライズドビューのAutomatic query rewritingを試してみました

以前から気になっていたクエリプランナが自動的にテーブルをマテリアライズドビュー置き換えてクエリを実行する機能 Automatic query rewriting の実際の動作を確認しました。日本語では「クエリの書き換え」らしいのですが、いまいちピンとこないので、Automatic query rewritingと原文をそのまま引用しています。

Automatic query rewritingとは

マテリアライズドビューは、ビュー定義内のクエリ（結合、フィルタ、集計）の実行結果を保持することで、ビュー定義内のクエリの実行することなく結果が得られるため、CPUやIOの負荷を抑え、素早く結果が得られるようになります。

一般的なマテリアライズドビューは、SQL内でテーブルの代わりにマテリアライズドビューを指定しますが、Redshiftのマテリアライズドビューは、SQL内でテーブルを指定した場合でも、クエリプランナが自動的にテーブルをマテリアライズドビュー置き換えて実行できる機能を備えています。そのため、利用者はマテリアライズドビューを意識することなく、暗黙的にマテリアライズドビューの恩恵が得られるようになります。

具体的な動作を解説

例えば、下記のような集計クエリの実行プランでは、FROM句に指定したテーブル（orders）が指定されています。

cmdb=# EXPLAIN
SELECT
  "orders"."地域" AS "地域",
  "orders"."都道府県" AS "都道府県",
  "orders"."市町村" AS "市町村",
  "orders"."製品カテゴリー" AS "製品カテゴリー",
  SUM("orders"."売り上げ") AS "sum:売り上げ:ok"
FROM
  "superstore"."orders" "orders"
GROUP BY
  1,2,3,4
;
                             QUERY PLAN
--------------------------------------------------------------------
 XN HashAggregate  (cost=188.30..190.40 rows=837 width=54)
   ->  XN Seq Scan on orders  (cost=0.00..83.69 rows=8369 width=54)
(2 rows)

上記の集計クエリを用いてマテリアライズドビューを作成します。

cmdb=# CREATE MATERIALIZED VIEW orders_city_summary AS (
SELECT
  "orders"."地域" AS "地域",
  "orders"."都道府県" AS "都道府県",
  "orders"."市町村" AS "市町村",
  "orders"."製品カテゴリー" AS "製品カテゴリー",
  SUM("orders"."売り上げ") AS "sum:売り上げ:ok"
FROM
  "superstore"."orders" "orders"
GROUP BY
  1,2,3,4
);
CREATE MATERIALIZED VIEW

再び、最初と全く同じSQLの実行プランを確認すると、FROM句に指定したテーブル（orders）ではなく、マテリアライズド・ビュー（mv_tbl__orders_city_summary__0）に置き換えられています。

cmdb=# EXPLAIN
SELECT
  "orders"."地域" AS "地域",
  "orders"."都道府県" AS "都道府県",
  "orders"."市町村" AS "市町村",
  "orders"."製品カテゴリー" AS "製品カテゴリー",
  SUM("orders"."売り上げ") AS "sum:売り上げ:ok"
FROM
  "superstore"."orders" "orders"
GROUP BY
  1,2,3,4
;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 XN HashAggregate  (cost=38.59..40.12 rows=611 width=54)
   ->  XN Seq Scan on mv_tbl__orders_city_summary__0 derived_table1  (cost=0.00..17.15 rows=1715 width=54)
(2 rows)

上記は、実行したクエリとマテリアライズドビューのクエリが全く同じだったので、置き換え可能だったのかもしれません。そこで、上記のクエリをドリルアップした２種類の実行プランを確認しましたが、いい感じでマテリアライズドビューに書き換えられています。

下記以外に、SELECT句の並びを変更してもマテリアライズドビューに書き換えられていますので、SQLテキストレベルではない段階でクエリ書き換えが行われていると推測されます。

cmdb=# EXPLAIN
SELECT
  "orders"."地域" AS "地域",
  "orders"."都道府県" AS "都道府県",
  "orders"."製品カテゴリー" AS "製品カテゴリー",
  SUM("orders"."売り上げ") AS "sum:売り上げ:ok"
FROM
  "superstore"."orders" "orders"
GROUP BY
  1,2,3;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 XN HashAggregate  (cost=34.30..34.73 rows=172 width=44)
   ->  XN Seq Scan on mv_tbl__orders_city_summary__0 derived_table1  (cost=0.00..17.15 rows=1715 width=44)
(2 rows)

cmdb=# EXPLAIN
SELECT
  "orders"."地域" AS "地域",
  "orders"."製品カテゴリー" AS "製品カテゴリー",
  SUM("orders"."売り上げ") AS "sum:売り上げ:ok"
FROM
  "superstore"."orders" "orders"
GROUP BY
  1,2;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 XN HashAggregate  (cost=30.01..30.07 rows=24 width=34)
   ->  XN Seq Scan on mv_tbl__orders_city_summary__0 derived_table1  (cost=0.00..17.15 rows=1715 width=34)
(2 rows)

Automatic query rewritingの制限事項

Automatic query rewritingは、以下を参照もしくは含むマテリアライズドビューでは機能しません。テーブルを参照する SELECT クエリを書き換えるため、CREATE TABLE ASやSELECT INTOも対象外です。その他、カタログやシステムテーブル、外部結合、SELECT DISTINCTも対象外なのでご注意ください。

• サブクエリ
• Left、right、または full 外部結合
• ORDER BY 句
• DISTINCT 集計
• Window 関数
• SUM、COUNT、または AVERAGE を除くすべての集計
• 外部テーブル

パフォーマンスや動作の検証

今回は、テーブル（６億件）から顧客（200万件）ごとの売上を集計するというシナリオで実施します。もちろん、クエリのリザルトキャッシュは、無効化しています。

cmdb=# SET enable_result_cache_for_session TO OFF;
SET

普通にテーブルを集計する

売上を集計するクエリを実行するのに約22秒かかっています。

cmawsteamdb=# SELECT count(*) FROM public.lineorder;
   count
-----------
 600037902
(1 row)

Time: 1613.185 ms (00:01.613)
cmdb=# SELECT count(*) FROM (
SELECT lo_custkey, sum(lo_ordertotalprice) AS lo_ordertotalprice_sum
FROM public.lineorder
GROUP BY 1
);
  count
---------
 2000000
(1 row)

Time: 22384.272 ms (00:22.384)

cmdb=# EXPLAIN
SELECT count(*) FROM (
SELECT lo_custkey, sum(lo_ordertotalprice) AS lo_ordertotalprice_sum
FROM public.lineorder
GROUP BY 1
);
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 XN Aggregate  (cost=7525363.31..7525363.31 rows=1 width=0)
   ->  XN Subquery Scan derived_table1  (cost=7500473.60..7520385.37 rows=1991177 width=0)
         ->  XN HashAggregate  (cost=7500473.60..7500473.60 rows=1991177 width=4)
               ->  XN Seq Scan on lineorder  (cost=0.00..6000378.88 rows=600037888 width=4)
(4 rows)

Time: 197.822 ms

マテリアライズドビューの作成

上記の集計クエリをそのまま流用してマテリアライズドビュー（lineorder_summary）を作成します。マテリアライズドビューを作成するとrdsdbという内部ユーザーが所有するmv_tbl__lineorder_summary__0も作成されます。

cmdb=# CREATE MATERIALIZED VIEW public.lineorder_summary AS (
SELECT lo_custkey, sum(lo_ordertotalprice) AS lo_ordertotalprice_sum
FROM public.lineorder
GROUP BY 1
);
CREATE MATERIALIZED VIEW
Time: 77503.457 ms (01:17.503)

cmdb=# \d
                             List of relations
 schema |                   name                    | type  |    owner
--------+-------------------------------------------+-------+--------------
 ：
 public | lineorder_summary                         | view  | root
 ：
 public | mv_tbl__lineorder_summary__0              | table | rdsdb
 ：

マテリアライズドビューの新規作成に約77秒かかりました。あれ遅いぞ、という印象を持たれるかもしれませんがファクトのような時系列データは増分更新も可能なので常にリフレッシュのコストが生じるわけではありません。

詳細は以下のブログをご覧ください。

再び普通にテーブルを集計する

同じクエリを実行すると、0.446秒でクエリが終了、レスポンスが約50倍改善しました。実行プランを確認すると、内部的にテーブルではなくmv_tbl__lineorder_summary__0を参照するように書き換えられることでパフォーマンスが向上していると考えられます。

cmdb=# SELECT count(*) FROM (
SELECT lo_custkey, sum(lo_ordertotalprice) AS lo_ordertotalprice_sum
FROM public.lineorder
GROUP BY 1
);
  count
---------
 2000000
(1 row)

Time: 446.933 ms

cmdb=# EXPLAIN
SELECT count(*) FROM (
SELECT lo_custkey, sum(lo_ordertotalprice) AS lo_ordertotalprice_sum
FROM public.lineorder
GROUP BY 1
);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 XN Aggregate  (cost=49889.72..49889.72 rows=1 width=0)
   ->  XN Subquery Scan derived_table2  (cost=25000.00..44911.77 rows=1991177 width=0)
         ->  XN HashAggregate  (cost=25000.00..25000.00 rows=1991177 width=4)
               ->  XN Seq Scan on mv_tbl__lineorder_summary__0 derived_table1  (cost=0.00..20000.00 rows=2000000 width=4)
(4 rows)

実行プランを更に確認すると、集計済みのマテリアライズドビューをそのまま参照して返すのではなく、もう一度集計し直しているというのは若干想定外でした。つまり、集約度が低い場合はあまりパフォーマンスが改善しないかもしれないというのは意識したほうが良いでしょう。

Automatic query rewritingは、デフォルトでonとなっていますので、テーブルの最新データを参照したい場合は、セッションでAutomatic query rewritingを無効化してください。

cmdb=# SET mv_enable_aqmv_for_session TO OFF;
SET

Automatic query rewritingの対象であるかを確認

STV_MV_INFO システムテーブルからマテリアライズドビューがクエリの自動書き換えの対象かどうかを確認できます。autorewriteがtなので書き換え可能な状態です。

cmdb=# SELECT * FROM stv_mv_info
WHERE db_name = 'cmdb' AND "schema" = 'public' AND name = 'lineorder_summary';
-[ RECORD 1 ]-
db_name          | cmdb
schema           | public
name             | lineorder_summary
updated_upto_xid | 68439661
is_stale         | f
owner_user_name  | root
state            | 1
autorefresh      | f
autorewrite      | t

まとめ

アドホックなスロークエリを特定して、そのクエリを汎化したマテリアライズドビューを作成すると、利用者はクエリを書き換えることなくマテリアライズドビューの恩恵を得られるようになります。マテリアライズドビューの更新は、データの更新が頻繁でなければ、同時期の追加された自動更新機能を併用することで運用を軽減することも可能になりました。自動更新は、マテリアライズドビュー作成時にAUTO REFRESH YESを指定してください。

マテリアライズドビューはオフピークに集計処理をオフロードし、繰り返されるアドホッククエリのレスポンス改善をするユースケースに効果的です。導入する際には、データの更新が頻繁に発生しないか、常に最新のデータでなくても構わないのかなど、要件を確認してください。

Amazon Redshiftは、マテリアライズドビューに関わらず暗黙的にクエリの書き換えることでクエリを最適化し、クエリのコンパイルキャッシュやパフォーマンスを改善するのが日常的なので、日本語で「クエリの書き換え」と書かれていても完全にスルーしていました。「スマートチューニング」のような目を引くカッコいい名前をつけてほしいです。

合わせて読みたい