Help us understand the problem. What is going on with this article?

Aurora PostgreSQL 互換版(10.5)東京リージョン登場記念でパラレルスキャンを試す

More than 1 year has passed since last update.

この記事は インフラ勉強会 Advent Calendar 2018 14 日目の記事です。

昨日 13 日目は akiko-pusu さんでした。


AWS 東京リージョンに Aurora PostgreSQL 互換版(10.5)がようやく登場したので、パラレルスキャンを試してみました。

本家 PostgreSQL の実装のほうのパラレルスキャンです。Aurora 独自実装(これを書いている時点では MySQL 5.6 互換版のみに実装されている)のパラレルクエリのことではありません。

パラレルスキャンとは

テーブルスキャンを複数のプロセス(Worker プロセス)で実行するもので、PostgreSQL 9.6 で実装されました。PostgreSQL 9.6 ではシーケンシャルスキャン(インデックスを使わないスキャン)のみが対象でしたが、PostgreSQL 10 からはインデックスを使ったスキャンも対象になりました。

Aurora 独自実装のパラレルクエリとの違い

これを書いている時点では PostgreSQL 互換版には未実装ですが、Aurora 独自実装のパラレルクエリは、

  • 前面の SQL ノードではなく背後のストレージノードがデータのスキャン/フェッチを担当する
  • 共有バッファを経由せずにデータを取得する

点が本家 PostgreSQL の実装と異なります。

試してみた

書籍[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則の P.290 ~ の例に近い形で、1,000 万レコードのテーブルから、

  • Aurora PostgreSQL 9.6 デフォルト設定(パラレルスキャン無効)
  • Aurora PostgreSQL 9.6 max_parallel_workers_per_gather = 2(Worker プロセス 2 つ)
  • Aurora PostgreSQL 10.5 デフォルト設定(Worker プロセス 2 つ)

について、

  • EXPLAIN ANALYZE SELECT id, avg(value) FROM table_i GROUP BY id;(シーケンシャルスキャン)
  • EXPLAIN ANALYZE SELECT id, avg(value) FROM table_i WHERE id < 10 GROUP BY id;(インデックススキャン)

を試してみました(インスタンスタイプは db.r4.large)。

※すべての組み合わせではありません。

結果

以下のようになりました(単位:ms)。各々 1 回目がデータページが共有バッファに読み込まれていない状態、2 回目が読み込まれた状態です。

バージョン/パラレルスキャン有効・無効 シーケンシャル 1 回目 シーケンシャル 2 回目 インデックス 1 回目 インデックス 2 回目
9.6/無効 6,764.665 2,718.139 - -
9.6/有効 6,303.016 2,936.115 185,450.502 923.486
10.5/有効 5,707.766 2,921.991 42,457.903 493.760

シーケンシャルスキャンの場合、

  • データページが共有バッファに読み込まれていなければ、パラレルスキャンのほうが速い
  • データページが共有バッファに読み込まれていれば、わずかにパラレルスキャンのほうが遅い

という興味深い結果となりました。

おそらく、Worker プロセスの数が増えると結果が変わってくるでしょう。

インデックススキャンの場合、PostgreSQL 10 でかなり高速化しているのがわかります。

Aurora 独自実装のパラレルクエリではどうなるのか、楽しみです。


補足・テスト用テーブル定義とデータ生成

テストテーブル・データ
pq_test=> CREATE TABLE table_i (number SERIAL PRIMARY KEY, id INT, value INT);
CREATE TABLE
pq_test=> CREATE INDEX idx_id ON table_i (id);
CREATE INDEX
pq_test=> CREATE FUNCTION gen_data () RETURNS INTEGER AS '
pq_test'> BEGIN
pq_test'>      FOR i IN 1..10000000 LOOP
pq_test'>          EXECUTE ''INSERT INTO table_i (id, value) VALUES((random() * 10000)::int % 100, (random() * 100000000)::int % 10000)'';
pq_test'>      END LOOP;
pq_test'>
pq_test'>      RETURN 1;
pq_test'> END;
pq_test'> ' LANGUAGE 'plpgsql';
CREATE FUNCTION
pq_test=> SELECT gen_data();
 gen_data
----------
        1
(1 row)
hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。 https://hmatsu47.qrunch.io/
https://hmatsu47.hatenablog.com/
alieaters
Alibaba Cloudを上手に使うためのノウハウの共有を目的としたコミュニティ
https://www.alieaters.com
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした