【9.3新機能チェック】マテリアライズドビューを試してみる

昨日、PostgreSQLの次期リリースである9.3のソースコードに、マテリアライズドビューのコードが追加されました。

pgsql: Add a materialized view relations.
http://www.postgresql.org/message-id/E1UCJDN-00042x-0w@gemulon.postgresql.org

PostgreSQLの開発者Wikiによると、マテリアライズドビューはもっとも要望の多かった機能のようです。

Materialized Views - PostgreSQL wiki
http://wiki.postgresql.org/wiki/Materialized_Views

今回は、このマテリアライズドビューがどのようなものなのか、そしてどのように使えるのかを見てみます。

■マテリアライズドビューとは

「マテリアライズドビュー」とは、特に集約や集計系の処理をする際に使われる機能で、ビューから取得できるデータの実体を持つ（materialized）ビューです。

通常、ビューというのは「見え方を定義する」だけですので、ビューに対する参照処理を行うと、その都度、元のテーブルに対してSQLの参照処理が行われることになります。

しかし、集計系や集約系のビューの場合、参照する都度、元テーブルに対してSQLが実行され、非常に時間がかかることになります。

そのため、「ビューから取得できるデータを実体として保持しておく」ための機能として「マテリアライズドビュー」という機能が考えられました。

簡単に言うと、ビューから取得するデータをキャッシュしておく機能と考えれば良いでしょう。そのため、ビューへの問い合わせを非常に高速に行うことができるようになります。

マテリアライズドビュー - Wikipedia

■マテリアライズドビューを作成する

それでは、実際にマテリアライズドビューを使ってみます。

今回は、PostgreSQLのDWH系ワークロードツールであるDBT-3のスキーマを使い、月別の売り上げを顧客ごとに集計する以下のクエリをマテリアライズドビューとして定義することを考えてみます。

select c_name,
           date_trunc('month', o_orderdate),
           sum(o_totalprice)::numeric
      from orders o, customer c
     where o.o_custkey = c.c_custkey
     group by 1, 2;

このクエリのEXPLAINを取得すると402055程度となっています。

dbt3=# explain select c_name,
           date_trunc('month', o_orderdate),
           sum(o_totalprice)::numeric
      from orders o, customer c
     where o.o_custkey = c.c_custkey
     group by 1, 2;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=360805.98..402055.98 rows=1500000 width=27)
   ->  Sort  (cost=360805.98..364555.98 rows=1500000 width=27)
         Sort Key: c.c_name, (date_trunc('month'::text, (o.o_orderdate)::timestamp with time zone))
         ->  Hash Join  (cost=7785.00..99265.00 rows=1500000 width=27)
               Hash Cond: (o.o_custkey = c.c_custkey)
               ->  Seq Scan on orders o  (cost=0.00..40326.00 rows=1500000 width=12)
               ->  Hash  (cost=5031.00..5031.00 rows=150000 width=23)
                     ->  Seq Scan on customer c  (cost=0.00..5031.00 rows=150000 width=23)
(8 rows)

dbt3=#

このクエリをマテリアライズドビューとして定義します。マテリアライズドビューを作成するにはCREATE MATERIALIZED VIEWを使います。

PostgreSQL: Documentation: devel: CREATE MATERIALIZED VIEW
http://www.postgresql.org/docs/devel/static/sql-creatematerializedview.html

dbt3=# create materialized view customer_monthly_totalprice
dbt3-#  as select c_name,
dbt3-#            date_trunc('month', o_orderdate),
dbt3-#            sum(o_totalprice)::numeric
dbt3-#       from orders o, customer c
dbt3-#      where o.o_custkey = c.c_custkey
dbt3-#      group by 1, 2;
SELECT 1353175
dbt3=# 

上記のクエリによって、customer_monthly_totalpriceというマテリアライズドビューが作成されました。

dbt3=# select relname,relkind from pg_class where relname like 'cust%';
           relname           | relkind
-----------------------------+---------
 customer                    | r
 customer_monthly_totalprice | m
(2 rows)

dbt3=#

■マテリアライズドビューを使って検索する

それでは作成したマテリアライズドビューに対してクエリを実行してみます。

作成したマテリアライズドビューに対してフルスキャンを行うと、以下のように実行コストは25188程度となります。

dbt3=# explain select * from customer_monthly_totalprice;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Seq Scan on customer_monthly_totalprice  (cost=0.00..25188.75 rows=1353175 width=34)
(1 row)

dbt3=#

ビューを定義する前の元クエリの実行コストが402055でしたので、マテリアライズドビューを作成することによって集約処理をかなり高速化できていることが分かります。

このように、通常のビューと違って「データの実体」をキャッシュのように持っていますので、マテリアライズドビューを使うとクエリを高速化することができるようになります。

■マテリアライズドビューを更新する

マテリアライズドビューは、ビューをキャッシュするような機能ですので、元テーブルが更新された場合には更新されなければなりません。

ここでは、テーブルの元データを削除した際に、マテリアライズドビューがどのように動作するのかを見てみます。

まず、マテリアライズドビューで取得できるデータのうち、顧客名 'Customer#000000007' の注文データを削除してみます。

dbt3=# select * from customer_monthly_totalprice where c_name='Customer#000000007';
       c_name       |       date_trunc       |   sum
--------------------+------------------------+---------
 Customer#000000007 | 1992-03-01 00:00:00+00 |  322432
 Customer#000000007 | 1992-04-01 00:00:00+00 |  434825
 Customer#000000007 | 1993-06-01 00:00:00+00 |  501704
 Customer#000000007 | 1993-11-01 00:00:00+00 | 80777.5
 Customer#000000007 | 1994-02-01 00:00:00+00 |  176604
 Customer#000000007 | 1994-12-01 00:00:00+00 |  192318
 Customer#000000007 | 1995-09-01 00:00:00+00 |  327616
 Customer#000000007 | 1995-10-01 00:00:00+00 |  190890
 Customer#000000007 | 1996-06-01 00:00:00+00 | 79104.5
 Customer#000000007 | 1997-01-01 00:00:00+00 |  181378
 Customer#000000007 | 1997-02-01 00:00:00+00 |  151988
 Customer#000000007 | 1998-02-01 00:00:00+00 | 31698.3
 Customer#000000007 | 1998-07-01 00:00:00+00 |  286525
(13 rows)

dbt3=# delete from orders where o_custkey in ( select c_custkey from customer where c_name = 'Customer#000000007' );
DELETE 16

元テーブル orders からレコードを削除した後、再度マテリアライズドビューからデータを取得してみます。

dbt3=# select * from customer_monthly_totalprice where c_name='Customer#000000007';
       c_name       |       date_trunc       |   sum
--------------------+------------------------+---------
 Customer#000000007 | 1992-03-01 00:00:00+00 |  322432
 Customer#000000007 | 1992-04-01 00:00:00+00 |  434825
 Customer#000000007 | 1993-06-01 00:00:00+00 |  501704
 Customer#000000007 | 1993-11-01 00:00:00+00 | 80777.5
 Customer#000000007 | 1994-02-01 00:00:00+00 |  176604
 Customer#000000007 | 1994-12-01 00:00:00+00 |  192318
 Customer#000000007 | 1995-09-01 00:00:00+00 |  327616
 Customer#000000007 | 1995-10-01 00:00:00+00 |  190890
 Customer#000000007 | 1996-06-01 00:00:00+00 | 79104.5
 Customer#000000007 | 1997-01-01 00:00:00+00 |  181378
 Customer#000000007 | 1997-02-01 00:00:00+00 |  151988
 Customer#000000007 | 1998-02-01 00:00:00+00 | 31698.3
 Customer#000000007 | 1998-07-01 00:00:00+00 |  286525
(13 rows)

この段階では、まだマテリアライズドビューが以前のデータを保持しているために、ordersテーブルから元データが削除されたことが反映されていません。

ここで、マテリアライズドビューを更新します。マテリアライズドビューを更新するためにはREFRESH MATERIALIZED VIEWを使います。

PostgreSQL: Documentation: devel: REFRESH MATERIALIZED VIEW
http://www.postgresql.org/docs/devel/static/sql-refreshmaterializedview.html

dbt3=# refresh materialized view customer_monthly_totalprice;
REFRESH MATERIALIZED VIEW
dbt3=# select * from customer_monthly_totalprice where c_name='Customer#000000007';
 c_name | date_trunc | sum
--------+------------+-----
(0 rows)

dbt3=#

マテリアライズドビューを更新すると、元テーブル orders からレコードが削除されたことが反映されて、マテリアライズドビューからデータが消えました。

■まとめ

以上、簡単ではありますが次期バージョン9.3に向けてコミットされたての機能である「マテリアライズドビュー」を試してみました。

見てきたように、マテリアライズドビューは集約や集計系の処理のパフォーマンスを大幅に向上させる可能性を秘めた強力な機能です。

私自身も、最近はデータ分析のプラットフォームとしてPostgreSQLを使うケースが増えており、非常に楽しみにしている機能の一つです。

興味を持った方は、ぜひご自身でもトライしてみていただければと思います。

では、また。