🐐

パラレルクエリの有効利用の仕方

2023/09/21に公開

PostgreSQL

Aurora PostgreSQLのチューニングを行ったときに効果的だったことを紹介します。
今回は特に効果が高かったパラレルクエリについてです。

利用したPosgreSQLは13.7です。

要約

デフォルトだとパラレルクエリの上限が低い
- 設定変更やhint句で上限を上げると良い
パラレルクエリを使えるようにする書き方
- UPDATE文のままだと効かない
  - 工夫することで効くようにすることが可能
- hint句も活用

最初に

本記事ではチューニングに焦点を当てています。
なので、パラレルクエリ自体の正確な詳細やhint句などは以下のように良記事がいろいろありますので、そちらでご確認ください。

PostgreSQL新機能-第4回パラレルクエリ
 若手エンジニアのブログ-パラレルクエリとは
 PostgreSQL技術インデックス-pg_hint_planで実行計画を制御する

パラレルクエリが効果的な対象

パラレルクエリは並行実行なので、単一実行のときとは別に余計な処理が必要です（「並行で実行するための準備」や「並行実行された結果を取りまとめる処理」など）。
このため、処理量が少ない場合にはかえって性能が劣化する可能性があります。

このことから、パラレルクエリが効果的なのはSQLの実行が数分以上かかるような重いSQLです。

ちなみに、私が担当したシステムでは1時間以上かかるSQLがあり、これがチューニング対象でした。

パラレルクエリの設定に注意

Aurora PostgreSQLはデフォルトで利用できる設定値になっています。
（通常のPostgreSQLもおそらく同じ）
設定を何もいじっていなければ使えると思って大丈夫。

ただし、注意が必要な点があります。
具体的には以下のパラメータでパラレルクエリが使えるかどうかが決まります。

項目	内容
max_worker_processes	並行実行できる最大数（パラレルクエリ以外も含む）。デフォルト8
max_parallel_workers	パラレルクエリでの並行実行最大数。デフォルト8
max_parallel_workers_per_gather	1つのパラレルクエリでの並行実行最大数。デフォルト2

パラメータは以下のコマンドで確認できます。
（パラメータ名に"all"を指定すれば全パラメータを確認できます。）
SHOW [パラメータ名]
max_worker_processesの場合なら以下です。
SHOW max_worker_processes

上記のデフォルト設定ををわかりやすくいうと「全体の最大は8でパラレルクエリ全体の最大も8、だけど１クエリでの最大は2」なので、1SQLのパラレル数は2で実行されます。
遅いSQLが特定できている場合、パラレル数2だともの足りません。

1SQLのパラレル数の最大を増やす方法

ということで、遅いSQLに対しては以下のどちらかでパラレル数をもっと増やして実行できるようにします。

①max_parallel_workers_per_gatherの設定値を8にする

1クエリの最大パラレル数を8に変更する方法です。
（別に8ではなくてもいいのですが、最大の性能改善を求めて最大値指定を例にしています）

設定値の方法はいくつかあるので詳しくは以下を参照ください。
PostgreSQL公式。

なお、上記記事の中で設定ファイルを使う方法がありますが、Auroraの場合にはRDSのパラメータグループを使うことになります。
そちらの資料はAWS公式

ここで紹介されている設定方法は適用範囲が広いです。
設定ファイル（パラメータグループ）だとシステム全体ですし、ストアドプロシージャ内で以下のようにした場合はストアド全体が適用範囲になります。

SET LOCAL max_parallel_workers_per_gather TO 8;

このように他のSQLにも適用されるので、全体で性能改善する可能性があります。
一方で、SQLが並行実行されているような状況では、1つのSQLで最大の8で実行されると他のSQLではパラレルクエリが適用されない可能性があります。
このあたりを考えて設定値を適用する必要があります。

私の担当システムでは特定のSQLのみへの適用にしたかったので、上記方法ではなく、次のhint句を採用しました。

②SQLにhint句をつけてパラレル数を指定する

こちらは対象のSQL実行時にのみパラレル数の最大を増やす方法です。
チューニングで遅いSQLが特定できている場合にはこちらを使うほうが全体への影響を抑えて性能改善できる方法だと思います。

パラレルクエリを利用するhint句の書き方

以下のような単純なSQLの場合は以下のように書きます。

SELECT /*+ HashJoin(T1 T2) Parallel(T1 8 hard) */ * FROM table1 AS T1
JOIN table2 AS T2
  ON T1.col1 = T2.col1;

ここにはいくつかポイントがあるので箇条書きで。

hint句の書き方
- hint句は上記のように/*+ (指定内容) */という書き方になります。
- SELECTの後に書いてあるのは、ストアドプロシージャ内のSQLだとこう書かないと効かないからです。
  - pgAdmin等で実行する場合はSELECT文の前に書いても問題なく、他の記事だとそう書かれていることが多いので、ストアドプロシージャのSQLの場合はご注意ください。
HashJoin
- パラレルを生かす場合、HashJoinがもっとも効果的でした。
  - この指定がない場合はNestedLoopが選択されることが多く、これだとパラレルクエリにならないようでした。
  - JOINにはMergeJoinもあり、こちらも試しましたがパラレル数に上限があるようで8指定しても4しか使わない、などで性能向上も限定的でした。
Parallel
- ここでパラレル数を指定しています。
- テーブル指定ですが、T1・T2のどちらを試しても私の場合には特に差異がありませんでした。
- "hard"をつけると強制的にパラレル数を8で実行します。
  - これがないとPostgreSQLが最適と考えるパラレル数での実行になるのですが、どうしても2や4などで実行し、8で実行したときよりも遅かったため強制指定にしています。

パラレルクエリを使えるようにするには？

本記事、ここからがポイントです。

hint句で示したSQLは非常に単純でしたが、実際のSQLはもっと複雑なことが多いですよね。
実際、私がチューニングしたSQLは時系列データの前後を参照して更新を行うような１SQLでした。
適当に書いたSQLですが、イメージはこんな感じです。
（あくまでイメージで内容的な意味はありません）

UPDATE table1 AS T1
   SET val1 = T4.val1
   FROM (
       SELECT T2.col1, MIN(T3.val1)
         FROM table2 AS T2
         JOIN table3 AS T3
           ON T2.col1 = T3.col1
        WHERE T2.time_a < T3.time_a
        GROUP BY T2.col1
   ) T4
   WHERE T1.col1 = T4.col1;

もちろんWith句を使って以下のように書くこともできます。

With T4 AS (
    SELECT T2.col1 AS col1, MIN(T3.val1) AS val1
        FROM table2 AS T2
        JOIN table3 AS T3
        ON T2.col1 = T3.col1
    WHERE T2.time_a < T3.time_a
    GROUP BY T2.col1
)
UPDATE table1 AS T1
   SET val1 = T4.val1
   FROM T4
   WHERE T1.col1 = T4.col1;

いずれにしろ、ベースはUPDATE文でその値を取得するためにSELECT文が入れ子になっているような形です。

ですが、このままだとパラレルクエリは使えません。

なぜかといえば、パラレルクエリが利用できる条件の一つが「参照処理またはCREATE系の処理」だからです。

参照処理、つまりSELECT文です。
上記SQLも、処理に時間がかかるのはSELECTのところですが、全体としてはUPDATE文なのでパラレルクエリは使えません。

上記SQLにはもう一点問題があります。
SELECT結果をT4としてテーブルとして利用していますが、このT4にはインデックスが効きません。

上記SQLでは簡略化していますが、WHERE句の条件が実際にはもっとあったので、この結合処理にインデックスが効かないのも遅い原因の一つでした。

パラレルクエリを利用する書き方

最初の修正ＳＱＬ

ではどうしたかというと、まずは以下のようにしました。

CREATE TEMPORARY TABLE AS T4 AS 
    SELECT T2.col1, MIN(T3.val1)
        FROM table2 AS T2
        JOIN table3 AS T3
        ON T2.col1 = T3.col1
    WHERE T2.time_a < T3.time_a
    GROUP BY T2.col1;

CREATE INDEX index1 ON T4 (col1);

UPDATE table1 AS T1
   SET val1 = T4.val1
   FROM T4
   WHERE T1.col1 = T3.col1;

処理に時間がかかる部分を一度テーブルとして作成することにしました。
こうすることでインデックス作成をすることもできるようになりました。
(TEMPORARYにしていますが、後始末（DROP）をしさえすればTEMPORARYでなくても構いません)

これでUPDATE時のWHERE句での検索が早くなりました。

誤算

ただ、ここで一つ誤算がありました。
パラレルクエリはCREATE系なら効くはずで、このSQLも適用条件は満たしており、自動的にパラレルクエリを適用してくれるだろうと期待していました。
ですが、PostgreSQLはJOINをNestedLoopで実行してしまい、パラレルクエリで実行してくれませんでした。

最終的なＳＱＬ

ということで、最終的には以下のようなSQLになりました。

CREATE TEMPORARY TABLE /*+ HashJoin(T2 T3) Parallel(T2 8 hard) */ AS T4 AS 
    SELECT T2.col1, MIN(T3.val1)
        FROM table2 AS T2
        JOIN table3 AS T3
        ON T2.col1 = T3.col1
    WHERE T2.time_a < T3.time_a
    GROUP BY T2.col1;

CREATE INDEX index1 ON T4 (col1);

UPDATE table1 AS T1
   SET val1 = T4.val1
   FROM T4
   WHERE T1.col1 = T3.col1;

hint句で強制的にパラレルクエリを使うように指定しました。インデックスも効いているので、これで当初のＳＱＬから考えるとかなりの性能改善になりました。

想定コストと実コスト

想定コストと実コストを確認してみたところ、以下のようなことがわかりました。

想定コストや実コストを確認するのはＳＱＬの前に以下のコマンドをつけて実行します。
EXPLAIN ANALYZE (ＳＱＬ文)

以下が比較した結果です。

ＳＱＬ種類	JOIN方式	想定コスト	実コスト
ヒント句なしＳＱＬ	NestedLoop	低(=早いと推定)	高（遅い)
ヒント句ありＳＱＬ	HashJoin	高（NestedLoopと比べ一桁大きい）	低（早い）

どうもPostgreSQLは、本来パラレルクエリを効かせて早く処理できるSQLはかなり高コストと見積もるようです。
結果としてPostgreSQLにお任せだとパラレルクエリをうまく活用しきれていないようです。

このことから、単純にパラレルクエリが効く書き方をするだけではダメで、実際にパラレルクエリで実行されているか確認し、どうしても改善したい箇所についてはhint句を使うのも大事です。