【PostgreSQL】work_mem を変更すると Sort Method が external merge から quicksort に切り替わることを確認してみた
PostgreSQL の work_mem パラメータは、ソートやハッシュなどの処理で、使用できるメモリの上限を決める設定です。
この work_mem を変更すると、EXPLAIN ANALYZE の実行結果に表示される
Sort Method が external merge（ディスク使用）から quicksort（メモリ内処理）に切り替わることを確認してみました。
work_mem (integer)
一時ディスクファイルに書き込む前に、問い合わせ操作（ソートやハッシュなど）で使用される最大メモリ量を設定します。 この値が単位なしで指定された場合は、キロバイト単位であるとみなします。 デフォルト値は4MB(4MB)です。 複雑な問い合わせでは、同時に複数のソート操作とハッシュ操作が実行される可能性があります。 各操作は通常、データを一時ファイルに書き込む前にこの値で指定された量のメモリを使用できます。
PostgreSQL インストール
検証のための実行環境は EC2（AL2023, t3.micro）にインストールした PostgreSQL を使用しました。
EC2 に PostgreSQL をインストールする手順としては下記をご参照ください。
[ec2-user@ip-xx-xx-xx-xx ~]$ sudo -u postgres psql
psql (17.10)
Type "help" for help.
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 17.10 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
サンプルデータの準備
検証用にサンプル DB blog_sample_db を作ります。
-- サンプルデータベース作成
postgres=# CREATE DATABASE blog_sample_db;
CREATE DATABASE
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- サンプルテーブル作成
blog_sample_db=# CREATE TABLE sort_test (
id serial PRIMARY KEY,
random_value int
);
CREATE TABLE
-- データ挿入
blog_sample_db=# INSERT INTO sort_test (random_value)
SELECT (random() * 1000000)::int
FROM generate_series(1, 1000000);
INSERT 0 1000000
-- 実際に入っているデータは以下の形式
blog_sample_db=# select * from sort_test limit 5;
id | random_value
----+--------------
1 | 488340
2 | 793911
3 | 530536
4 | 980314
5 | 355090
(5 rows)
work_mem が小さい時の EXPLAIN ANALYZE
work_mem のデフォルト値は 4 MB になっています。
blog_sample_db=# SHOW work_mem;
work_mem
----------
4MB
(1 row)
work_mem (integer)
... デフォルト値は4MB(4MB)です。
これを一時的に 1 MB にします。
blog_sample_db=# SET work_mem = '1MB';
SET
blog_sample_db=# SHOW work_mem;
work_mem
----------
1MB
(1 row)
ソートが発生するクエリ文で EXPLAIN ANALYZE を実行。
-- クエリ内容 = 100万行のランダムな数字を、random_value が小さい順に並び替えて全部取得する
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM sort_test ORDER BY random_value;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Sort (cost=141431.84..143931.84 rows=1000000 width=8) (actual time=406.051..487.561 rows=1000000 loops=1)
Sort Key: random_value
Sort Method: external merge Disk: 17640kB
-> Seq Scan on sort_test (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.009..55.326 rows=1000000 loops=1)
Planning Time: 0.085 ms
Execution Time: 517.752 ms
(6 rows)
Sort Method: external merge Disk: 17640kB
...
Execution Time: 517.752 ms
注目する部分は Sort Method:〜 の部分です。
external merge Disk: 〜 の表記より、本ソートがディスク上で行われた、すなわち work_mem（1MB） よりソート対象のデータが大きいため、メモリではなくディスク上で処理が行われたことを示しています。
work_mem を大きくして EXPLAIN ANALYZE
続いて、work_mem を 256 MB と大きくします。
SET work_mem = '256MB';
SET
blog_sample_db=# SET work_mem = '256MB';
SET
blog_sample_db=# SHOW work_mem;
work_mem
----------
256MB
(1 row)
再度クエリを発行
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM sort_test ORDER BY random_value;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Sort (cost=114082.84..116582.84 rows=1000000 width=8) (actual time=279.835..417.252 rows=1000000 loops=1)
Sort Key: random_value
Sort Method: quicksort Memory: 48014kB
-> Seq Scan on sort_test (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.010..56.459 rows=1000000 loops=1)
Planning Time: 0.057 ms
Execution Time: 448.892 ms
(6 rows)
Sort Method: quicksort Memory: 48014kB
...
Execution Time: 448.892 ms
今度は quicksort Memory: 〜 の表示になりました。
work_mem が十分に大きいため、ディスク上で処理する必要がなく、メモリ上でソート処理が展開されたという意味になります。
ついでなので、ソートがディスク処理からメモリ処理に変わるタイミングはいつなのか調べてみます。
-- work_mem が 40 MB
blog_sample_db=# SET work_mem = '40MB';
SET
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM sort_test ORDER BY random_value;
QUERY PLAN
Sort (cost=114082.84..116582.84 rows=1000000 width=8) (actual time=400.804..476.611 rows=1000000 loops=1)
Sort Key: random_value
Sort Method: external merge Disk: 17616kB
-> Seq Scan on sort_test (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.009..55.971 rows=1000000 loops=1)
Planning Time: 0.044 ms
Execution Time: 507.451 ms
(6 rows)
-- work_mem が 50 MB
blog_sample_db=# SET work_mem = '50MB';
SET
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM sort_test ORDER BY random_value;
QUERY PLAN
Sort (cost=114082.84..116582.84 rows=1000000 width=8) (actual time=288.760..427.323 rows=1000000 loops=1)
Sort Key: random_value
Sort Method: quicksort Memory: 48014kB
-> Seq Scan on sort_test (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.009..56.285 rows=1000000 loops=1)
Planning Time: 0.049 ms
Execution Time: 460.421 ms
(6 rows)
上記より work_mem が 40〜50 MB 程度あたりがディスク処理からメモリ内処理に切り替わるタイミングだと把握できました。
終わりに
今回の検証結果をまとめると以下の通りです。
| work_mem | Sort Method | 使用量 | Execution Time |
|---|---|---|---|
| 1MB | external merge | Disk: 17640kB | 517.752 ms |
| 40MB | external merge | Disk: 17616kB | 507.451 ms |
| 50MB | quicksort | Memory: 48014kB | 460.421 ms |
| 256MB | quicksort | Memory: 48014kB | 448.892 ms |
work_mem を 40MB から 50MB に増やしたタイミングで、Sort Method が external merge（ディスク使用）からquicksort（メモリ内処理）に切り替わりました。一方で、処理時間に大きな差は見られませんでした。
このことから、ディスク処理になっていたとしても、それがボトルネックになっていない場合は、必ずしも work_mem を増やす必要はないかもしれません。
また、work_mem は「クエリ全体」ではなく「ソートやハッシュなどの処理1つあたり」の上限です。
例えば複雑なクエリでは複数のソート処理が走ることもあり、それぞれの処理にて work_mem が消費されます。
その結果、安易に work_mem 値を増やすことで、サーバー全体のメモリ不足に繋がる可能性もあります。
処理時間への影響とメモリ消費のリスク、両方を踏まえて調整するのが良さそうだと感じました。
本記事がどなたかのお役に立てば幸いです。
参考情報
