AnalyticDB:リアルタイムマテリアライズドビューを使用して、変数パラメーターを含むクエリを高速化する

手順

次のセクションでは、リアルタイムのマテリアライズドビューとクエリの書き換えを使用して、変数パラメーターを含むクエリを高速化する方法について説明します。 この例では、TPC-H Q1クエリが使用されます。

TPC-H Q1の詳細については、「TPC-H」をご参照ください。

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

CREATE TABLE lineitem (
    l_orderkey    bigint not null,
    l_partkey     integer not null,
    l_suppkey     integer not null,
    l_linenumber  integer not null,
    l_quantity    numeric not null,
    l_extendedprice  numeric not null,
    l_discount    numeric not null,
    l_tax         numeric not null,
    l_returnflag  "char" not null,
    l_linestatus  "char" not null,
    l_shipdate    date not null,
    l_commitdate  date not null,
    l_receiptdate date not null,
    l_shipinstruct char(25) not null,
    l_shipmode     char(10) not null,
    l_comment      varchar(44) not null
) DISTRIBUTED BY (l_orderkey);

次のステートメントを実行して、TPC-H Q1クエリを実行します。

SELECT l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
FROM
    lineitem
WHERE
    l_shipdate <= date '1998-12-01' - interval '$1' day --$1 specifies a dynamic condition, which is in the range of 60 to 120.
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus
LIMIT 1;

上記のTPC-H Q1クエリステートメントのWHERE句では、l_shipdateの値は日付と間隔の値によって異なり、間隔の値の範囲は60〜120です。

次のステートメントを実行して、TPC-HのQ1クエリステートメントに基づいてマテリアライズドビューを作成します。

CREATE INCREMENTAL MATERIALIZED VIEW q1_mv
AS
SELECT
    l_returnflag,
    l_linestatus,
    l_shipdate,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    count(*) as count_order,
    sum(l_extendedprice) as sum_price,
    sum(l_discount) as sum_disc,
    count(l_quantity) as count_qty,
    count(l_extendedprice) as count_price,
    count(l_discount) as count_disc
FROM
    lineitem
WHERE
    l_shipdate <= date '1998-12-01' - interval '60' day
GROUP BY
    l_returnflag,
    l_linestatus,
    l_shipdate
DISTRIBUTED BY (l_returnflag, l_linestatus);

上記のステートメントでは、クエリの書き換えの次の機能が使用されます。

• サプリメントの再凝集

  SQL文では、WHERE句はGROUP BY句の前に実行されます。 TPC-H Q1クエリステートメントには、GROUP BY句のl_returnflagおよびl_linestatusフィールドのみが含まれます。 WHERE句の変数パラメーターをサポートするために、リアルタイムマテリアライズドビューの作成に使用されるステートメントには、GROUP BY句のl_returnflagおよびL_linestatusの他に、L_shipdateフィールドが含まれます。 実際のクエリでは、マテリアライズドビューで動的WHERE条件を適用し、再集計を実行して、l_returnflagおよびl_linestatusフィールドのGROUP BY結果を取得できます。

  説明

  AVG機能は再集計をサポートしていません。 したがって、クエリの書き換えでは、式の補足メカニズムを使用して、マテリアライズドビューでの再集計をサポートするSUM関数とCOUNT関数を作成します。 補足メカニズムは、以下の式AVG = SUM/COUNTに基づいて実装される。

• WHERE条件範囲マッチング

  マテリアライズドビューの作成時にWHERE条件が指定されていない場合、クエリの書き換えによって条件が自動的に補完されます。 ただし、動的条件は60〜120の範囲でなければなりません。 マテリアライズド・ビューを作成すると、WHERE条件の範囲を60に絞り込むことができます。 このように、具体化されたビューは、60〜120の範囲の条件を使用することができる。 クエリの書き換えは、実際のクエリステートメントに含まれる条件が、マテリアライズドビューに含まれる条件のサブセットであるかどうかを確認します。 はいの場合、クエリの書き換えが必要な条件を補足します。

次のステートメントを実行して、クエリプランでのマテリアライズド・ビューの使用状況を確認します。

EXPLAIN SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
FROM
    lineitem
WHERE
    l_shipdate <= date '1998-12-01' - interval '100' day
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus
LIMIT 1;

次のサンプル結果が返されます。

                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.01..1.13 rows=1 width=234)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=1.01..1.13 rows=1 width=234)
         Merge Key: l_returnflag, l_linestatus
         ->  Limit  (cost=1.01..1.11 rows=1 width=234)
               ->  GroupAggregate  (cost=1.01..1.11 rows=1 width=234)
                     Group Key: l_returnflag, l_linestatus
                     ->  Sort  (cost=1.01..1.01 rows=1 width=194)
                           Sort Key: l_returnflag, l_linestatus
                           ->  Seq Scan on q1_mv  (cost=0.00..1.00 rows=1 width=194)
                                 Filter: (l_shipdate <= '1998-08-23 00:00:00'::timestamp without time zone)
 Optimizer: Postgres query optimizer
(11 rows)

テスト結果

TPC-HのQ1クエリ速度は、リアルタイムのマテリアライズドビューとクエリの書き換えを使用することで大幅に向上します。 1テラバイトデータのTPC-Hテストは、16個の計算ノードを含むAnalyticDB for PostgreSQLインスタンスで実行されます。 リアルタイムのマテリアライズドビューとクエリの書き換えがTPC-HのQ1クエリに使用されると、クエリ速度はほぼ10,000倍向上し、クエリ時間は340秒から0.04秒に大幅に短縮されます。

関連ドキュメント

• リアルタイムマテリアライズドビュー

• マテリアライズドビューのクエリ書き換え