第1部 はじめに
1. はじめに
本記事は、pgbenchの標準テーブルを使って「遅いSQL」を意図的に作り、EXPLAIN ANALYZEで原因を特定しながら段階的に解決していく実践記事です。
「インデックスを貼ったら速くなった」という成功体験だけでなく、「インデックスを貼っても速くならないケース」 を実測で確認し、その先の解決策（クエリの書き換え）まで手を動かして進めます。
また、検証環境にはPostgreSQL16 / 17 / 18 の3バージョンを同一インスタンス上に構築し、随所で実行計画の出力差分も観察します。
1.1 この記事のコンセプト
この記事の軸は、手を動かして実行計画を確認しながら、性能問題の解決方法の基礎を学ぶことです。
「遅い」の原因はひとつではありません。そして解決の手立ても、SQLの書き換えだけではありません。この記事では、ひとつの遅いクエリを起点に、解決のアプローチを次の3つの層で順番に試していきます。
- 設計：インデックス、クエリの書き換え、パーティション
- 運用：dead tupleの掃除（VACUUM）と監視
- インフラ：メモリ・WAL・I/Oなどの設定、そしてディスク（EBS）そのものの増強
それぞれの段階で「効いたもの」だけでなく「効かなかったもの」も実測で示します。チューニングで大切なのは、勘で設定をいじることではなく、まず数字を見て、ボトルネックがどの層にあるかを見極めることだからです。
あわせて、検証環境にはPostgreSQL 16 / 17 / 18 の3バージョンを同一インスタンス上に構築し、随所で実行計画やVACUUMの挙動の違いも観察します。
1.2 検証環境
| 項目 | 内容 |
|---|---|
| OS | AlmaLinux 10.2（カーネル 6.12） |
| インスタンスタイプ | m7i.large（2 vCPU、8GiB メモリ） EBS 50GB gp3 |
| PostgreSQL | 16.14（port 5416）/ 17.10（port 5417）/ 18.4（port 5418） |
| ベンチマークツール | pgbench（スケールファクタ 100） |
PostgreSQL18 のみ io_method = io_uring を設定しています（AlmaLinux 10系はカーネル6.12でio_uringに対応しています）。
環境構築の手順は記事末尾の「おまけ：検証環境の構築手順」にまとめています。同じ環境を再現したい方はそちらを参照してください。
m7i.largeはバースト型ではないため安定した計測が可能ですが、本記事の数値はあくまで一例です。環境やキャッシュ状態によって結果は変動します。
2. 検証データの準備
pgbenchのスケールファクタ100で初期化しています。テーブル構成は以下の通りです。
| テーブル | 行数 | サイズ | 主キー |
|---|---|---|---|
| pgbench_accounts | 10,000,000 | 1,495 MB | aid |
| pgbench_tellers | 1,000 | 120 kB | tid |
| pgbench_branches | 100 | 56 kB | bid |
# データベース作成と初期化（PostgreSQL18の例）
$ sudo -u postgres psql -p 5418 -c "CREATE DATABASE pgbench;"
$ sudo -u postgres /usr/pgsql-18/bin/pgbench -i -s 100 -p 5418 pgbench
注目すべき点は、pgbench_accountsのbid列にはインデックスがないことです。主キー（aid）以外の列で検索すると、1,000万行のSeq Scanが発生します。
第2部 設計で解決する ─ クエリとインデックス
ここからは、SQLとテーブル設計の工夫で「遅い」を解決していきます。OSやハードウェアには手を入れず、PostgreSQLの中だけでどこまで戦えるかを見ていきます。
1. 症状①：支店の口座一覧が遅い（Seq Scan）
1.1 問題のSQL
「支店ID = 1 の口座を残高の大きい順に50件表示する」という、業務アプリでよくある一覧画面相当のSQLです。
SELECT aid, bid, abalance
FROM pgbench_accounts
WHERE bid = 1
ORDER BY abalance DESC
LIMIT 50;
1.2 EXPLAIN ANALYZEで実行計画を確認する
PostgreSQL18で実行した結果です。
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
EXPLAIN ANALYZE
SELECT aid, bid, abalance
FROM pgbench_accounts
WHERE bid = 1
ORDER BY abalance DESC
LIMIT 50;
EOF
実行計画（PostgreSQL18・インデックスなし）
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=220402.54..220408.37 rows=50 width=12) (actual time=9244.869..9245.827 rows=50.00 loops=1)
Buffers: shared hit=74 read=163935
-> Gather Merge (cost=220402.54..232670.31 rows=105333 width=12) (actual time=9244.868..9245.820 rows=50.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=74 read=163935
-> Sort (cost=219402.52..219512.24 rows=43889 width=12) (actual time=9237.445..9237.449 rows=50.00 loops=3)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=74 read=163935
Worker 0: Sort Method: top-N heapsort Memory: 26kB
Worker 1: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..216018.33 rows=43889 width=12) (actual time=1.084..9232.802 rows=33333.33 loops=3)
Filter: (bid = 1)
Rows Removed by Filter: 3300000
Buffers: shared read=163935
Planning:
Buffers: shared hit=52 read=18 dirtied=1
Planning Time: 12.737 ms
Execution Time: 9247.161 ms
(20 rows)
実行時間は 約9.2秒 でした。注目すべき行は以下です。
-> Parallel Seq Scan on pgbench_accounts （略）
Filter: (bid = 1)
Rows Removed by Filter: 3300000
Rows Removed by Filter: 3300000 は、並列ワーカーあたり330万行（3並列で合計約990万行）を読んでから捨てたことを示しています。1,000万行を全部読んで、使うのは50行だけ。この無駄がSeq Scanの遅さの正体と考えられます。
1.3 PostgreSQL16 / 17 でも同様に確認する
同じSQLをPostgreSQL16と17でも実行しました。
実行計画（PostgreSQL16・インデックスなし）
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=218384.03..218389.87 rows=50 width=12) (actual time=9247.057..9247.969 rows=50 loops=1)
-> Gather Merge (cost=218384.03..227977.27 rows=82222 width=12) (actual time=9247.055..9247.963 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=217384.01..217486.79 rows=41111 width=12) (actual time=9237.676..9237.680 rows=50 loops=3)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Worker 0: Sort Method: top-N heapsort Memory: 26kB
Worker 1: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..216018.33 rows=41111 width=12) (actual time=1.401..9234.507 rows=33333 loops=3)
Filter: (bid = 1)
Rows Removed by Filter: 3300000
Planning Time: 6.145 ms
Execution Time: 9248.368 ms
(14 rows)
実行計画（PostgreSQL17・インデックスなし）
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=218476.32..218482.15 rows=50 width=12) (actual time=9246.148..9246.238 rows=50 loops=1)
-> Gather Merge (cost=218476.32..228717.80 rows=87778 width=12) (actual time=9246.146..9246.231 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=217476.29..217586.02 rows=43889 width=12) (actual time=9240.680..9240.684 rows=50 loops=3)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Worker 0: Sort Method: top-N heapsort Memory: 26kB
Worker 1: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..216018.33 rows=43889 width=12) (actual time=0.753..9236.899 rows=33333 loops=3)
Filter: (bid = 1)
Rows Removed by Filter: 3300000
Planning Time: 7.999 ms
Execution Time: 9246.942 ms
(14 rows)
| バージョン | Execution Time | 実行計画 |
|---|---|---|
| PostgreSQL16 | 9248.368 ms | Parallel Seq Scan |
| PostgreSQL17 | 9246.942 ms | Parallel Seq Scan |
| PostgreSQL18 | 9247.161 ms | Parallel Seq Scan |
3バージョンともほぼ同じ約9.2秒でした。Seq Scanの遅さはバージョンでは解決しないことが確認できます。
1.4 バージョン間の出力差分に注目する
実行時間は同じでも、EXPLAIN ANALYZEの出力には違いがあります。
PostgreSQL18ではBuffersが自動表示される
PostgreSQL16 / 17 では EXPLAIN (ANALYZE, BUFFERS) と明示しないと表示されなかったBuffers情報が、PostgreSQL18ではANALYZEオプションだけで自動的に表示されます。
Buffers: shared hit=74 read=163935
read=163935 はブロック数なので、約1.3GB（163935 × 8kB）をディスクから読んだことが分かります。
PostgreSQL18では行数が小数で表示される
PostgreSQL16/17: rows=33333
PostgreSQL18 : rows=33333.33
並列ワーカーあたりの平均行数が、PostgreSQL18から小数点付きで表示されるようになりました。
2. 解決①：インデックスを作成する
2.1 インデックス作成
bid 列にインデックスを作成します。
$ sudo -u postgres psql -p 5418 -d pgbench -c "CREATE INDEX idx_accounts_bid ON pgbench_accounts(bid);"
2.2 同じSQLを再実行する
実行計画（PostgreSQL16・インデックスあり）
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6205.86..6211.61 rows=50 width=12) (actual time=23.217..24.277 rows=50 loops=1)
-> Gather Merge (cost=6205.86..12880.34 rows=58039 width=12) (actual time=23.215..24.271 rows=50 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=5205.85..5350.94 rows=58039 width=12) (actual time=21.519..21.522 rows=50 loops=2)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Worker 0: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts (cost=0.43..3277.83 rows=58039 width=12) (actual time=0.038..14.654 rows=50000 loops=2)
Index Cond: (bid = 1)
Planning Time: 0.332 ms
Execution Time: 24.317 ms
(12 rows)
実行計画（PostgreSQL17・インデックスあり）
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6558.35..6564.10 rows=50 width=12) (actual time=24.781..25.736 rows=50 loops=1)
-> Gather Merge (cost=6558.35..13683.86 rows=61961 width=12) (actual time=24.779..25.729 rows=50 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=5558.34..5713.24 rows=61961 width=12) (actual time=22.113..22.117 rows=50 loops=2)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Worker 0: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts (cost=0.43..3500.04 rows=61961 width=12) (actual time=0.039..15.588 rows=50000 loops=2)
Index Cond: (bid = 1)
Planning Time: 0.335 ms
Execution Time: 25.774 ms
(12 rows)
実行計画（PostgreSQL18・インデックスあり）
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6558.35..6564.05 rows=50 width=12) (actual time=27.105..28.212 rows=50.00 loops=1)
Buffers: shared hit=76 read=1727
-> Gather Merge (cost=6558.35..18563.33 rows=105334 width=12) (actual time=27.104..28.205 rows=50.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=76 read=1727
-> Sort (cost=5558.34..5713.24 rows=61961 width=12) (actual time=24.405..24.408 rows=50.00 loops=2)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=76 read=1727
Worker 0: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts (cost=0.43..3500.04 rows=61961 width=12) (actual time=0.045..16.048 rows=50000.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=69 read=1727
Planning:
Buffers: shared hit=82 read=1
Planning Time: 0.343 ms
Execution Time: 28.245 ms
(19 rows)
2.3 Before / After 比較
| バージョン | Before（Seq Scan） | After（Index Scan） | 改善率 |
|---|---|---|---|
| PostgreSQL16 | 9248.368 ms | 24.317 ms | 約380倍 |
| PostgreSQL17 | 9246.942 ms | 25.774 ms | 約359倍 |
| PostgreSQL18 | 9247.161 ms | 28.245 ms | 約327倍 |
3バージョンとも Parallel Index Scan に変わり、20ms台へと劇的に改善しました。
PostgreSQL18のBuffers出力を見ると、改善の理由がブロック数で分かります。
| Before | After | |
|---|---|---|
| Buffers read | 163,935 ブロック（約1.3GB） | 1,727 ブロック（約13MB） |
読み込み量が 約95分の1 になっています。インデックスとは「読む量を減らす技術」であることが数字で確認できます。
なお、PostgreSQL18の実行計画には Index Searches: 1 という行が表示されています。これはPostgreSQL18で追加された出力項目で、インデックスを何回走査したかを示します。
バージョン間のms単位の差（24ms / 25ms / 28ms）はキャッシュ状態の影響範囲内であり、性能差を示すものではないと考えられます。
2.4 PostgreSQL18のスキップスキャン ─ 同じインデックスでも読み方が変わる
ここで、PostgreSQL18で追加された B-treeインデックスのスキップスキャン を確認します。複合インデックスの先頭列を検索条件に含めない場合でも、インデックスを効率的に使えるようになる機能です。
まず複合インデックスを作成します。
$ sudo -u postgres psql -p 5418 -d pgbench -c "CREATE INDEX idx_accounts_bid_abalance ON pgbench_accounts(bid, abalance);"
このインデックスの先頭列は bid です。それに対して、先頭列を使わずに2列目のabalanceだけで検索してみます。
SELECT aid, bid, abalance
FROM pgbench_accounts
WHERE abalance > 4900;
なお、初期化直後のpgbench_accountsは全行 abalance = 0 のため、事前にpgbenchを30秒実行して残高をばらけさせています。
$ sudo -u postgres /usr/pgsql-18/bin/pgbench -c 4 -T 30 -p 5418 pgbench
PostgreSQL17と18で同じSQLを実行した結果です。
実行計画（PostgreSQL16・先頭列なしの複合インデックス検索）
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_accounts_bid_abalance on pgbench_accounts (cost=0.43..110013.65 rows=1 width=12) (actual time=0.404..47.955 rows=531 loops=1)
Index Cond: (abalance > 4900)
Buffers: shared hit=447 read=8716 dirtied=323 written=4046
Planning:
Buffers: shared hit=93 read=4
Planning Time: 0.794 ms
Execution Time: 48.042 ms
(7 rows)
実行計画（PostgreSQL17・先頭列なしの複合インデックス検索）
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_accounts_bid_abalance on pgbench_accounts (cost=0.43..109996.50 rows=1 width=12) (actual time=0.440..50.443 rows=478 loops=1)
Index Cond: (abalance > 4900)
Buffers: shared hit=420 read=8690 dirtied=293 written=4314
Planning:
Buffers: shared hit=93 read=4
Planning Time: 7.991 ms
Execution Time: 50.543 ms
(7 rows)
実行計画（PostgreSQL18・先頭列なしの複合インデックス検索）
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_accounts_bid_abalance on pgbench_accounts (cost=0.43..450.45 rows=1 width=12) (actual time=0.674..4.828 rows=470.00 loops=1)
Index Cond: (abalance > 4900)
Index Searches: 101
Buffers: shared hit=567 read=258 dirtied=271
Planning:
Buffers: shared hit=75 read=22
Planning Time: 7.146 ms
Execution Time: 4.888 ms
(8 rows)
| PostgreSQL16 | PostgreSQL17 | PostgreSQL18 | |
|---|---|---|---|
| 実行計画 | Index Scan | Index Scan | Index Scan（スキップスキャン） |
| Buffers read | 8,716 | 8,690 | 258 |
| Index Searches | ─ | ─ | 101 |
| Execution Time | 48.042 ms | 50.543 ms | 4.888 ms |
3バージョンとも実行計画上は同じ「Index Scan」と表示されますが、中身がまったく違います。
- PostgreSQL16 / 17：先頭列（bid）の条件がないため、インデックスのリーフページを端から端まで読んで
abalance > 4900を探しています。Buffers readが約8,700ブロック（約68MB）に達しているのがその証拠です - PostgreSQL18：
Index Searches: 101に注目してください。bidの値は100種類（スケールファクタ100）あるため、各bidの範囲内でabalance > 4900の部分だけをピンポイントに101回検索しています。不要なリーフページを読み飛ばすため、Buffers readは258ブロック（約2MB）で済んでいます
結果として、同じインデックス・同じSQLで実行時間に約10倍、読み取りブロック数に約34分の1の差が付きました。第2部「解決①」で登場した Index Searches という出力項目の意味が、ここで具体的に確認できます。
スキップスキャンが有効なのは「先頭列の値の種類が少ない」場合です。先頭列の値が数百万種類あるようなケースではスキップする回数が増えすぎるため、効果は限定的になると考えられます。
3. 症状②：JOINを加えても、思ったより遅くならない
3.1 3テーブルJOINのSQL
次に、支店（branches）と窓口（tellers）を結合した「支店・窓口つき口座一覧」を試します。
ここで重要な前提があります。pgbench_tellersのbid列にはインデックスがありません。先ほどの教訓からすると「tellersへのJOINがSeq Scanになって遅くなる」と予想できそうです。
SELECT
a.aid,
a.abalance,
b.bbalance,
t.tid,
t.tbalance
FROM pgbench_accounts a
JOIN pgbench_branches b ON b.bid = a.bid
JOIN pgbench_tellers t ON t.bid = a.bid
WHERE a.bid = 1
ORDER BY a.abalance DESC
LIMIT 50;
3.2 実行結果：プランナは賢かった
実行計画（PostgreSQL18・3テーブルJOIN・LIMIT 50）
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9431.86..9433.05 rows=50 width=20) (actual time=23.063..24.481 rows=50.00 loops=1)
Buffers: shared hit=1793 read=7
-> Nested Loop (cost=9431.86..34624.22 rows=1053330 width=20) (actual time=23.061..24.475 rows=50.00 loops=1)
Buffers: shared hit=1793 read=7
-> Gather Merge (cost=9431.86..21436.72 rows=105333 width=12) (actual time=22.147..23.483 rows=5.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1793
-> Sort (cost=8431.85..8586.75 rows=61961 width=12) (actual time=19.914..19.958 rows=821.50 loops=2)
Sort Key: a.abalance DESC
Sort Method: quicksort Memory: 3288kB
Buffers: shared hit=1793
Worker 0: Sort Method: quicksort Memory: 2910kB
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts a (cost=0.43..3500.04 rows=61961 width=12) (actual time=0.019..11.621 rows=50000.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=1786
-> Materialize (cost=0.00..20.90 rows=10 width=20) (actual time=0.182..0.196 rows=10.00 loops=5)
Storage: Memory Maximum Storage: 17kB
Buffers: shared read=7
-> Nested Loop (cost=0.00..20.85 rows=10 width=20) (actual time=0.904..0.971 rows=10.00 loops=1)
Buffers: shared read=7
-> Seq Scan on pgbench_branches b (cost=0.00..2.25 rows=1 width=8) (actual time=0.422..0.425 rows=1.00 loops=1)
Filter: (bid = 1)
Rows Removed by Filter: 99
Buffers: shared read=1
-> Seq Scan on pgbench_tellers t (cost=0.00..18.50 rows=10 width=12) (actual time=0.479..0.541 rows=10.00 loops=1)
Filter: (bid = 1)
Rows Removed by Filter: 990
Buffers: shared read=6
Planning:
Buffers: shared hit=137 read=2 dirtied=1
Planning Time: 1.252 ms
Execution Time: 24.662 ms
(34 rows)
実行時間は 24.662 ms 。JOINを2つ足したのに、ほとんど遅くなっていません。
予想と違う結果になった理由は、実行計画の2箇所から読み取れます。
理由1：Materializeノードがtellersの結果をキャッシュした
-> Materialize （略） (actual time=0.182..0.196 rows=10.00 loops=5)
-> Nested Loop （略）
-> Seq Scan on pgbench_tellers t （略） loops=1
tellersのSeq Scanは loops=1、つまり1回しか実行されていません。プランナは結果（10行）をMaterializeノードでメモリに保存し、Nested Loopの内側ではキャッシュを再利用しています。
理由2：LIMIT 50で外側が早期に打ち切られた
-> Gather Merge （略） rows=5.00 loops=1
ORDER BY + LIMIT 50の組み合わせでは、ソート済みの外側から5行取り出した時点で、5行 × tellers 10行 = 50行に達して処理が終了しています。
「JOINを足すと遅くなる」という直感は、このケースではプランナの最適化（MaterializeとLIMITの早期打ち切り）によって裏切られました。プランナは思ったより賢い、というのが最初の学びです。
3.3 PostgreSQL18ではプランナがさらに賢くなっている ─ 自己結合の除去
プランナの賢さに関連して、PostgreSQL18で追加された 自己結合の除去（Self-Join Elimination） も確認します。同じテーブル同士を主キーで結合している場合、結合処理そのものを実行計画から取り除く最適化です。
以下のSQLで確認します。pgbench_accountsを2回登場させ、主キー（aid）同士で結合しています。
SELECT a1.aid, a1.abalance
FROM pgbench_accounts a1, pgbench_accounts a2
WHERE a1.aid = a2.aid AND a1.bid = 1;
実行計画（PostgreSQL17・自己結合）
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.87..107593.47 rows=105333 width=8) (actual time=2.598..134.363 rows=100000 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=0.87..96060.17 rows=61961 width=8) (actual time=0.899..124.355 rows=50000 loops=2)
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts a1 (cost=0.43..3500.04 rows=61961 width=8) (actual time=0.878..64.095 rows=50000 loops=2)
Index Cond: (bid = 1)
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts a2 (cost=0.43..1.49 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=100000)
Index Cond: (aid = a1.aid)
Heap Fetches: 0
Planning Time: 0.453 ms
Execution Time: 138.155 ms
(11 rows)
実行計画（PostgreSQL18・自己結合）
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_accounts_bid on pgbench_accounts a2 (cost=0.43..3933.76 rows=105333 width=8) (actual time=1.890..66.938 rows=100000.00 loops=1)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared read=1727
Planning:
Buffers: shared hit=159
Planning Time: 0.330 ms
Execution Time: 70.405 ms
(8 rows)
| PostgreSQL17 | PostgreSQL18 | |
|---|---|---|
| 実行計画 | Nested Loop（2回スキャン） | Index Scan（1回のみ） |
| Execution Time | 138.155 ms | 70.405 ms |
PostgreSQL17では、a1側のIndex Scanで取り出した10万行それぞれについて、a2側のIndex Only Scanを実行しています（loops=100000）。
PostgreSQL18では、a1とa2が主キーで結合された同一テーブルであることをプランナが認識し、結合を実行計画から除去しています。実行計画はわずか2行（実質1ノード）になり、実行時間は約2分の1になりました。
主キーによる自己結合は冗長であることをプランナが見抜いてくれるため、今回の検証条件では、PostgreSQL18はこの形のSQLを書き直さなくても性能が劣化しにくいように見えました。とはいえ、こうした最適化が常に効くとは限らないので、実際にはEXPLAIN ANALYZEで実行計画を確認するのが確実です。
4. 症状③：集計クエリにすると遅くなる
4.1 LIMITで打ち切れないクエリを作る
では、Nested Loopが本当に重くなるのはどんなときでしょうか。答えのひとつは「全行を処理する必要があるとき」です。LIMITで打ち切れない集計クエリに変えてみます。
「支店1の窓口ごとの口座残高集計レポート」という設定です。
SELECT
t.tid,
t.tbalance,
COUNT(a.aid) AS account_count,
SUM(a.abalance) AS total_balance
FROM pgbench_accounts a
JOIN pgbench_tellers t ON t.bid = a.bid
WHERE a.bid = 1
GROUP BY t.tid, t.tbalance
ORDER BY total_balance DESC;
4.2 実行結果：100万行の中間結果
実行計画（PostgreSQL18・集計クエリ・tellersインデックスなし）
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=16913.34..16913.37 rows=10 width=24) (actual time=204.041..204.107 rows=10.00 loops=1)
Sort Key: (sum(a.abalance)) DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1851
-> Finalize GroupAggregate (cost=16911.01..16913.17 rows=10 width=24) (actual time=204.020..204.090 rows=10.00 loops=1)
Group Key: t.tid
Buffers: shared hit=1848
-> Gather Merge (cost=16911.01..16912.95 rows=17 width=24) (actual time=204.013..204.080 rows=20.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1848
-> Sort (cost=15911.00..15911.02 rows=10 width=24) (actual time=200.352..200.355 rows=10.00 loops=2)
Sort Key: t.tid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1848
Worker 0: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=15910.73..15910.83 rows=10 width=24) (actual time=200.330..200.334 rows=10.00 loops=2)
Group Key: t.tid
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1841
Worker 0: Batches: 1 Memory Usage: 32kB
-> Nested Loop (cost=0.43..11263.69 rows=619606 width=16) (actual time=0.059..116.940 rows=500000.00 loops=2)
Buffers: shared hit=1841
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts a (cost=0.43..3500.04 rows=61961 width=12) (actual time=0.018..9.456 rows=50000.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=1809
-> Materialize (cost=0.00..18.55 rows=10 width=12) (actual time=0.000..0.001 rows=10.00 loops=100000)
Storage: Memory Maximum Storage: 17kB
Buffers: shared hit=32
-> Seq Scan on pgbench_tellers t (cost=0.00..18.50 rows=10 width=12) (actual time=0.033..0.084 rows=10.00 loops=2)
Filter: (bid = 1)
Rows Removed by Filter: 990
Buffers: shared hit=32
Planning:
Buffers: shared hit=128 read=5
Planning Time: 1.885 ms
Execution Time: 204.222 ms
(38 rows)
実行時間は 204.222 ms 。LIMIT版（約25ms）の8倍以上かかるようになりました。
重さの正体はこの2行です。
-> Nested Loop （略） (actual time=0.059..116.940 rows=500000.00 loops=2)
（略）
-> Materialize （略） rows=10.00 loops=100000
- Nested Loopの出力は
rows=500000 × loops=2= 100万行 - Materializeは
loops=100000、つまり10万回参照されています
accounts側の10万行それぞれに対してtellersの10行を組み合わせ、100万行の中間結果を作ってから集計しています。tellersのSeq Scan自体は1回だけ（Materializeのおかげ）ですが、組み合わせ処理そのものが重くなっています。
5. インデックスを追加しても解決しないことを確認する
5.1 tellersにインデックスを作成してみる
「tellersのSeq Scanが残っているから」と考えて、インデックスを追加してみます。
$ sudo -u postgres psql -p 5418 -d pgbench -c "CREATE INDEX idx_tellers_bid ON pgbench_tellers(bid);"
5.2 結果：まったく速くならない
実行計画（PostgreSQL18・集計クエリ・tellersインデックスあり）
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=16903.29..16903.32 rows=10 width=24) (actual time=203.841..205.179 rows=10.00 loops=1)
Sort Key: (sum(a.abalance)) DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1825 read=2
-> Finalize GroupAggregate (cost=16900.96..16903.12 rows=10 width=24) (actual time=203.817..205.161 rows=10.00 loops=1)
Group Key: t.tid
Buffers: shared hit=1822 read=2
-> Gather Merge (cost=16900.96..16902.90 rows=17 width=24) (actual time=203.808..205.148 rows=20.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1822 read=2
-> Sort (cost=15900.95..15900.97 rows=10 width=24) (actual time=201.333..201.336 rows=10.00 loops=2)
Sort Key: t.tid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1822 read=2
Worker 0: Sort Method: quicksort Memory: 25kB
-> Partial HashAggregate (cost=15900.68..15900.78 rows=10 width=24) (actual time=201.311..201.314 rows=10.00 loops=2)
Group Key: t.tid
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=1815 read=2
Worker 0: Batches: 1 Memory Usage: 32kB
-> Nested Loop (cost=0.71..11253.64 rows=619606 width=16) (actual time=0.048..117.929 rows=500000.00 loops=2)
Buffers: shared hit=1815 read=2
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts a (cost=0.43..3500.04 rows=61961 width=12) (actual time=0.017..9.603 rows=50000.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=1810
-> Materialize (cost=0.28..8.50 rows=10 width=12) (actual time=0.000..0.001 rows=10.00 loops=100000)
Storage: Memory Maximum Storage: 17kB
Buffers: shared hit=5 read=2
-> Index Scan using idx_tellers_bid on pgbench_tellers t (cost=0.28..8.45 rows=10 width=12) (actual time=0.023..0.026 rows=10.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 2
Buffers: shared hit=5 read=2
Planning:
Buffers: shared hit=145 read=1
Planning Time: 0.458 ms
Execution Time: 205.295 ms
(38 rows)
| インデックスなし | インデックスあり | |
|---|---|---|
| Execution Time | 204.222 ms | 205.295 ms |
tellersのスキャンは Seq Scan から Index Scan using idx_tellers_bid に変わりました。しかし実行時間は まったく変わっていません。
理由は実行計画を見比べると分かります。
-> Nested Loop （略） rows=500000.00 loops=2 ← 変わっていない
-> Materialize （略） loops=100000 ← 変わっていない
ボトルネックは「tellersの読み方」ではなく「100万行の中間結果を作って集計していること」です。ボトルネックでない場所にインデックスを貼っても、効果はありません。
EXPLAIN ANALYZEで「どこが遅いか」を確認せずにインデックスを追加すると、効果がないばかりか、更新性能の低下やディスク使用量の増加といったデメリットだけが残る可能性があります。
6. 解決②：クエリを書き換える
6.1 「JOINの前に集計する」
ボトルネックが「100万行の中間結果」なら、解決策は中間結果を小さくすることです。accountsを先に集計（10万行 → 1行）してから、tellers（10行）とJOINするように書き換えます。
SELECT
t.tid,
t.tbalance,
agg.account_count,
agg.total_balance
FROM (
SELECT bid, COUNT(aid) AS account_count, SUM(abalance) AS total_balance
FROM pgbench_accounts
WHERE bid = 1
GROUP BY bid
) agg
JOIN pgbench_tellers t ON t.bid = agg.bid
ORDER BY agg.total_balance DESC;
6.2 実行結果：約14倍の改善
実行計画（PostgreSQL18・書き換え版）
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=4489.11..4489.14 rows=10 width=24) (actual time=14.903..14.905 rows=10.00 loops=1)
Sort Key: (sum(pgbench_accounts.abalance)) DESC
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1736
-> Nested Loop (cost=0.43..4488.95 rows=10 width=24) (actual time=14.787..14.884 rows=10.00 loops=1)
Join Filter: (t.bid = pgbench_accounts.bid)
Rows Removed by Join Filter: 990
Buffers: shared hit=1733
-> GroupAggregate (cost=0.43..4460.44 rows=1 width=20) (actual time=14.773..14.774 rows=1.00 loops=1)
Buffers: shared hit=1727
-> Index Scan using idx_accounts_bid on pgbench_accounts (cost=0.43..3933.76 rows=105333 width=12) (actual time=0.016..9.100 rows=100000.00 loops=1)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=1727
-> Seq Scan on pgbench_tellers t (cost=0.00..16.00 rows=1000 width=12) (actual time=0.010..0.053 rows=1000.00 loops=1)
Buffers: shared hit=6
Planning:
Buffers: shared hit=152
Planning Time: 0.422 ms
Execution Time: 14.961 ms
(20 rows)
| 元のクエリ | 書き換え版 | 改善率 | |
|---|---|---|---|
| Execution Time | 205.295 ms | 14.961 ms | 約14倍 |
| Nested Loopの中間結果 | 1,000,000 行 | 10 行 | 10万分の1 |
実行計画の構造変化がポイントです。
書き換え前: 10万行 × 10行 = 100万行をJOINしてから集計
書き換え後: 10万行 → 1行に集計してから、10行とJOIN
GroupAggregate がNested Loopの内側（先に実行される位置）に移動し、JOINに渡る行数が1行になりました。「JOINの前に行数を減らす」 というクエリチューニングの基本原則が、actual rowsの数字でそのまま確認できます。
7. もうひとつの選択肢：パーティションとパーティション・プルーニング（Partition Pruning）
7.1 「読む量を減らす」もうひとつの方法
インデックスは「読む場所を絞る」技術でした。これに対してパーティショニングは「読む対象そのものを分割しておく」技術です。同じSQL-Aを題材に、両者を比較してみます。
bidの値ごとに100個のパーティションへ分割したテーブルを作成します。
# パーティション表の作成（bidによるLISTパーティション）
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
CREATE TABLE pgbench_accounts_p (
aid integer NOT NULL,
bid integer,
abalance integer,
filler character(84)
) PARTITION BY LIST (bid);
EOF
# パーティションを100個生成（bid 1〜100）
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
SELECT format(
'CREATE TABLE pgbench_accounts_p_%s PARTITION OF pgbench_accounts_p FOR VALUES IN (%s);',
g, g)
FROM generate_series(1, 100) g \gexec
EOF
# 既存データを流し込んで統計情報を更新
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
INSERT INTO pgbench_accounts_p SELECT * FROM pgbench_accounts;
ANALYZE pgbench_accounts_p;
EOF
ポイントは、このパーティション表にはbidのインデックスを作っていないことです。パーティション・プルーニングだけでどこまで速くなるかを確認します。
7.2 パーティション・プルーニング（Partition Pruning）の効果を確認する
第2部「症状①」と同じ条件のSQLをパーティション表に対して実行します。
実行計画（PostgreSQL18・パーティション表・bid = 1指定）
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=5329.40..5335.09 rows=50 width=12) (actual time=38.784..39.882 rows=50.00 loops=1)
Buffers: shared hit=45 read=1632
-> Gather Merge (cost=5329.40..16726.57 rows=100001 width=12) (actual time=38.783..39.876 rows=50.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=45 read=1632
-> Sort (cost=4329.39..4476.45 rows=58824 width=12) (actual time=11.498..11.500 rows=25.00 loops=2)
Sort Key: pgbench_accounts_p.abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=45 read=1632
Worker 0: Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on pgbench_accounts_p_1 pgbench_accounts_p (cost=0.00..2375.29 rows=58824 width=12) (actual time=0.030..7.496 rows=50000.00 loops=2)
Filter: (bid = 1)
Buffers: shared hit=8 read=1632
Planning:
Buffers: shared hit=370 read=7 dirtied=1
Planning Time: 23.295 ms
Execution Time: 39.923 ms
(18 rows)
実行計画に登場するのは pgbench_accounts_p_1 の1パーティションだけです。残り99パーティションはプランナ段階で除外されました。これがパーティション・プルーニングです。
| 非パーティション表（Seq Scan） | パーティション表（Seq Scan） | |
|---|---|---|
| Execution Time | 9,247 ms | 39.923 ms |
| Buffers read | 163,935 | 1,632 |
| Rows Removed by Filter | 3,300,000 | なし |
約232倍の改善です。注目すべきは Rows Removed by Filter の行が実行計画から消えていることです。bid = 1のパーティションにはbid = 1の行しか入っていないため、読んだ行を1行も捨てていません。同じSeq Scanでも、読む対象自体が100分の1なら速い、という理屈です。
インデックス（第2部「解決①」）との比較も並べてみます。
| インデックス | パーティション | |
|---|---|---|
| Execution Time | 28.245 ms | 39.923 ms |
| Buffers read | 1,727 | 1,632 |
このデータ規模では読み取り量・実行時間ともほぼ互角でした。
7.3 パーティション・プルーニングが効かないと逆効果になる
ではパーティションキー（bid）をWHERE句に含めないクエリではどうなるでしょうか。
SELECT aid, bid, abalance
FROM pgbench_accounts_p
ORDER BY abalance DESC
LIMIT 50;
実行計画（PostgreSQL18・パーティション表・bid指定なし／218行の一部を抜粋）
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=383071.83..383077.65 rows=50 width=12) (actual time=1810.454..1814.008 rows=50.00 loops=1)
Buffers: shared hit=6731 read=157343 written=12394
-> Gather Merge (cost=383071.83..1547745.94 rows=10000080 width=12) (actual time=1810.452..1814.001 rows=50.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=6731 read=157343 written=12394
-> Sort (cost=382071.81..392488.56 rows=4166700 width=12) (actual time=1803.327..1803.348 rows=37.67 loops=3)
Sort Key: pgbench_accounts_p.abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=6731 read=157343 written=12394
Worker 0: Sort Method: top-N heapsort Memory: 26kB
Worker 1: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Append (cost=0.00..243657.03 rows=4166700 width=12) (actual time=0.044..1218.930 rows=3333333.33 loops=3)
Buffers: shared hit=6657 read=157343 written=12394
-> Parallel Seq Scan on pgbench_accounts_p_1 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.010..11.997 rows=100000.00 loops=1)
Buffers: shared hit=1640
-> Parallel Seq Scan on pgbench_accounts_p_2 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.107..35.026 rows=100000.00 loops=1)
Buffers: shared hit=7 read=1633 written=890
-> Parallel Seq Scan on pgbench_accounts_p_3 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.093..20.730 rows=100000.00 loops=1)
Buffers: shared hit=8 read=1632 written=1343
（中略：pgbench_accounts_p_4 〜 pgbench_accounts_p_99 まで同様のSeq Scanが続く）
-> Parallel Seq Scan on pgbench_accounts_p_100 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.013..29.470 rows=100000.00 loops=1)
Buffers: shared hit=1640
Planning:
Buffers: shared hit=2365 dirtied=22
Planning Time: 2.560 ms
Execution Time: 1814.508 ms
(218 rows)
Parallel Append の下に100個のSeq Scanが並ぶ、218行の実行計画になりました。
| bid = 1 指定（Pruning有効） | bid指定なし（Pruning無効） | |
|---|---|---|
| アクセスしたパーティション | 1個 | 100個すべて |
| Execution Time | 39.923 ms | 1,814.508 ms |
| Buffers read | 1,632 | 157,343 |
| 実行計画の行数 | 18行 | 218行 |
同じテーブル・同じデータでも、WHERE句にパーティションキーが含まれるかどうかで約45倍の差が付きました。
パーティションキーがWHERE句に含まれないクエリでは、全パーティションへのアクセスが発生し、パーティション分のオーバーヘッド（Planning Time 23ms等）だけが残ります。パーティション設計では「主要なクエリのWHERE句に必ず登場する列」をキーに選ぶことが重要と考えられます。
第3部 運用で解決する ─ VACUUMとデッドタプル（dead tuple）
ここからは、テーブルの運用面に目を向けます。更新を重ねるうちに溜まっていくデッドタプル（dead tuple）と、それを掃除するVACUUMの挙動を見ていきます。
1. 症状④：更新が積み重なると遅くなる（デッドタプル（dead tuple）とVACUUM）
1.1 デッドタプル（dead tuple）とは
PostgreSQLのUPDATEは「古い行を残したまま新しい行を作る」仕組み（追記型MVCC）で動いています。古い行は デッドタプル（dead tuple） として残り、VACUUMが回収するまでディスク上に残り続けます。
まず現在の状態を確認します。スキップスキャン検証（第2部「解決①」）でpgbenchを30秒実行した直後の状態です。
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
SELECT
relname,
n_live_tup,
n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('pgbench_accounts', 'pgbench_tellers', 'pgbench_branches', 'pgbench_history')
ORDER BY n_dead_tup DESC;
EOF
relname | n_live_tup | n_dead_tup | dead_ratio | last_autovacuum
------------------+------------+------------+------------+-------------------------------
pgbench_accounts | 10000035 | 45828 | 0.46 |
pgbench_history | 45829 | 0 | 0.00 | 2026-06-11 22:05:38.340446+09
pgbench_tellers | 1000 | 0 | 0.00 | 2026-06-11 22:05:38.274579+09
pgbench_branches | 100 | 0 | 0.00 | 2026-06-11 22:05:38.264364+09
(4 rows)
興味深いのは last_autovacuum の列です。小さいテーブル（tellers / branches / history）はすでに自動VACUUM済みですが、1,000万行のpgbench_accountsだけ一度も自動VACUUMされていません。45,828行のデッドタプル（dead tuple）があっても全体の0.46%にすぎず、autovacuumのデフォルト閾値（テーブルの約20%）に遠く及ばないためです。巨大テーブルではautovacuumがなかなか動かない、という実例です。
1.2 デッドタプル（dead tuple）を意図的に蓄積させる
検証のためautovacuumを一時的に無効化し、pgbenchを3分間実行してUPDATEを大量発生させます。
$ sudo -u postgres psql -p 5418 -d pgbench -c "ALTER TABLE pgbench_accounts SET (autovacuum_enabled = false);"
$ sudo -u postgres /usr/pgsql-18/bin/pgbench -c 4 -T 180 -p 5418 pgbench
number of transactions actually processed: 229289
latency average = 3.140 ms
tps = 1273.857480 (without initial connection time)
このTPS（1273.86）は後のEBS増強検証のベースラインとしても使用します。3分間で約23万件のUPDATEが発生し、デッドタプル（dead tuple）は275,110件（2.68%）に増えました。
この状態（デッドタプル（dead tuple） 2.68%）で第2部「解決①」のSQL-Aを再計測したところ、実行時間は27.824 msでクリーン状態（28.245 ms）とほぼ変わりませんでした。読み取りBuffersは約1.6倍に増えていましたが、キャッシュに乗っていたため実行時間には現れていません。数%程度のデッドタプル（dead tuple）では実害が小さい、というのが実測から言えることです。
1.3 全行UPDATEでテーブルを肥大化させる
では「もっと溜まったら」どうなるか。全行UPDATEで一気にデッドタプル（dead tuple）を50%にします。
$ sudo -u postgres psql -p 5418 -d pgbench -c "UPDATE pgbench_accounts SET abalance = abalance + 1;"
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';
SELECT pg_size_pretty(pg_relation_size('pgbench_accounts')) AS table_size;
EOF
relname | n_live_tup | n_dead_tup | dead_ratio
------------------+------------+------------+------------
pgbench_accounts | 10000035 | 10275101 | 50.68
(1 row)
table_size
------------
2561 MB
(1 row)
デッドタプル（dead tuple）が約1,028万件（50.68%）になり、テーブルサイズは1,298 MB → 2,561 MBへ倍増しました。生きている行数は1,000万行のまま変わっていないのに、です。
この状態でSQL-Aを再計測します（複合インデックスidx_accounts_bid_abalanceが選ばれないよう、トランザクション内で一時的にDROPして計測しています）。
実行計画（PostgreSQL18・デッドタプル（dead tuple） 50.68%の状態）
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=12081.27..12086.97 rows=50 width=12) (actual time=106.632..108.303 rows=50.00 loops=1)
Buffers: shared hit=591 read=5036 dirtied=5141
-> Gather Merge (cost=12081.27..36091.11 rows=210667 width=12) (actual time=106.630..108.297 rows=50.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=591 read=5036 dirtied=5141
-> Sort (cost=11081.26..11391.06 rows=123922 width=12) (actual time=104.231..104.234 rows=37.50 loops=2)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=591 read=5036 dirtied=5141
Worker 0: Sort Method: top-N heapsort Memory: 26kB
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts (cost=0.44..6964.66 rows=123922 width=12) (actual time=0.059..96.868 rows=50000.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=584 read=5036 dirtied=5141
Planning:
Buffers: shared hit=79
Planning Time: 0.292 ms
Execution Time: 108.336 ms
(19 rows)
| 状態 | テーブルサイズ | Buffers合計 | Execution Time |
|---|---|---|---|
| クリーン | 1,298 MB | 約1,796 | 28.245 ms |
| dead tuple 2.68% | 1,298 MB | 約2,890 | 27.824 ms |
| dead tuple 50.68% | 2,561 MB | 約5,627 | 108.336 ms |
返ってくる結果は同じ50行なのに、読むブロックが3倍、実行時間が約4倍になりました。デッドタプル（dead tuple）の実害が数字に現れるのは、テーブルの肥大化が進んでからであることが分かります。
1.4 VACUUMで回復させる
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
\timing on
VACUUM (VERBOSE) pgbench_accounts;
EOF
VACUUM (VERBOSE) の出力（PostgreSQL18）
INFO: vacuuming "pgbench.public.pgbench_accounts"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: finished vacuuming "pgbench.public.pgbench_accounts": index scans: 1
pages: 0 removed, 327870 remain, 327870 scanned (100.00% of total), 0 eagerly scanned
tuples: 9841220 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 276127, which was 0 XIDs old when operation ended
new relfrozenxid: 276125, which is 275354 XIDs ahead of previous value
frozen: 292884 pages from table (89.33% of total) had 9900689 tuples frozen
visibility map: 327870 pages set all-visible, 324326 pages set all-frozen (0 were all-visible)
index scan needed: 166146 pages from table (50.67% of total) had 10275101 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_accounts_bid": pages: 16299 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_accounts_bid_abalance": pages: 18280 in total, 8387 newly deleted, 8387 currently deleted, 0 reusable
avg read rate: 77.517 MB/s, avg write rate: 77.945 MB/s
buffer usage: 416888 hits, 562249 reads, 565350 dirtied
WAL usage: 1231855 records, 565349 full page images, 3068681710 bytes, 1515 buffers full
system usage: CPU: user: 3.80 s, system: 2.05 s, elapsed: 56.66 s
VACUUM
Time: 56666.321 ms (00:56.666)
所要時間は56.66秒でした。VERBOSE出力から多くの情報が読み取れます。
-
launched 2 parallel vacuum workers─ インデックス3本をパラレルVACUUMしています -
tuples: 9841220 removed─ 約1,000万件のデッドタプル（dead tuple）を除去しました -
visibility map: 327870 pages set all-visible─ 可視性マップが整備されました（この効果は後述します） -
WAL usage: ... 3068681710 bytes─ VACUUM自体が約3GBのWALを出力しています -
avg read rate: 77.517 MB/s─ VACUUM中の読み取り速度です。検証環境のEBS（125 MB/s）に対してかなりの帯域を使っていることが分かります
VACUUM後の状態を確認します。
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';
SELECT pg_size_pretty(pg_relation_size('pgbench_accounts')) AS table_size;
EOF
relname | n_live_tup | n_dead_tup
------------------+------------+------------
pgbench_accounts | 10000000 | 0
(1 row)
table_size
------------
2561 MB
(1 row)
デッドタプル（dead tuple）は0になりましたが、テーブルサイズは2,561 MBのまま縮んでいません。VACUUMは死んだ行の領域を「再利用可能」にするだけで、OSへ返却はしないためです（サイズを物理的に縮めるにはVACUUM FULLやpg_repackが必要ですが、排他ロック等の別のコストがかかります）。
サイズが縮んでいないにもかかわらず、SQL-Aの性能は回復しました。
実行計画（PostgreSQL18・VACUUM後）
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=8613.35..8619.05 rows=50 width=12) (actual time=31.904..32.048 rows=50.00 loops=1)
Buffers: shared hit=4138 read=3
-> Gather Merge (cost=8613.35..20618.33 rows=105334 width=12) (actual time=31.902..32.041 rows=50.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=4138 read=3
-> Sort (cost=7613.34..7768.24 rows=61961 width=12) (actual time=26.450..26.454 rows=39.50 loops=2)
Sort Key: abalance DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=4138 read=3
Worker 0: Sort Method: top-N heapsort Memory: 27kB
-> Parallel Index Scan using idx_accounts_bid on pgbench_accounts (cost=0.43..5555.04 rows=61961 width=12) (actual time=0.027..17.779 rows=50000.00 loops=2)
Index Cond: (bid = 1)
Index Searches: 1
Buffers: shared hit=4131 read=3
Planning:
Buffers: shared hit=79
Planning Time: 0.358 ms
Execution Time: 32.089 ms
(19 rows)
| 状態 | Execution Time |
|---|---|
| dead tuple 50.68% | 108.336 ms |
| VACUUM後 | 32.089 ms |
VACUUMはテーブルサイズを戻す道具ではなく、死んだ行への参照を断ち切って読み取り効率を回復する道具である、というのがこの実測のまとめです。
1.5 VACUUMの進捗をリアルタイムで監視する
大きなテーブルのVACUUMは数十秒〜数分かかることがあり、「今どこまで進んだのか」が分からないと不安になります。pg_stat_progress_vacuum ビューを使うと、実行中のVACUUMの進捗を別セッションから確認できます。
VACUUMを実行しているセッションとは別のセッションで、以下を繰り返し実行します。
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
SELECT
pid,
phase,
heap_blks_total,
heap_blks_scanned,
round(heap_blks_scanned * 100.0 / nullif(heap_blks_total, 0), 1) AS scan_pct,
index_vacuum_count
FROM pg_stat_progress_vacuum;
EOF
VACUUM実行中に3回観測した結果がこちらです。
| 観測 | phase | heap_blks_scanned | scan_pct |
|---|---|---|---|
| 1回目 | scanning heap | 29,587 | 9.0% |
| 2回目 | scanning heap | 162,534 | 49.6% |
| 3回目 | scanning heap | 300,211 | 91.6% |
heap_blks_total（327,869ブロック）に対して heap_blks_scanned が増えていき、進捗率が読み取れます。phase 列はVACUUMの段階を示し、scanning heap（テーブル本体のスキャン）のほか vacuuming indexes（インデックスの掃除）、vacuuming heap（テーブルの掃除）などに変化します。長時間VACUUMの「あと何割か」を把握する手段として有用です。
1.6 VACUUMの累計実行時間を記録する（PostgreSQL18の新機能）
PostgreSQL18では、pg_stat_all_tables にVACUUM・ANALYZEの累計実行時間を示す列（total_vacuum_time など）が追加されました。テーブルごとに「VACUUMに合計どれだけ時間を使っているか」が分かります。
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
SELECT relname, vacuum_count, autovacuum_count,
total_vacuum_time, total_autovacuum_time
FROM pg_stat_all_tables
WHERE relname = 'pgbench_accounts';
EOF
relname | vacuum_count | autovacuum_count | total_vacuum_time | total_autovacuum_time
------------------+--------------+------------------+-------------------+-----------------------
pgbench_accounts | 4 | 0 | 184524 | 0
(1 row)
手動VACUUMを4回実行し、累計で184,524ミリ秒（約184秒）かかったことが記録されています。この列はPostgreSQL17以前には存在せず、同じSQLをPostgreSQL17で実行するとエラーになります。
$ sudo -u postgres psql -p 5417 -d pgbench -c "SELECT total_vacuum_time FROM pg_stat_all_tables LIMIT 1;"
ERROR: column "total_vacuum_time" does not exist
total_autovacuum_time が大きいテーブルは、自動VACUUMにコストがかかっているテーブルです。チューニングで優先的に見るべきテーブルを特定する手がかりになります。
2. 番外編：VACUUMの所要時間は3バージョンで単純比較できない
2.1 3バージョンで同じVACUUMを実行してみる
最後に、PostgreSQL16 / 17 / 18 でVACUUMの所要時間を比較しました。各バージョンで全行UPDATE（dead tuple 1,000万件を生成）した直後にVACUUMを実行しています。計測条件はshared_buffers = 2GB、EBSは増強後（500 MB/s）、インデックス3本（パラレルVACUUMが有効になる構成）です。
VACUUM (VERBOSE) の出力（PostgreSQL16）
INFO: vacuuming "pgbench.public.pgbench_accounts"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: finished vacuuming "pgbench.public.pgbench_accounts": index scans: 1
pages: 0 removed, 327869 remain, 327869 scanned (100.00% of total)
tuples: 9676700 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 50472, which was 0 XIDs old when operation ended
new relfrozenxid: 50471, which is 6 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 163936 pages from table (50.00% of total) had 10000000 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_accounts_bid": pages: 33434 in total, 14875 newly deleted, 23248 currently deleted, 8373 reusable
index "idx_accounts_bid_abalance": pages: 24486 in total, 7972 newly deleted, 16411 currently deleted, 8439 reusable
avg read rate: 58.964 MB/s, avg write rate: 94.038 MB/s
buffer usage: 811997 hits, 320497 misses, 511142 dirtied
WAL usage: 773779 records, 235645 full page images, 435303638 bytes
system usage: CPU: user: 4.72 s, system: 1.14 s, elapsed: 42.46 s
VACUUM
Time: 43468.268 ms (00:43.468)
VACUUM (VERBOSE) の出力（PostgreSQL17）
INFO: vacuuming "pgbench.public.pgbench_accounts"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: finished vacuuming "pgbench.public.pgbench_accounts": index scans: 1
pages: 0 removed, 327869 remain, 327869 scanned (100.00% of total)
tuples: 10000000 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 48947, which was 0 XIDs old when operation ended
new relfrozenxid: 48946, which is 1 XIDs ahead of previous value
frozen: 41833 pages from table (12.76% of total) had 2509899 tuples frozen
index scan needed: 205087 pages from table (62.55% of total) had 10000000 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_accounts_bid": pages: 32598 in total, 8999 newly deleted, 17368 currently deleted, 8369 reusable
index "idx_accounts_bid_abalance": pages: 31422 in total, 7796 newly deleted, 22898 currently deleted, 15102 reusable
avg read rate: 40.146 MB/s, avg write rate: 69.930 MB/s
buffer usage: 807744 hits, 330587 misses, 575851 dirtied
WAL usage: 843868 records, 478316 full page images, 1077603021 bytes
system usage: CPU: user: 3.43 s, system: 1.40 s, elapsed: 64.33 s
VACUUM
Time: 64352.405 ms (01:04.352)
VACUUM (VERBOSE) の出力（PostgreSQL18・再初期化したクリーン環境）
INFO: vacuuming "pgbench.public.pgbench_accounts"
INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)
INFO: finished vacuuming "pgbench.public.pgbench_accounts": index scans: 1
pages: 0 removed, 327869 remain, 327869 scanned (100.00% of total), 0 eagerly scanned
tuples: 10000000 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 577674, which was 0 XIDs old when operation ended
new relfrozenxid: 577674, which is 9 XIDs ahead of previous value
frozen: 163935 pages from table (50.00% of total) had 10000000 tuples frozen
visibility map: 327869 pages set all-visible, 327869 pages set all-frozen (0 were all-visible)
index scan needed: 163935 pages from table (50.00% of total) had 10000000 dead item identifiers removed
index "pgbench_accounts_pkey": pages: 54840 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "idx_accounts_bid": pages: 16133 in total, 8348 newly deleted, 8348 currently deleted, 0 reusable
index "idx_accounts_bid_abalance": pages: 16511 in total, 8439 newly deleted, 8439 currently deleted, 0 reusable
avg read rate: 30.436 MB/s, avg write rate: 58.862 MB/s
buffer usage: 750899 hits, 291630 reads, 564008 dirtied
WAL usage: 1252833 records, 564163 full page images, 3092818903 bytes, 94138 buffers full
system usage: CPU: user: 5.23 s, system: 1.75 s, elapsed: 74.85 s
VACUUM
Time: 74862.394 ms (01:14.862)
2.2 所要時間だけ見ると誤読する
| PostgreSQL16 | PostgreSQL17 | PostgreSQL18 | |
|---|---|---|---|
| 所要時間 | 43.47 秒 | 64.35 秒 | 74.86 秒 |
| デッドタプル（dead tuple）回収 | 1,000万件 | 1,000万件 | 1,000万件 |
| 凍結した行数 | 0行 | 約250万行 | 1,000万行（全行） |
| WAL出力 | 0.44 GB | 1.08 GB | 3.09 GB |
所要時間だけ見ると「PostgreSQL18が一番遅い」ように見えます。しかしVERBOSE出力の frozen: 行を読むと、3バージョンは同じ仕事をしていないことが分かります。
- PostgreSQL16：デッドタプル（dead tuple）の回収だけを実行（凍結0行）
- PostgreSQL17：回収に加えて約250万行を凍結
- PostgreSQL18：回収に加えて全1,000万行を凍結し、可視性マップも全ページ整備（
327869 pages set all-frozen）
PostgreSQL18では、今回の検証条件で全1,000万行の凍結が行われました。この結果を見る限り、「UPDATEでページを書き換えるタイミングで凍結も積極的に進める」方針に近い挙動になっているように見えます。その分のWAL（3.09 GB）と時間を支払っている形です。凍結はトランザクションIDの周回（wraparound）対策としていずれ必ず実行しなければならない処理なので、見方を変えれば、将来の凍結専用VACUUM（anti-wraparound VACUUM）のコストを先払いしているとも捉えられます。
実際、全行凍結が済んだ後のPostgreSQL18では、可視性マップの効果でその後のVACUUMやIndex Only Scan（第4部「EBSの限界」）が軽くなることを確認しています。
VACUUMの所要時間をバージョン間・環境間で比較するときは、必ずVERBOSEオプションで「何をしたか」（凍結行数・WAL量・スキャン率）を確認することをおすすめします。所要時間の数字だけの比較は、今回のように仕事量が数倍違うケースを見落とす可能性があります。
なお、計測の過程では条件が揃わずに93秒や142秒という値も観測されました。原因はテーブルの肥大化（過去の検証履歴による3.8GB化）や凍結対象の蓄積で、同じVACUUMコマンドでも実行時の状態によって所要時間が大きく揺れることを示しています。
第4部 インフラで解決する ─ OSとEBS
ここからは、PostgreSQLの外側に目を向けます。設定の調整では超えられない壁にぶつかったとき、最後に効いてくるのがディスク（EBS）の性能です。
1. OS/インフラの壁：EBSの限界を実測する
1.1 速すぎる集計クエリ ─ Index Only Scanが効いていた
ここからはOS・インフラレベルの観察に移ります。まず、テーブル全体（2,561 MB）を読む集計クエリでディスクI/Oを発生させようとしたところ、予想外のことが起きました。
$ sudo -u postgres psql -p 5418 -d pgbench << 'EOF'
EXPLAIN (ANALYZE)
SELECT count(*), sum(abalance) FROM pgbench_accounts;
EOF
実行計画（PostgreSQL18・count + sum集計）
Finalize Aggregate (cost=186620.66..186620.67 rows=1 width=16) (actual time=752.417..754.947 rows=1.00 loops=1)
Buffers: shared hit=167153
-> Gather (cost=186620.44..186620.65 rows=2 width=16) (actual time=752.173..754.936 rows=3.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=167153
-> Partial Aggregate (cost=185620.44..185620.45 rows=1 width=16) (actual time=749.167..749.168 rows=1.00 loops=3)
Buffers: shared hit=167153
-> Parallel Index Only Scan using idx_accounts_bid_abalance on pgbench_accounts (cost=0.43..164787.10 rows=4166667 width=4) (actual time=0.015..419.148 rows=3333333.33 loops=3)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=167153
Planning:
Buffers: shared hit=96
Planning Time: 0.247 ms
Execution Time: 755.012 ms
(16 rows)
2.5GBのテーブルを読むはずが、わずか0.75秒で終わっています。実行計画を見ると理由が分かります。
-
Parallel Index Only Scan using idx_accounts_bid_abalance─ count(*)とsum(abalance)に必要な情報が複合インデックス（bid, abalance）にすべて含まれているため、テーブル本体を読まずにインデックスだけで集計しています -
Heap Fetches: 0─ 行の可視性確認のためのテーブルアクセスがゼロです。これは第3部「デッドタプル（dead tuple）とVACUUM」のVACUUMで可視性マップ（327870 pages set all-visible）が整備されたおかげです
VACUUMにはIndex Only Scanを機能させる効果もある、ということです。
1.2 EBSのスループット上限を観測する
テーブル本体の読み込みを強制するため、インデックスに含まれない filler 列を集計します。事前にPostgreSQLの再起動とOSページキャッシュのクリアを行い、完全なコールドリードの状態を作ります。
$ sudo systemctl restart postgresql-18 && \
sudo sh -c 'sync; echo 3 > /proc/sys/vm/drop_caches' && \
sudo -u postgres psql -p 5418 -d pgbench -c '\timing on' \
-c "SELECT count(filler) FROM pgbench_accounts;"
Time: 19557.093 ms (00:19.557)
約19.6秒かかりました。検証環境のEBS（gp3）のスループット上限は125 MB/sです。2,561 MB ÷ 125 MB/s ≒ 20.5秒なので、理論値とほぼ一致しています。
実行中の iostat -xm 2 の出力（nvme0n1の行）は以下の通りでした。
Device r/s rMB/s r_await %util
nvme0n1 1160.80 134.75 11.06 85.52
nvme0n1 1011.80 125.06 14.86 93.00
-
rMB/sが125前後に張り付いています。これがEBSスループット上限です -
%utilは85〜93%でディスクはほぼ飽和状態です - このときCPUの
%iowaitは81〜96%でした。CPUの仕事のほとんどが「ディスクからの返事待ち」になっています
1.3 shared_buffersは「2回目以降」を速くする
PGDGパッケージのshared_buffersデフォルトは128MBです。8GiBメモリのインスタンスに対して小さすぎるため、2GBへ変更します。
$ sudo sh -c "echo 'shared_buffers = 2GB' >> /var/lib/pgsql/18/data/postgresql.conf"
$ sudo systemctl restart postgresql-18
$ sudo -u postgres psql -p 5418 -c "SHOW shared_buffers;"
設定後、コールド（キャッシュクリア後）とホット（直後の再実行）で同じクエリを計測します。
# コールド
$ sudo sh -c 'sync; echo 3 > /proc/sys/vm/drop_caches' && \
sudo -u postgres psql -p 5418 -d pgbench -c '\timing on' \
-c "SELECT count(filler) FROM pgbench_accounts;"
# ホット（そのまま再実行）
$ sudo -u postgres psql -p 5418 -d pgbench -c '\timing on' \
-c "SELECT count(filler) FROM pgbench_accounts;"
| 実行時間 | |
|---|---|
| コールドリード | 19,545 ms |
| ホットリード | 805 ms（約24倍） |
重要なのは、コールドリードの約19.5秒はshared_buffersをいくら増やしても縮まないことです。初回は必ずディスクから読むため、ディスクの速度がそのまま実行時間になります。shared_buffersが効くのは2回目以降です。
メモリ設定はディスクを速くするのではなく、再訪を速くする。では「初回アクセスも速くしたい」場合はどうするか。ディスク自体を速くするしかありません。
2. 解決④：EBSをスペックアップする
2.1 オンラインでEBSの性能を変更する
EBS gp3はインスタンスを停止せずにIOPSとスループットを変更できます。AWSコンソールから以下のように変更しました。
| 変更前 | 変更後 | |
|---|---|---|
| IOPS | 3,000 | 12,000 |
| スループット | 125 MB/s | 500 MB/s |
AWS 管理コンソールからの変更です。
- [EC2] → [ボリューム] → 対象ボリュームを選択
- [アクション] → [ボリュームの変更]
- IOPS・スループットを入力して変更を保存
変更直後はボリュームの状態が「modifying」→「optimizing」と遷移します。計測は状態が「使用中」（完了）に戻ってから行いました。
2.2 コールドリードの再計測
第4部「EBSの限界」と同じ手順でコールドリードを再計測します。
$ sudo systemctl restart postgresql-18 && \
sudo sh -c 'sync; echo 3 > /proc/sys/vm/drop_caches' && \
sudo -u postgres psql -p 5418 -d pgbench -c '\timing on' \
-c "SELECT count(filler) FROM pgbench_accounts;"
Time: 4184.608 ms (00:04.185)
| 増強前（125 MB/s） | 増強後（500 MB/s） | |
|---|---|---|
| コールドリード | 19,545 ms | 4,185 ms（約4.7倍） |
| iostat %util | 85〜93% | 18% |
| CPU %iowait | 81〜96% | 13% |
SQLもPostgreSQLの設定も1行も変えずに約4.7倍になりました。2,561 MB ÷ 500 MB/s ≒ 5.1秒の理論値ともほぼ一致しています。%util と %iowait が大幅に下がり、ディスクが飽和しなくなったことも確認できます。
2.3 pgbenchのTPSで比較する
更新系ワークロード（TPC-B）ではどうでしょうか。第3部「デッドタプル（dead tuple）とVACUUM」と同条件（クライアント4・180秒）で再計測します。
$ sudo -u postgres /usr/pgsql-18/bin/pgbench -c 4 -T 180 -p 5418 pgbench
number of transactions actually processed: 301511
latency average = 2.388 ms
tps = 1675.059743 (without initial connection time)
| 増強前 | 増強後 | |
|---|---|---|
| TPS | 1273.86 | 1675.06（+31%） |
| レイテンシ平均 | 3.140 ms | 2.388 ms |
コールドリード（4.7倍）に比べると改善幅は+31%にとどまりました。この差は、ワークロードの性質によるものと考えられます。
- 大量シーケンシャルリード：スループット上限がボトルネックに直撃するため、上限を4倍にすると実行時間もほぼ4倍改善します
- TPC-B（小さいランダムI/O + WAL書き込み）：ボトルネックがI/Oスループットだけでなく、レイテンシ・WAL書き込み・CPUに分散しているため、改善は限定的です
スペックアップは万能薬ではなく、ワークロードのボトルネックに合った増強が効く、というのがこの検証の結論です。
3. ソフトウェア側でできる改善を試す
ハードウェア（EBS）の増強は効果が大きい一方、コストもかかります。ここでは設定変更だけでできる改善を3つ試します。検証はEBSを125 MB/sに戻した状態で行っています。
3.1 非同期I/Oとプリフェッチ設定は効くのか
環境構築でPostgreSQL18の非同期I/O（io_method = io_uring）を有効化しました。これと、I/Oの先読み並列度を決める effective_io_concurrency を組み合わせて、コールドリード（2,561 MBのフルスキャン）への効果を測定します。
effective_io_concurrency はPostgreSQL18からデフォルトが16に引き上げられています（17以前は1）。
| パターン | io_method |
effective_io_concurrency |
実行時間 |
|---|---|---|---|
| 1 | worker | 16 | 20,098 ms |
| 2 | io_uring |
16 | 19,540 ms |
| 3 | io_uring |
64 | 19,539 ms |
| 4 | worker | 64 | 19,539 ms |
4パターンとも約19.5秒で横並びでした。io_methodを変えても、プリフェッチ並列度を上げても、ほとんど差が出ていません。
理由は明確で、このワークロード（単一クライアントのシーケンシャルスキャン）が非同期I/Oと相性が悪いためと考えられます。
- シーケンシャルスキャンはOSとPostgreSQLの先読みが既に効率的に働いており、非同期化・並列化の余地が小さい
-
effective_io_concurrencyが活きるのは、ビットマップスキャンのような「飛び飛びのブロックを読む」ランダムI/Oを伴う処理 - そして何より、EBSスループット（125 MB/s）に張り付いている状況では、I/Oを効率的に発行しても発行先のディスクが詰まっているため効果が出ない
io_uringやeffective_io_concurrencyは「I/O要求を効率的に発行する」機能であり、ボトルネックがディスクのスループットそのものである場合には効果が現れない、という点が実測から確認できました。
これはPostgreSQL18の非同期I/Oが無意味という意味ではありません。ランダムI/Oが多いワークロードや、ディスクに帯域の余裕がある環境では効果が期待できます。「ワークロードと環境次第」というのが要点です。
3.2 work_memでディスクソートを解消する
これまで扱ってきた shared_buffers は「読み取りのキャッシュ」でした。これとは別に、ソートやハッシュ結合の作業領域を決めるのが work_mem です。
大きなソートで work_mem が不足すると、PostgreSQLは中間データを一時ファイルとしてディスク（EBS）に書き出します。1,000万行を全件ソートするクエリで確認します。
EXPLAIN (ANALYZE, BUFFERS)
SELECT aid, abalance FROM pgbench_accounts ORDER BY abalance;
実行計画（`work_mem` = 4MB・デフォルト）
Sort (cost=1863984.83..1888984.83 rows=10000000 width=8) (actual time=2592.624..3257.195 rows=10000000.00 loops=1)
Sort Key: abalance
Sort Method: external merge Disk: 176160kB
Buffers: shared hit=3171 read=324701, temp read=44038 written=44159
-> Seq Scan on pgbench_accounts (cost=0.00..427869.00 rows=10000000 width=8) (actual time=247.637..1012.004 rows=10000000.00 loops=1)
Buffers: shared hit=3168 read=324701
Planning Time: 1.572 ms
Execution Time: 3625.848 ms
(10 rows)
実行計画（`work_mem` = 1GB）
Sort (cost=1590543.83..1615543.83 rows=10000000 width=8) (actual time=1996.983..2383.060 rows=10000000.00 loops=1)
Sort Key: abalance
Sort Method: quicksort Memory: 627592kB
Buffers: shared hit=4227 read=323645
-> Seq Scan on pgbench_accounts (cost=0.00..427869.00 rows=10000000 width=8) (actual time=247.628..1008.515 rows=10000000.00 loops=1)
Buffers: shared hit=4224 read=323645
Planning Time: 0.252 ms
Execution Time: 2750.660 ms
(10 rows)
work_mem = 4MB |
work_mem = 1GB |
|
|---|---|---|
| Sort Method | external merge（ディスク） | quicksort（メモリ） |
| ディスク書き出し | 176,160 kB | なし |
| 一時ファイルI/O | read 44,038 / written 44,159 | なし |
| Execution Time | 3,625.848 ms | 2,750.660 ms（約24%短縮） |
work_mem が4MBのときは Sort Method: external merge となり、約172MBの一時ファイルをディスクに書き出していました。1GBに増やすと627MBのソートがメモリ内に収まり（quicksort）、一時ファイルI/Oがゼロになりました。
ただし改善幅は24%にとどまりました。一時ファイルI/Oは消えても、ソート処理そのもののCPU時間が支配的だからです。ディスクI/Oを取り除いても、処理の本体が重ければそこで頭打ちになります。
work_mem は接続ごと・ソートごとに確保されます。1GBのような大きな値をグローバル（postgresql.conf）に設定すると、同時接続が増えたときにメモリ枯渇を招く危険があります。大きな値が必要なときは、SET LOCAL で特定のクエリ・トランザクションに限定して適用するのが安全です。
3.3 チェックポイント設定とPostgreSQL18のデフォルト改善
更新系ワークロードでは、チェックポイント（メモリ上の変更をディスクに書き出す処理）の頻度が性能に影響します。max_wal_size が小さいとWALがすぐ一杯になってチェックポイントが頻発し、書き込みI/Oがスパイクします。
max_wal_size をデフォルトの1GBから8GBに拡大して、pgbench（クライアント4・120秒）で比較しました。
max_wal_size = 1GB |
max_wal_size = 8GB |
|
|---|---|---|
| TPS | 1664.87 | 1717.30（+3.1%） |
| レイテンシ平均 | 2.403 ms | 2.329 ms |
改善は+3.1%と小幅でした。120秒という短い計測ではチェックポイントの発生回数が少なく差が出にくいこと、またEBSの書き込み帯域（125 MB/s）が狭いことが要因と考えられます。
なお、この検証の過程で気づいた点があります。チェックポイントの書き込みを時間的に分散させる checkpoint_completion_target は、PostgreSQL18ではデフォルトが0.9になっていました（17以前は0.5）。
$ sudo -u postgres psql -p 5418 -c "SHOW checkpoint_completion_target;"
`checkpoint_completion_target`
------------------------------
0.9
以前は手動で0.9前後に設定するのが定番チューニングのひとつでしたが、PostgreSQL18では最初から推奨値が入っています。「設定をいじる前に、新しいバージョンでは良いデフォルトが入っていないかを確認する」ことの大切さを示す一例です。
第5部 まとめ
1. まとめ：チューニングの判断フロー
| 症状 | 原因 | 解決策 | 効果 |
|---|---|---|---|
| 一覧表示が遅い（9.2秒） | bid列にインデックスがなくSeq Scan | CREATE INDEX | 約327倍 |
| JOINを足しても遅くならない | プランナのMaterialize + LIMIT打ち切り | （対応不要） | - |
| 集計クエリが遅い（205ms） | 100万行の中間結果を作って集計 | インデックス追加 | 効果なし |
| 同上 | 同上 | クエリ書き換え（先に集計） | 約14倍 |
| 一覧表示が遅い（9.2秒） | 同上（インデックスの代替手段として） | パーティション化（Pruning） | 約232倍 |
| パーティション表が遅い | WHERE句にパーティションキーがない | クエリまたは設計の見直し | 45倍の差 |
| 更新を重ねると遅くなる（4倍劣化） | デッドタプル（dead tuple）蓄積によるテーブル肥大化 | VACUUM | ほぼ回復 |
| 初回アクセスが常に遅い | EBSスループット上限（コールドリード） | shared_buffers増 | 効果なし |
| 同上 | 同上 | EBSスペックアップ | 約4.7倍 |
| 同上 | 同上 |
io_method / effective_io_concurrency調整 |
効果なし |
| 大きなソートが遅い | work_mem不足でディスクソート | work_mem増 | 約24%短縮 |
| 更新系のTPSが伸びない | チェックポイント頻発 | max_wal_size増 | +3.1%（環境依存） |
また、今回の検証条件では、PostgreSQL18へのバージョンアップだけで改善が確認できたケースもありました（いずれも環境やデータ次第で結果は変わり得ます）。
| ケース | PG16 / 17 | PG18 | 効果 |
|---|---|---|---|
| 複合インデックスの先頭列なし検索 | 約48〜51 ms | 4.888 ms | 約10倍（スキップスキャン） |
| 主キーによる自己結合 | 138.155 ms | 70.405 ms | 約2倍（自己結合の除去） |
checkpoint_completion_target |
0.5（手動調整が必要） | 0.9（デフォルト） | 良いデフォルト |
この検証から導ける実務上の指針は以下と考えられます。
- 遅いと感じたら、まずEXPLAIN ANALYZEで実行計画を確認する
-
Rows Removed by Filterが大きい → インデックスが効く可能性が高い - Nested Loopの
rows × loopsが大きい → インデックスではなくクエリ構造の問題の可能性が高い - 中間結果を小さくする書き換え（先に集計・先に絞り込み）を検討する
-
Sort Method: external mergeが出たらwork_mem不足を疑う - ディスクI/Oがボトルネックのときは、I/O発行側（io_method等）の設定では解決しないことがある。ボトルネックの位置を見極める
2. もっと深く学びたい方へ
公式ドキュメント（日本語）
SRA OSS の PostgreSQL16/17/18に関する技術情報
| 資料 | 内容 |
|---|---|
| SRA OSS PostgreSQL16 | PostgreSQL16に関する技術情報 |
| SRA OSS PostgreSQL17 | PostgreSQL17に関する技術情報 |
| SRA OSS PostgreSQL18 | PostgreSQL18に関する技術情報 |
3. まとめ
- インデックスなしのSeq Scanは1,000万行のテーブルで約9.2秒かかり、インデックス作成で約28msまで改善しました（約327倍）
- PostgreSQL18ではEXPLAIN ANALYZEでBuffersが自動表示され、
Index Searchesという新しい出力項目も追加されています - PostgreSQL18のスキップスキャンにより、複合インデックスの先頭列を使わない検索が約10倍速くなるケースを確認しました
- PostgreSQL18の自己結合の除去により、主キーによる自己結合が実行計画から取り除かれ、約2倍速くなるケースを確認しました
- JOINを追加してもプランナのMaterializeとLIMITの早期打ち切りにより、遅くならないケースがあります
- 集計クエリでは100万行の中間結果がボトルネックになり、インデックスを追加しても効果がありませんでした
- 「JOINの前に集計する」クエリ書き換えで約14倍の改善を確認しました
- パーティション・プルーニング（Partition Pruning）はインデックスに匹敵する効果（約232倍）を発揮しますが、WHERE句にパーティションキーがないと100パーティション全アクセスで45倍の差が付きました
- デッドタプル（dead tuple）が50%に達するとテーブルが倍増し実行時間が約4倍に劣化、VACUUMでほぼ回復しました。ただしテーブルサイズは縮みません
- 初回アクセス（コールドリード）の遅さは
shared_buffersでは解決できず、EBSスペックアップで約4.7倍に改善しました。一方TPC-BのTPSは+31%にとどまり、増強の効き方はワークロード依存です - io_methodやeffective_io_concurrencyはフルスキャンでは効果がありませんでした。I/O発行側の最適化は、ボトルネックがディスクのスループット自体だと効きません
- work_mem不足によるディスクソートはwork_mem増で解消し約24%短縮しましたが、ソート処理自体のCPU時間が支配的なため劇的改善には至りませんでした
- checkpoint_completion_targetはPostgreSQL18でデフォルトが0.9になっており、以前は手動で行っていた定番チューニングが不要になっていました
- VACUUMの所要時間はバージョンで単純比較できません。VERBOSE出力を読むと、PostgreSQL16は回収のみ、17は部分凍結、18は全行凍結+可視性マップ整備と、同じコマンドでも仕事量が大きく異なっていました
- チューニングの第一歩は、勘ではなくEXPLAIN ANALYZE・統計ビュー・OSコマンドの数字を見ることです
4. おわりに
「処理が遅い」原因は、一つではありません。「SQLの書き方が悪い」「PostgreSQLの設定が悪い」「スペックが低い」など、さまざまな原因があります。そうした中で、解決の糸口として「実行計画を確認する」「システムビューを確認する」「OSのメトリクスを確認する」といった、チューニングの基本の考え方を、本記事で少しでも身につけていただければと考えました。
実行計画を確認することは、SQLの書き換えや、インデックス・パーティションテーブルの設計といった、具体的な解消策の行動につながります。また、OS面で状況を確認することは、クラウドインフラの設定変更を判断する基準にもなります。
あわせて、「インデックスを貼れば速くなる」という一つの解決策だけを盲目的に信じることの危険性も感じていただけると思います（SQLを少し書き換えるだけで解決することもあります）。そして、バージョンアップに消極的な方にも、毎年のメジャーバージョンアップによる機能面の改善に目を向けるきっかけになればと思います。処理に手を加えなくても、バージョンアップを実施するだけで改善できることもあります。
若手の方、未経験から業界に飛び込んだ方、プログラミング経験は豊富でもミドルウェアの知識が不足していると感じている方、そして逆に、インフラ面の知識はあるけれどSQLにはあまり馴染みがない方。そうした方々のお役に立てればと考えています。皆さんの少しの知識の向上につながり、何かの課題解決の助けになることを願っています。
下記にも記事をまとめてますので、よろしければご参照ください。
https://qiita.com/matsutomu/stocks/4e5f9c7dd139dcc21050
おまけ：検証環境の構築手順
同じ環境を再現したい方向けの構築手順です。
A. EC2インスタンスの準備
AWS Marketplaceで「AlmaLinux 10.2 (Alma Linux 10.2) | Support by ProComputers」を選択します。
Marketplace AMIは事前にサブスクライブ（利用規約への同意）が必要です。サブスクライブせずに起動すると「AWS Marketplace サブスクリプションを必要とする AMI を選択しました」というエラーになります。
| 設定項目 | 値 |
|---|---|
| AMI | AlmaLinux 10.2（ProComputers） |
| インスタンスタイプ | m7i.large |
| EBS | gp3 / 50GiB / 3000 IOPS / 125 MB/s |
| ログインユーザー | ec2-user |
B. OS初期設定
# タイムゾーン設定
$ sudo timedatectl set-timezone Asia/Tokyo
# パッケージ更新
$ sudo dnf update -y
# 必要ツールのインストール
$ sudo dnf install -y wget vim git sysstat iotop
# htopを使いたい場合はEPELリポジトリが必要
$ sudo dnf install -y epel-release
$ sudo dnf install -y htop
C. PostgreSQL16 / 17 / 18 のインストール
# PGDGリポジトリ登録（EL-10用）
$ sudo dnf install -y \
https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 登録確認
$ sudo dnf repolist | grep pgdg
pgdg-common PostgreSQL common RPMs for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg14 PostgreSQL 14 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg15 PostgreSQL 15 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg16 PostgreSQL 16 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg17 PostgreSQL 17 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
pgdg18 PostgreSQL 18 for RHEL / Rocky Linux / AlmaLinux 10 - x86_64
# 3バージョン一括インストール
$ sudo dnf install -y \
postgresql16-server \
postgresql16-contrib \
postgresql17-server \
postgresql17-contrib \
postgresql18-server \
postgresql18-contrib
PostgreSQL18のインストール時には、io_uring用ライブラリ liburing が依存パッケージとして自動的にインストールされます。
# バージョン確認
$ /usr/pgsql-16/bin/postgres --version
postgres (PostgreSQL) 16.14
$ /usr/pgsql-17/bin/postgres --version
postgres (PostgreSQL) 17.10
$ /usr/pgsql-18/bin/postgres --version
postgres (PostgreSQL) 18.4
D. 初期化とポート分け
# 3バージョンの初期化
$ sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
$ sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
$ sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
# ポート設定（16: 5416 / 17: 5417 / 18: 5418）
$ sudo sh -c "echo 'port = 5416' >> /var/lib/pgsql/16/data/postgresql.conf"
$ sudo sh -c "echo 'port = 5417' >> /var/lib/pgsql/17/data/postgresql.conf"
$ sudo sh -c "echo 'port = 5418' >> /var/lib/pgsql/18/data/postgresql.conf"
# 設定確認（dataディレクトリ配下の参照にはsudoが必要）
$ sudo grep '^port' /var/lib/pgsql/16/data/postgresql.conf
port = 5416
# 3バージョン起動
$ sudo systemctl enable --now postgresql-16
$ sudo systemctl enable --now postgresql-17
$ sudo systemctl enable --now postgresql-18
起動後に systemctl status postgresql-18 を確認すると、PostgreSQL18では io worker プロセスが3つ動作していることが確認できます。これはPostgreSQL18の非同期I/O（デフォルト io_method = worker）によるものです。
CGroup: /system.slice/postgresql-18.service
├─13555 /usr/pgsql-18/bin/postgres -D /var/lib/pgsql/18/data/
├─13556 "postgres: logger "
├─13557 "postgres: io worker 0"
├─13558 "postgres: io worker 2"
├─13559 "postgres: io worker 1"
（略）
E. io_uring有効化（PostgreSQL18のみ）
AlmaLinux 10.2（カーネル6.12）はio_uringに対応しています。
# カーネル設定
$ sudo sysctl -w kernel.io_uring_disabled=0
$ echo "kernel.io_uring_disabled=0" | sudo tee -a /etc/sysctl.conf
# PostgreSQL18のio_methodをio_uringに変更
$ sudo sh -c "echo 'io_method = io_uring' >> /var/lib/pgsql/18/data/postgresql.conf"
# 再起動と確認
$ sudo systemctl restart postgresql-18
$ sudo -u postgres psql -p 5418 -c "SHOW io_method;"
`io_method`
-----------
io_uring
F. pgbenchデータの初期化
# データベース作成
$ sudo -u postgres psql -p 5416 -c "CREATE DATABASE pgbench;"
$ sudo -u postgres psql -p 5417 -c "CREATE DATABASE pgbench;"
$ sudo -u postgres psql -p 5418 -c "CREATE DATABASE pgbench;"
# pgbench初期化（スケールファクタ100）
$ sudo -u postgres /usr/pgsql-16/bin/pgbench -i -s 100 -p 5416 pgbench
$ sudo -u postgres /usr/pgsql-17/bin/pgbench -i -s 100 -p 5417 pgbench
$ sudo -u postgres /usr/pgsql-18/bin/pgbench -i -s 100 -p 5418 pgbench
# テーブルサイズ確認
$ sudo -u postgres psql -p 5418 -d pgbench -c "
SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relname LIKE 'pgbench_%'
ORDER BY pg_total_relation_size(oid) DESC;"
relname | pg_size_pretty
-----------------------+----------------
pgbench_accounts | 1495 MB
pgbench_accounts_pkey | 214 MB
pgbench_tellers | 120 kB
pgbench_branches | 56 kB
pgbench_tellers_pkey | 40 kB
pgbench_branches_pkey | 16 kB
pgbench_history | 0 bytes
(7 rows)
3バージョン分の初期化後のディスク使用量は約9.8GB（50GiB中）でした。