【PostgreSQL】パラレルクエリについて

2023年12月21日 2023年12月16日

1 パラレルクエリについて
2 パラレルクエリを安全に実行できる関数の確認
3 パフォーマンスが上がるかを確認してみる
4 パラレルクエリで期待した効果がでないとき

パラレルクエリについて

パラレルクエリはPostgreSQL9.6から実装された、検索パフォーマンスの向上を目的とした機能です。
本来1クエリ1プロセスで処理するところ、パラレルクエリは1クエリを複数のプロセスで分担して並列処理することにより、およそ2倍以上の速度向上が見込めます。

その特性上、PostgreSQLが動作するサーバはマルチコア（複数のCPUを持つ）環境である必要があります。
膨大なレコード数を持つテーブルに対して特に有効で、ハードウェアレベルの恩恵を存分に預かります。

パラレルクエリの実行にはいくつか条件があり、SELECTのような参照を行うクエリであることであることや、パラレルクエリを安全に実行できない関数が使われていないこと、同時実行するプロセス数に猶予があるなどがあります。

パラレルクエリをサクッと実行してみる

実行環境はoss-db.jpを参考にしました。
以下の検証は「pgbench -i -s 100」で上記参考元よりやや多めの行数で行っています。

[postgres@localhost ~]$ pgbench -i -s 100
[postgres@localhost ~]$ psql
psql (10.6)
"help" でヘルプを表示します。

postgres=# explain analyze verbose  select * from pgbench_accounts where bid = 1;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..227585.03 rows=105667 width=97) (actual time=0.246..1095.111 rows=100000 loops=1)
   Output: aid, bid, abalance, filler
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..216018.33 rows=44028 width=97) (actual time=0.021..1046.930 rows=33333 loops=3)
         Output: aid, bid, abalance, filler
         Filter: (pgbench_accounts.bid = 1)
         Rows Removed by Filter: 3300000
         Worker 0: actual time=0.025..1036.795 rows=488 loops=1
         Worker 1: actual time=0.029..1037.419 rows=488 loops=1
 Planning time: 0.056 ms
 Execution time: 1098.625 ms

"Parallel Seq Scan"と表示されているので、パラレルクエリとして実行できていることが確認できます。

パラレルクエリの実行判断に関係するパラメータ

パラメータ	デフォルト	内容
force_parallel_mode	off	パラレルクエリ強制パラメータ。offの場合、有効と判断されたときのみパラレルクエリが実行されるが、Onにするとすべてパラレルクエリとして実行するようになる。※ただしパラレルクエリが実行できるクエリに限る
min_parallel_index_scan_size	512KB	パラレルクエリの実行を検討する最小インデックスサイズ。
min_parallel_table_scan_size	8MB	パラレルクエリの実行を検討する最小テーブルサイズ。

※すべてSETによる手軽な確認が可能。

起動するパラレルワーカー数に関係するパラメータ

パラメータ	デフォルト	内容
max_worker_processes	8	バックグラウンドプロセスの最大数、これを超えてパラレルワーカーを起動することはできない。 ※パラレルワーカーの最大数を指定するパラメーターではない点に注意
max_parallel_workers	8	パラレルクエリ用に起動するワーカープロセスの最大数。これを超えてパラレルワーカーを起動することはできない。
max_parallel_workers_per_gather	2	1つGatherノードで起動できる最大パラレルワーカー数。必ず全て割り当てられるわけではない。

※すべてSETによる手軽な確認が可能。

十分にmax_worker_processes、max_parallel_workersが割り当てられている場合

postgres=# explain analyze verbose  select * from pgbench_accounts where bid = 1;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..227585.03 rows=105667 width=97) (actual time=0.246..1095.111 rows=100000 loops=1)
   Output: aid, bid, abalance, filler
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..216018.33 rows=44028 width=97) (actual time=0.021..1046.930 rows=33333 loops=3)
         Output: aid, bid, abalance, filler
         Filter: (pgbench_accounts.bid = 1)
         Rows Removed by Filter: 3300000
         Worker 0: actual time=0.025..1036.795 rows=488 loops=1
         Worker 1: actual time=0.029..1037.419 rows=488 loops=1
 Planning time: 0.056 ms
 Execution time: 1098.625 ms

「Workers Planned: 2」で実行を計画し、「Workers Launched: 2」で計画通り2つのパラレルワーカーによって処理できたことがわかります。

max_worker_processes、max_parallel_workersが不足している場合

postgres=# set max_parallel_workers=0;
SET
postgres=# explain analyze verbose  select * from pgbench_accounts where bid = 1;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..227585.03 rows=105667 width=97) (actual time=0.441..942.240 rows=100000 loops=1)
   Output: aid, bid, abalance, filler
   Workers Planned: 2
   Workers Launched: 0
   ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..216018.33 rows=44028 width=97) (actual time=0.011..929.282 rows=100000 loops=1)
         Output: aid, bid, abalance, filler
         Filter: (pgbench_accounts.bid = 1)
         Rows Removed by Filter: 9900000
 Planning time: 0.094 ms
 Execution time: 946.525 ms

「Workers Planned: 2」で実行を計画したものの、「Workers Launched: 0」でパラレルワーカーによる並行処理ができなかったことが分かります。

パラレルクエリを安全に実行できる関数の確認

パラレルクエリを安全に実行できる関数であるかどうかは、システムカタログpg_procのproparallelにて確認が可能です。

パラメータ	内容
s	パラレルクエリを安全に実行できる
r	パラレルクエリを実行可能だが、並列グループリーダに制限される。並列ワーカプロセスはこれらの関数を呼び出すことはできない　※要確認
u	パラレルクエリを安全に実行でない関数。この関数がクエリに含まれるとパラレルクエリとして実行されない

安全な関数にパラレルクエリを実行できる関数（s）

postgres=# select proname ,proparallel from pg_proc;
                   proname                    | proparallel
----------------------------------------------+-------------
 boolin                                       | s
 boolout                                      | s
 byteain                                      | s

パラレルクエリを安全に実行できる関数の割合は、pg_procの調べでは9割方該当し、通常業務で扱う関数であればほぼ当てはまることと思います。

そのためパラレルクエリを利用することでパフォーマンスが向上する可能性のあるSQLがあったとして、proparallelがsでなかったから実行できなかった、というのはレアケースに思います。

並列グループリーダーにのみ実行が限定される関数（r）

postgres=#  select proname,proparallel from pg_proc  where proparallel='r';
                   proname                    | proparallel
----------------------------------------------+-------------
 current_query                                | r
 age                                          | r
 now                                          | r
 statement_timestamp                          | r
 random                                       | r

PostgreSQL公式Webサイト曰く、「並列グループリーダーのみ実行できる、並列ワーカプロセスは実行できない。」とありますが、そもそもグループリーダー、ワーカプロセスの説明が不足していて理解が難しいパラメータです。

該当する関数の頭文字には「pg_stat」から始まるものが多く、それらはPostgreSQLの実行中に収集した統計情報を参照するための関数となります。

例えば「pg_stat_get_tuples_deleted」は引数にoid（テーブルID）を渡すと、該当テーブルでDELETEされた行数が取得できます。

なんとかパッと見でわかりやすいのはnowやrandomでしょうか、どちらにしてもユーザーが作成したテーブルの行を集約して～というタイプの関数ではないようです。

パラレルクエリとして実行できない関数（u）

postgres=# select proname,proparallel from pg_proc where proparallel = 'u';
                proname                 | proparallel
----------------------------------------+-------------
 brin_summarize_new_values              | u
 brin_summarize_range                   | u
 brin_desummarize_range                 | u
 lo_import                              | u

該当する関数を使用すると、パラレルクエリを実行しなくなります。
pg_procで検索すると、rと同様に馴染みがない関数ばかりですが、sequence周りのsetvalやnextvalは、多少わかりやすいかと思います。

どのみちこちらも大量のデータをどうにかするタイプの関数ではなさそうです。

パフォーマンスが上がるかを確認してみる

以下はCPU2コア環境でmax_parallel_workers_per_gatherを0⇒2と変更してみました。
想定通りいけば、2の時は2倍になるはずです。

max_parallel_workers_per_gatherが0のとき

postgres=# set max_parallel_workers_per_gather=0;
SET
postgres=# explain analyze verbose select * from pgbench_accounts where bid = 1;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.pgbench_accounts  (cost=0.00..288935.00 rows=105667 width=97) (actual time=0.151..1665.167 rows=100000 loops=1)
   Output: aid, bid, abalance, filler
   Filter: (pgbench_accounts.bid = 1)
   Rows Removed by Filter: 9900000
 Planning time: 0.057 ms
 Execution time: 1672.525 ms
(6 行)

パラレルクエリとして処理させず、単純に1.6秒ほどかかることがわかります。

max_parallel_workers_per_gatherが2のとき

postgres=# set max_parallel_workers_per_gather=2;
SET
postgres=# explain analyze verbose select * from pgbench_accounts where bid = 1;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..227585.03 rows=105667 width=97) (actual time=0.544..596.550 rows=100000 loops=1)
   Output: aid, bid, abalance, filler
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.pgbench_accounts  (cost=0.00..216018.33 rows=44028 width=97) (actual time=0.022..563.522 rows=33333 loops=3)
         Output: aid, bid, abalance, filler
         Filter: (pgbench_accounts.bid = 1)
         Rows Removed by Filter: 3300000
         Worker 0: actual time=0.020..569.191 rows=46970 loops=1
         Worker 1: actual time=0.022..567.539 rows=48638 loops=1
 Planning time: 0.061 ms
 Execution time: 603.487 ms
(12 行)

パラレルワーカー2つで並列処理した結果、2倍（以上）の速度で完了しました。