ディスク領域を利用したソートを抑制したい場合に設定する work_mem のサイズ

ディスク領域を利用したソート

ORDER BY などで発生するソート処理ですが、PostgreSQL ではディスク領域を利用して行われる場合と、work_mem で指定されたメモリ領域を利用して行われる場合があります。
ディスク領域を利用したソートは、メモリ領域を利用したソートに比べて、当然ながら低速で行われます。
そのため、ディスク領域を利用したソートを可能な限り発生させないことが SQL の高速化に繋がります。

ディスク領域を利用したソートを抑止する方法

ソート処理が行われる場合に、ソート処理の対象となるデータが work_mem を上限としたメモリサイズに収まりきらない場合、ディスク領域を利用したソートが行われます。
つまり、ディスク領域を利用したソートを抑止したい場合は、work_mem を増加させることが必要となります。

具体的に検証して確認

実際に SQL を実行し、ディスク領域を利用したソートを抑止してみます。

環境は

• PostgreSQL 16.0
• Ubuntu 22.04

です。

work_mem = 4MB の場合

pgbench のテストデータを利用してディスク領域を利用したソートが発生することを確認します。

postgres=# SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY bid;
                                                                  QUERY PLAN                                                    

--------------------------------------------------------------------------------------------------------------------------------
---------------
 Gather Merge  (cost=215219.54..409677.49 rows=1666666 width=97) (actual time=217.798..553.265 rows=2000000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=214219.52..216302.85 rows=833333 width=97) (actual time=201.916..245.684 rows=666667 loops=3)
         Sort Key: bid
         Sort Method: external merge  Disk: 71496kB
         Worker 0:  Sort Method: external merge  Disk: 69104kB
         Worker 1:  Sort Method: external merge  Disk: 69032kB
         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..41120.33 rows=833333 width=97) (actual time=1.613..64.029 rows=6
66667 loops=3)
 Planning Time: 1.778 ms
 Execution Time: 603.755 ms
(11 rows)

実行計画のどこを見れば、ディスク領域を利用したソートかメモリ領域を利用したソートかがを判別できるかと言いますと、「Sort Method: external merge Disk」の部分です。
該当する行が 3 行ありますので、ディスク領域を利用したソートが 3 回行われていることを表しています。
merge Disk に続く xxxkB の部分が、実際に利用されたディスク領域のサイズです。
おおよそ 70 MB 前後のディスク領域を利用してソートが行われたようです。

work_mem を増加させてディスク領域を利用したソートを抑止

前述のとおり、ソート時に利用されたディスク領域は 70 MB 前後でした。
そのため、work_mem には、70 MB よりも少し大きめのサイズを指定したくなりますが、それよりも多くの work_mem のサイズが必要となる場合があります。
具体的には、merge Disk に続く xxxkB のサイズの 2 から 3 倍のサイズが必要となる場合があります。
実際に work_mem を増加させて確認した結果を下記に記載します。

postgres=# SET work_mem = '100MB';
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pgbench_accounts ORDER BY bid;
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=169646.04..364103.99 rows=1666666 width=97) (actual time=233.718..510.305 rows=2000000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=3679 read=29220
   ->  Sort  (cost=168646.02..170729.35 rows=833333 width=97) (actual time=219.943..240.459 rows=666667 loops=3)
         Sort Key: bid
         Sort Method: quicksort  Memory: 101789kB
         Buffers: shared hit=3679 read=29220
         Worker 0:  Sort Method: quicksort  Memory: 98366kB
         Worker 1:  Sort Method: quicksort  Memory: 98351kB
         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..41120.33 rows=833333 width=97) (actual time=0.241..105.836 rows=666667 loops=3)
               Buffers: shared hit=3567 read=29220
 Planning Time: 0.199 ms
 Execution Time: 570.930 ms
(14 rows)

実行計画上から「Sort Method: external merge Disk」が消え、代わりに「Sort Method: quicksort Memory」が出力されていることを確認できます。
この「Sort Method: quicksort Memory」が、メモリ領域を利用したソートが行われたことを表していますので、無事にディスク領域を利用したソートを抑止できました。
また、quicksort Memory に続く xxxkB の部分が、実際に利用されたメモリ領域のサイズとなります。
上記の実行計画から、おおよそ 100 MB 前後のメモリサイズが利用されていることを確認できます。

work_mem を増加させる際の注意点

work_mem は 1 SQL ごとではなく、ソートやハッシュなどの 1 ノード（操作）ごとのメモリサイズの上限値となります。
SQL を実行した際に、仮にソート処理が 5 回実行されますと、最大で work_mem * 5 のメモリが消費されます。
work_mem を増加させすぎたことで、OS 側で利用できるメモリが少なくなり、その結果、システム遅延や OOM Killer の発生などに繋がる可能性がありますので、ご注意ください。

余談

前述の実行計画に「Sort Method: external merge Disk」や「Sort Method: quicksort Memory」の行が 3 行ほど出ている理由ですが、これはパラレルシーケンシャルスキャンが行われているためです。
「Workers Launched: 2」とある通り、2 プロセスを利用してシーケンシャルスキャンが行われ、それぞれでソートが行われますので、2 回のソートが実行されていることになります。
実行計画の先頭ノードは「Gather Merge」となっていますので、パラレルシーケンシャルスキャンの結果を合算するためのマージ処理でもソートが行われています。

まとめ

本記事では、ディスク領域を利用したソート処理を抑制するための方法として work_mem を増加させることを紹介いたしました。
ディスク領域を利用した処理は、システム全体の遅延の原因とまではいかないですが、SQL 単位で見た際のチューニングポイントとなっています。
できるだけディスク領域ではなくメモリで処理されるように、work_mem の仕様などは理解しておくのが推奨となります。