PostgreSQL16でパーティショニング（Partitioning）を実測する ─ プルーニングが実行計画にどう現れるか

1. はじめに

本記事では、大テーブルへのSeq Scanに対してパーティショニング（partitioning）がどう有効かを実測で確認します。work_mem や shared_buffers を増やしても、そもそも読むブロック数（block count）が多ければ根本解決にはなりません。

パーティショニングは「読まなくていいパーティションを実行計画（execution plan）の段階で除外する」パーティションプルーニング（partition pruning）によってこの問題を解決します。

pgbench_accounts（5,000万行）をレンジパーティション（range partition）で分割し、プルーニングが実行計画にどう現れるかを実測で確認します。

1.1 検証環境

項目	内容
OS	AmazonLinux2023
PostgreSQL	16
インスタンスタイプ	t3.micro (2 vCPU、1GiB)
データ	pgbench scale factor 500（約5,000万行）

1.2 パーティショニングの基本

PostgreSQL 10以降、宣言的パーティショニングが使えます。分割方式は3種類です。

方式	用途
RANGE	日付・連番など範囲で分割
LIST	区分コード・地域コードなど値リストで分割
HASH	均等分散（特定の範囲クエリがない場合）

今回は aid（口座ID、1〜50,000,000）をキーにRANGEパーティションで分割します。

1.3 事前準備

今回は、設定変更は実施しませんが、別の記事で実施した内容を引き継いで、postgresql.confは下記内容で設定しています。

パラメータ	値	理由・狙い
shared_buffers	64MB	少量の共有メモリでバッファに乗らないようにする
work_mem	4MB	pgbenchの並列度（-c）を上げた際、接続数 × work_mem のメモリが消費されます。t3.microで並列度50〜100を試すなら、小さく保たないと即座に落ちます。
min_wal_size	512MB	WALファイルの削除・再作成のオーバーヘッド（LWLockの原因の一つ）を減らすため、少し多めに確保しておきます。
max_wal_size	2GB	ロック観察中にチェックポイントが走ると、I/O負荷で待機イベントが書き換わってしまいます。大きくしてチェックポイントを遠ざけます。
checkpoint_timeout	15min	チェックポイント間隔を長くしておきます。
checkpoint_completion_target	0.9	書き込みを穏やかにしておきます。

pgbenchの初期化はスケール500で実施します。

# pgbench_accounts: 50,000,000件のデータを投入
$ pgbench -i -s 500 -U postgres pgbench_test

# データサイズを確認（1テーブルで約 1.5GB）
$ psql -U postgres -d pgbench_test -c "SELECT relname, round(pg_relation_size(relid) / 1024.0^3, 3) AS table_gb, round(pg_indexes_size(relid) / 1024.0^3, 3) AS index_gb, round(pg_total_relation_size(relid) / 1024.0^3, 3) AS total_gb FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';"
     relname      | table_gb | index_gb | total_gb
------------------------------------------------
 pgbench_accounts |    6.254 |    1.046 |    7.301
(1 row)

./pgsql/partition_test.sqlとして下記のSQLを保存します。

SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS)
SELECT aid, abalance
FROM pgbench_accounts
WHERE aid BETWEEN 24000000 AND 25000000
OR    aid BETWEEN 26000000 AND 27000000;

上記SQLを利用して、実行計画を取得します。

# 統計情報やキャッシュをクリア
$ psql -U postgres -d pgbench_test -c "SELECT pg_stat_reset();"
$ sudo systemctl stop postgresql
$ sync && echo 3 | sudo tee /proc/sys/vm/drop_caches
$ sudo systemctl start postgresql

# 実行計画を取得
$ psql -U postgres -d pgbench_test -f ./pgsql/partition_test.sql
Seq Scan on pgbench_accounts  (cost=0.00..1819673.00 rows=1951830 width=97) (actual time=23556.741..50076.996 rows=2000
002 loops=1)
   Filter: (((aid >= 24000000) AND (aid <= 25000000)) OR ((aid >= 26000000) AND (aid <= 27000000)))
   Rows Removed by Filter: 47999998
   Buffers: shared hit=32 read=819641
 Planning:
   Buffers: shared hit=78
 Planning Time: 1.695 ms
 Execution Time: 50151.729 ms
(8 rows)

この実行計画が、パーティションテーブルを作成する事でどう変わるかを確認していきます。

2. パーティションテーブルを作成する

別のテーブルとしてpgbench_accounts_partを作成して、検証していきます。

2.1 親テーブルと子テーブルの定義

./pgsql/create_partition.sqlとしてCREATE TABLEのSQLを保存します。

-- vi ./pgsql/create_partition.sql
DROP TABLE IF EXISTS pgbench_accounts_part;

-- 親テーブル（データは持たない）
CREATE TABLE pgbench_accounts_part (
  aid        INTEGER NOT NULL,
  bid        INTEGER,
  abalance   INTEGER,
  filler     CHARACTER(84)
) PARTITION BY RANGE (aid);

-- 子テーブル（各パーティション）
CREATE TABLE pgbench_accounts_part_1
  PARTITION OF pgbench_accounts_part
  FOR VALUES FROM (1) TO (10000000);

CREATE TABLE pgbench_accounts_part_2
  PARTITION OF pgbench_accounts_part
  FOR VALUES FROM (10000001) TO (20000001);

CREATE TABLE pgbench_accounts_part_3
  PARTITION OF pgbench_accounts_part
  FOR VALUES FROM (20000001) TO (30000001);

CREATE TABLE pgbench_accounts_part_4
  PARTITION OF pgbench_accounts_part
  FOR VALUES FROM (30000001) TO (40000001);

CREATE TABLE pgbench_accounts_part_5
  PARTITION OF pgbench_accounts_part
  FOR VALUES FROM (40000001) TO (50000001);

SQLを実行して、パーティションテーブルを作成します。

$ psql -U postgres -d pgbench_test -f ./pgsql/create_partition.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

2.2 データのコピーとインデックス作成

元々pgbenchで作成したデータを今回のパーティションテーブルにデータをコピー（INSERT）する形で登録します。

-- vi ./pgsql/partition_data_insert.sql
-- 元テーブルからデータをコピー
INSERT INTO pgbench_accounts_part
SELECT * FROM pgbench_accounts;

-- 各パーティションにインデックスを作成
CREATE INDEX ON pgbench_accounts_part (aid);

-- 統計情報（statistics）を更新
ANALYZE pgbench_accounts_part;

データを実際に投入します。

$ psql -U postgres -d pgbench_test -f ./pgsql/partition_data_insert.sql
INSERT 0 50000000
CREATE INDEX
ANALYZE

事前準備で利用したSQLを修正して、テーブルをpgbench_accounts_partに変更します。

$ cp ./pgsql/partition_test.sql ./pgsql/partition_test_2.sql
$ vi ./pgsql/partition_test_2.sql

-- 強制的にパラレルクエリを無効化
SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, BUFFERS)
SELECT aid, abalance
FROM pgbench_accounts_part
WHERE aid BETWEEN 24000000 AND 25000000
OR    aid BETWEEN 26000000 AND 27000000;

2.3 プルーニング（Pruning）の効果を実行計画で確認する

実行計画の出力例です。

Seq Scan on pgbench_accounts_part_3 pgbench_accounts_part  (cost=0.00..363935.00 rows=1931759 width=8) (actual time=309
4.334..9157.961 rows=2000002 loops=1)
   Filter: (((aid >= 24000000) AND (aid <= 25000000)) OR ((aid >= 26000000) AND (aid <= 27000000)))
   Rows Removed by Filter: 7999998
   Buffers: shared hit=32 read=163903
 Planning:
   Buffers: shared hit=155
 Planning Time: 1.325 ms
 Execution Time: 9232.364 ms
(8 rows)

pgbench_accounts_part_3のみにアクセスしており、他の4パーティションは完全にスキップされています。フィルタで除外した行数も約1/5に減り、読むブロック数も819,641から163,903へと約1/5になっています。
結果、実行時間も50秒から9秒程度と短くなっています。

ー	Rows Removed by Filter	Buffers	Execution Time
通常テーブル	47999998	shared hit=32 read=819641	50,151ms
パーティションテーブル	7999998	shared hit=32 read=163903	9,232 ms

2.4 プルーニングが効かないケース（WHERE句にパーティションキーがない）

パーティションテーブルの効果が無いケースを確認してみます。

-- 強制的にパラレルクエリを無効化
SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, BUFFERS)
SELECT aid, abalance
FROM pgbench_accounts_part
WHERE abalance > 5000;  -- パーティションキー（aid）ではなくabalanceで検索

Appendと言うブロックが現れて、全パーティション5個すべてアクセスする形になってます。

 Append  (cost=0.00..1444673.51 rows=5 width=8) (actual time=50135.879..50135.881 rows=0 loops=1)
   Buffers: shared hit=320 read=819355
   ->  Seq Scan on pgbench_accounts_part_1  (cost=0.00..288935.00 rows=1 width=8) (actual time=9231.448..9231.448 rows=0 loops=1)
         Filter: (abalance > 5000)
         Rows Removed by Filter: 10000000
         Buffers: shared hit=32 read=163903
   ->  Seq Scan on pgbench_accounts_part_2  (cost=0.00..288933.49 rows=1 width=8) (actual time=10230.603..10230.603 rows=0 loops=1)
         Filter: (abalance > 5000)
         Rows Removed by Filter: 10000000
         Buffers: shared hit=32 read=163903
   ->  Seq Scan on pgbench_accounts_part_3  (cost=0.00..288935.00 rows=1 width=8) (actual time=10239.711..10239.711 rows=0 loops=1)
         Filter: (abalance > 5000)
         Rows Removed by Filter: 10000000
         Buffers: shared hit=96 read=163839
   ->  Seq Scan on pgbench_accounts_part_4  (cost=0.00..288935.00 rows=1 width=8) (actual time=10224.933..10224.933 rows=0 loops=1)
         Filter: (abalance > 5000)
         Rows Removed by Filter: 10000000
         Buffers: shared hit=96 read=163839
   ->  Seq Scan on pgbench_accounts_part_5  (cost=0.00..288935.00 rows=1 width=8) (actual time=10209.178..10209.178 rows=0 loops=1)
         Filter: (abalance > 5000)
         Rows Removed by Filter: 10000000
         Buffers: shared hit=64 read=163871
 Planning:
   Buffers: shared hit=231
 Planning Time: 0.734 ms
 Execution Time: 50135.947 ms
(26 rows)

パーティションキー（aid）がWHERE句にないため、全パーティションをスキャンしています。この場合、パーティショニングの恩恵はなく、むしろ Append ノードの分だけオーバーヘッドが増えます。
パーティションキーの選び方がプルーニングの可否を決めるという点がここから読み取れます。

2.5 静的プルーニング vs 動的プルーニング

プルーニングには2種類あります。

種類	タイミング	条件
静的プルーニング	プラン生成時	WHERE句の値がリテラル（定数）の場合
動的プルーニング	実行時	WHERE句の値がパラメータ（$1 など）の場合

静的プルーニング：リテラルで検索するケースです。プラン生成時点で pgbench_accounts_part_1 に絞られています。

SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pgbench_accounts_part WHERE aid = 12345;

実行計画はこんな感じです。

Index Scan using pgbench_accounts_part_1_aid_idx on pgbench_accounts_part_1 pgbench_accounts_part  (cost=0.43..8.45 rows=1 width=97) (actual time=1.352..1.354 rows=1 loops=
1)
   Index Cond: (aid = 12345)
   Buffers: shared read=4
 Planning:
   Buffers: shared hit=128 dirtied=1
 Planning Time: 0.474 ms
 Execution Time: 1.770 ms
(7 rows)

動的プルーニング：プリペアード変数を使うケースです。実行時にパラメータ値が評価され、同様にプルーニングが効いています。

PREPARE get_account(int) AS
  SELECT * FROM pgbench_accounts_part WHERE aid = $1;
SET plan_cache_mode = force_generic_plan;
SET max_parallel_workers_per_gather = 0;
EXPLAIN EXECUTE get_account(12345);

Subplans Removed: 4で、残りのパーティションが実行計画時に対象から除外されている事が確認できます。

Append  (cost=0.43..42.29 rows=5 width=97)
   Subplans Removed: 4
   ->  Index Scan using pgbench_accounts_part_1_aid_idx on pgbench_accounts_part_1  (cost=0.43..8.45 rows=1 width=97)
         Index Cond: (aid = $1)
(4 rows)

PostgreSQL 13以降、動的プルーニングはデフォルトで有効です（enable_partition_pruning = on）。プルーニングを無効にして動作の違いを確認することもできます。

-- プルーニングを無効にして違いを確認
SET max_parallel_workers_per_gather = 0;
SET enable_partition_pruning = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pgbench_accounts_part WHERE aid = 12345;

Append  (cost=0.43..42.29 rows=5 width=97) (actual time=0.013..5.017 rows=1 loops=1)
   Buffers: shared hit=4 read=12
   ->  Index Scan using pgbench_accounts_part_1_aid_idx on pgbench_accounts_part_1  (cost=0.43..8.45 rows=1 width=97) (actual time=0.012..0.013 rows=1 loops=1)
         Index Cond: (aid = 12345)
         Buffers: shared hit=4
   ->  Index Scan using pgbench_accounts_part_2_aid_idx on pgbench_accounts_part_2  (cost=0.43..8.45 rows=1 width=97) (actual time=1.097..1.097 rows=0 loops=1)
         Index Cond: (aid = 12345)
         Buffers: shared read=3
   ->  Index Scan using pgbench_accounts_part_3_aid_idx on pgbench_accounts_part_3  (cost=0.43..8.45 rows=1 width=97) (actual time=1.379..1.379 rows=0 loops=1)
         Index Cond: (aid = 12345)
         Buffers: shared read=3
   ->  Index Scan using pgbench_accounts_part_4_aid_idx on pgbench_accounts_part_4  (cost=0.43..8.45 rows=1 width=97) (actual time=1.022..1.022 rows=0 loops=1)
         Index Cond: (aid = 12345)
         Buffers: shared read=3
   ->  Index Scan using pgbench_accounts_part_5_aid_idx on pgbench_accounts_part_5  (cost=0.43..8.45 rows=1 width=97) (actual time=1.501..1.501 rows=0 loops=1)
         Index Cond: (aid = 12345)
         Buffers: shared read=3
 Planning:
   Buffers: shared hit=264
 Planning Time: 0.744 ms
 Execution Time: 5.105 ms
(21 rows)

3. まとめ

3.1 プルーニングの確認ポイント

確認ポイント	実行計画での見方	判断基準
プルーニングが効いているか	アクセスするパーティション数	1つだけなら効いている
プルーニングが効かない原因	WHERE句にパーティションキーがあるか	なければ全スキャン
静的 vs 動的プルーニング	EXPLAIN vs EXPLAIN EXECUTE	どちらも効く

3.2 パーティショニング設計のポイント

パーティショニングの効果を最大化するには
  │
  ├─ パーティションキーは検索条件に必ず含まれる列を選ぶ
  │    └─ WHERE句にパーティションキーがなければ全スキャン（プルーニング無効）
  │
  ├─ パーティション数は数十〜数百が目安
  │    ├─ 少なすぎると1パーティションが大きくなりすぎてプルーニング効果が薄れる
  │    └─ 多すぎるとプラン生成コスト（planning cost）が増加する
  │
  └─ 静的・動的どちらのプルーニングも活用できる設計にする
       ├─ リテラル検索 → 静的プルーニング（プラン生成時に除外）
       └─ パラメータ検索 → 動的プルーニング（実行時に除外）