✏️

PostgreSQLでマテリアライズドビューを作ってビューとの速度比較検証してみる

2022/10/16に公開約3,100字

PostgreSQLでマテリアライズドビューを作ってビューとの速度比較検証してみる

今日は、PostgreSQLでマテリアライズドビューの作成をし、ビューに対する検索クエリの実行速度とマテリアライズドビューに対する検索クエリの実行速度の比較を行いたいと思います。

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


クエリの結果を実際のテーブルにキャッシュする仕組みのこと。クエリの結果を表す仮想的なテーブルであるビューとは違い、都度クエリを発行する必要がないため、複雑な結合や集約を行うクエリをマテリアライズドビューとして作成しておくことで、効率的な参照が実現できる。ただし、あくまで「キャッシュ」であるため、リフレッシュのタイミングによっては最新でない状態を取得する可能性がある。PostgreSQLにおいては元テーブルが更新された際にマテリアライズドビューを更新する自動リフレッシュを指定するオプションがないため、cronジョブや手動リフレッシュによる計画的なリフレッシュ作業が必要になると考えられる。(認識齟齬あればご指摘いただけると幸いです。)

環境


POSTFRESQL TUTORIALで配布されているサンプルデータベースを使用します。リストアした時点である程度レコードが入っていますが、今回は検証のためにいくつかのテーブルに対してレコードを挿入します。

作成するマテリアライズドビューを「mv_sales_by_store」としました。店舗ごとの売上合計を集計したものになります。最初のリストア時点で、「sales_by_store」という名称のビューも作成されており、この両方へSELECTクエリを発行し、実行速度を比較していきます。

作成のために発行したクエリは以下の通り。

CREATE MATERIALIZED VIEW mv_sales_by_store AS
 SELECT (((c.city)::text || ','::text) || (cy.country)::text) AS store,
    (((m.first_name)::text || ' '::text) || (m.last_name)::text) AS manager,
    sum(p.amount) AS total_sales
   FROM (((((((payment p
     JOIN rental r ON ((p.rental_id = r.rental_id)))
     JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
     JOIN store s ON ((i.store_id = s.store_id)))
     JOIN address a ON ((s.address_id = a.address_id)))
     JOIN city c ON ((a.city_id = c.city_id)))
     JOIN country cy ON ((c.country_id = cy.country_id)))
     JOIN staff m ON ((s.manager_staff_id = m.staff_id)))
  GROUP BY cy.country, c.city, s.store_id, m.first_name, m.last_name
  ORDER BY cy.country, c.city

結果


とりあえず適当なダミーデータを該当のテーブルにそれぞれ追加し、各テーブルのレコードが100万レコードの状態に対する実行結果を取ってみる。

ビューに対する実行結果は以下の通り。

dvdrental=# SELECT * FROM sales_by_store;
        store        |   manager    | total_sales
---------------------+--------------+-------------
 Woodridge,Australia | Jon Stephens |    30683.13
 Lethbridge,Canada   | Mike Hillyer | 47941777.91
(2)

時間: 2034.285 ミリ秒(00:02.034)

マテリアライズドビューに対する実行結果は以下の通り。

dvdrental=# SELECT * FROM mv_sales_by_store;
        store        |   manager    | total_sales
---------------------+--------------+-------------
 Woodridge,Australia | Jon Stephens |    30683.13
 Lethbridge,Canada   | Mike Hillyer | 47941777.91
(2)

時間: 1.210 ミリ秒

追加で50万レコード、10万レコード、初期状態(1万レコード程度)の結果を集計してみます。結果は以下の表のとおり。ビューの場合、レコード数に比例して実行時間が伸びているようです。特に100万レコードぐらいの規模となってくると、マテリアライズドビューの恩恵も大きくなっていました。他に気なったこととしては、マテリアライズドビューに関して、50万レコードのケースの際に、100万レコードと比較して実行時間が伸びてしまいました。実行計画のキャッシュの関係で遅くなったのでしょうか?(実行計画については分からない部分が多いので、勉強します)

レコード数 VIEW MATERIALIZED VIEW
1000000 2034.285 ms 1.21 ms
500000 1109.148 ms 6.094 ms
100000 255.665 ms 2.123 ms
初期状態(10000レコード程度) 22.561 ms 1.35 ms

編集後記


マテリアライズドビューを業務で普段使うことはありませんが、調べたりする中で面白そうな仕組みだなと思っていたので、今回記事にすることとしました。PostgreSQLの場合だと、自動リフレッシュがない(開発中の旨の記事を見た気がするけど今はどうなっているのだろう)ので、用途を選びそうではあります。また、実体データを持つことからもAWS環境で使用する場合にもその部分にお金がかかってきたりするので、注意が必要そうです。

Discussion

ログインするとコメントできます