この記事は インフラ勉強会 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)