0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@matsutomu

pgbenchで見るPostgreSQL 17の変化 ─ t3.microで確かめる実行計画の改善

0
Last updated at Posted at 2026-05-22

pgbenchで見るPostgreSQL 17の変化 ─ t3.microで確かめる実行計画の改善

1. はじめに

本記事は、PostgreSQL16パフォーマンスチューニングシリーズの17対応版の一本です。

前回の記事ではWAL書き込み改善とVACUUM強化を実測しました。本記事は同じt3.micro環境で、実行計画（EXPLAIN）が大きく変わった4つのケースを検証します。

PostgreSQL 17ではプランナが大幅に改善されており、クエリを書き換えなくても速くなるケースが多くあります。実際にEXPLAIN ANALYZEとpgbenchカスタムスクリプトで差を確かめます。

1.1 この記事でわかること

  • CTEプランの無駄なソート・集約がなくなるのか
  • 相関IN句サブクエリがJoinに展開されるのか
  • GROUP BYがインデックス順に最適化されるのか
  • 範囲型の@>演算子でインデックスが使われるのか

2. 検証環境

2.1 環境構成

項目 内容
OS Amazon Linux 2023
インスタンス t3.micro（2vCPU・1GiB）EBS 30GB
PostgreSQL 16 16.12
PostgreSQL 17 17.x（前回記事でバージョンアップ済み）

2.2 postgresql.conf（16・17共通）

shared_buffers = 256MB
work_mem = 4MB
max_connections = 200
autovacuum = off

設定の考え方：実行計画の差を見たいため、チューニングは最小限にしています。autovacuum = offは計測中の自動VACUUM実行を防ぐためです。計測後は必ずonに戻してください。

2.3 設定の適用手順

sudo vi /var/lib/pgsql/data/postgresql.conf
sudo systemctl restart postgresql

# 設定確認
psql -U postgres -d postgres -c "SHOW shared_buffers;"
psql -U postgres -d postgres -c "SHOW autovacuum;"

3. ケース1: CTEプランの改善

3.1 背景

PostgreSQL 16では、CTEスキャン結果がインデックス順でソート済み・ユニークであっても、それを活かせず無駄なSort・HashAggregateが発生していました。PostgreSQL 17ではソート済み・ユニーク性を認識して不要な処理を省きます。

3.2 テストデータ作成

CREATE TABLE t_cte_a (id int PRIMARY KEY, v text);
INSERT INTO t_cte_a SELECT g, md5(g::text) FROM generate_series(1, 100000) g;

CREATE TABLE t_cte_b (id int PRIMARY KEY, v text);
INSERT INTO t_cte_b SELECT * FROM t_cte_a;

VACUUM ANALYZE t_cte_a, t_cte_b;
SET max_parallel_workers_per_gather TO 0;

3.3 実行計画の確認

EXPLAIN ANALYZE
  WITH cte1 AS MATERIALIZED (SELECT id FROM t_cte_b ORDER BY id)
  SELECT count(*) FROM t_cte_a WHERE id IN
  (SELECT id FROM cte1 ORDER BY id);

PostgreSQL 16 の実行計画

Aggregate  (cost=14088.20..14088.21 rows=1 width=8) (actual time=225.290..225.292 rows=1 loops=1)
  CTE cte1
    ->  Index Only Scan using t_cte_b_pkey on t_cte_b  (cost=0.29..2604.29 rows=100000 width=4) (actual time=0.036..9.552 rows=100000 loops=1)
          Heap Fetches: 0
  ->  Nested Loop  (cost=10805.11..11358.90 rows=50000 width=0) (actual time=71.875..220.600 rows=100000 loops=1)
        ->  HashAggregate  (cost=10804.82..10806.82 rows=200 width=4) (actual time=71.825..96.570 rows=100000 loops=1)
              Group Key: cte1.id
              Batches: 5  Memory Usage: 10305kB  Disk Usage: 200kB  ← ディスク溢れ発生
              ->  Sort  (cost=10304.82..10554.82 rows=100000 width=4) (actual time=36.384..41.323 rows=100000 loops=1)
                    Sort Key: cte1.id
                    Sort Method: quicksort  Memory: 3073kB
                    ->  CTE Scan on cte1  (cost=0.00..2000.00 rows=100000 width=4) (actual time=0.038..29.590 rows=100000 loops=1)
        ->  Index Only Scan using t_cte_a_pkey on t_cte_a  (cost=0.29..3.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=100000)
              Index Cond: (id = cte1.id)
              Heap Fetches: 0
Planning Time: 1.153 ms
Execution Time: 226.904 ms

PostgreSQL 17 の実行計画

Aggregate  (cost=8958.36..8958.37 rows=1 width=8) (actual time=68.023..68.025 rows=1 loops=1)
  CTE cte1
    ->  Index Only Scan using t_cte_b_pkey on t_cte_b  (cost=0.29..2604.29 rows=100000 width=4) (actual time=0.046..10.458 rows=100000 loops=1)
          Heap Fetches: 0
  ->  Merge Semi Join  (cost=0.52..6104.07 rows=100000 width=0) (actual time=0.081..63.297 rows=100000 loops=1)  ← ソート済みを活かした結合
        Merge Cond: (t_cte_a.id = cte1.id)
        ->  Index Only Scan using t_cte_a_pkey on t_cte_a  (cost=0.29..2604.29 rows=100000 width=4) (actual time=0.030..10.635 rows=100000 loops=1)
              Heap Fetches: 0
        ->  CTE Scan on cte1  (cost=0.00..2000.00 rows=100000 width=4) (actual time=0.048..31.026 rows=100000 loops=1)
Planning Time: 1.078 ms
Execution Time: 68.511 ms

3.4 pgbenchカスタムスクリプトで計測

-- cte_test.sql
WITH cte1 AS MATERIALIZED (SELECT id FROM t_cte_b ORDER BY id)
SELECT count(*) FROM t_cte_a WHERE id IN
(SELECT id FROM cte1 ORDER BY id);

pgbench -n -c 4 -T 60 -f cte_test.sql -U postgres pgbench_test

3.5 結果

PostgreSQL 16

pgbench (16.12)
transaction type: cte_test.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 367
number of failed transactions: 0 (0.000%)
latency average = 657.016 ms
initial connection time = 35.515 ms
tps = 6.088136 (without initial connection time)

PostgreSQL 17

pgbench (17.8)
transaction type: cte_test.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1331
number of failed transactions: 0 (0.000%)
latency average = 180.449 ms
initial connection time = 37.260 ms
tps = 22.166874 (without initial connection time)
バージョン TPS 平均レイテンシ
PG16 6.09 657ms
PG17 22.17 180ms

約3.6倍のスループット改善

3.6 考察

Sort・HashAggregateが排除されたことで、実行時間が約3.3倍短縮（EXPLAIN ANALYZE: 226ms→68ms）、pgbenchの継続負荷では約3.6倍のTPS改善となりました。

注目すべきポイントはPostgreSQL 16のDisk Usage: 200kBです。HashAggregateがメモリに収まらずディスクに溢れており、連続実行でさらに差が広がっています。t3.microという低メモリ環境でこの差が出ることは、実運用でも十分起こりうるケースです。

4. ケース2: 相関IN句サブクエリの改善

4.1 背景

外側テーブルを参照する相関サブクエリは、PostgreSQL 16ではSubplanとして毎ループ実行されていました。PostgreSQL 17ではHash Joinに展開され、テーブルスキャンが1回で済みます。

4.2 テストデータ作成

CREATE TABLE t_corr_c (id int PRIMARY KEY, c1 int, c2 int);
INSERT INTO t_corr_c SELECT g, g % 100, g % 2 FROM generate_series(1, 10000) g;

CREATE TABLE t_corr_d (id int PRIMARY KEY, c1 int, c2 int);
INSERT INTO t_corr_d SELECT * FROM t_corr_c;

VACUUM ANALYZE t_corr_c, t_corr_d;

4.3 実行計画の確認

EXPLAIN ANALYZE
SELECT * FROM t_corr_c c
WHERE c1 IN (SELECT c1 FROM t_corr_d d WHERE c.c2 = d.c2);

PostgreSQL 16 の実行計画

Seq Scan on t_corr_c c  (cost=0.00..962680.00 rows=5000 width=12) (actual time=0.010..62.429 rows=10000 loops=1)
  Filter: (SubPlan 1)
  SubPlan 1
    ->  Seq Scan on t_corr_d d  (cost=0.00..180.00 rows=5000 width=4) (actual time=0.001..0.004 rows=26 loops=10000)  ← 10,000回繰り返し
          Filter: (c.c2 = c2)
          Rows Removed by Filter: 25
Planning Time: 0.348 ms
Execution Time: 62.811 ms

PostgreSQL 17 の実行計画

Hash Join  (cost=210.00..540.00 rows=10000 width=12) (actual time=2.336..4.819 rows=10000 loops=1)  ← Hash Joinに変換
  Hash Cond: ((c.c2 = d.c2) AND (c.c1 = d.c1))
  ->  Seq Scan on t_corr_c c  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.005..0.618 rows=10000 loops=1)  ← 1回
  ->  Hash  (cost=207.00..207.00 rows=200 width=8) (actual time=2.301..2.302 rows=100 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 12kB
        ->  HashAggregate  (cost=205.00..207.00 rows=200 width=8) (actual time=2.270..2.282 rows=100 loops=1)
              Group Key: d.c2, d.c1
              Batches: 1  Memory Usage: 40kB
              ->  Seq Scan on t_corr_d d  (cost=0.00..155.00 rows=10000 width=8) (actual time=0.007..0.997 rows=10000 loops=1)  ← 1回
Planning Time: 0.571 ms
Execution Time: 5.199 ms

4.4 pgbenchカスタムスクリプトで計測

-- corr_subquery.sql
SELECT count(*) FROM t_corr_c c
WHERE c1 IN (SELECT c1 FROM t_corr_d d WHERE c.c2 = d.c2);

pgbench -n -c 4 -T 60 -f corr_subquery.sql -U postgres pgbench_test

4.5 結果

PostgreSQL 16

pgbench (16.12)
transaction type: corr_subquery.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1167
number of failed transactions: 0 (0.000%)
latency average = 205.887 ms
initial connection time = 35.567 ms
tps = 19.428086 (without initial connection time)

PostgreSQL 17

pgbench (17.8)
transaction type: corr_subquery.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 15897
number of failed transactions: 0 (0.000%)
latency average = 15.092 ms
initial connection time = 36.453 ms
tps = 265.045027 (without initial connection time)
バージョン TPS 平均レイテンシ
PG16 19.43 205ms
PG17 265.05 15ms

約13.6倍のスループット改善

4.6 考察

Subplan（10,000回ループ）からHash Join（1回のスキャン）への変換で、実行時間が約12倍短縮（62ms→5ms）されました。業務系システムで相関サブクエリを多用している場合、クエリを変更せずに大幅な改善が見込めます。

PostgreSQL 16のコスト見積もり（962,680）とPostgreSQL 17（540）の差も極端で、プランナの認識がいかに変わったかがわかります。

5. ケース3: GROUP BYの最適化

5.1 背景

GROUP BYの列順がインデックスと異なる場合、PostgreSQL 16では外部ソートが発生していました。PostgreSQL 17ではインデックス順に合わせてGROUP BYを再整列し、ソートを省略します。

5.2 テストデータ作成

CREATE TABLE t_grp (id int, k1 int, k2 int, k3 int, k4 int);
INSERT INTO t_grp
  SELECT g, g%10, (g/10)%10, (g/100)%10, (g/1000)%10
  FROM generate_series(1, 100000) g;
CREATE INDEX ON t_grp (k1, k2, k3);
VACUUM ANALYZE t_grp;

SET enable_hashagg TO off;
SET max_parallel_workers_per_gather TO 0;

5.3 実行計画の確認

SET enable_hashagg TO off;
SET max_parallel_workers_per_gather TO 0;

EXPLAIN ANALYZE
SELECT k3, k2, k1, count(*) FROM t_grp GROUP BY k3, k2, k1;

PostgreSQL 16 の実行計画

GroupAggregate  (cost=24303.14..26813.14 rows=1000 width=20) (actual time=92.349..136.241 rows=1000 loops=1)
  Group Key: k3, k2, k1
  ->  Sort  (cost=24303.14..24803.14 rows=200000 width=12) (actual time=92.294..117.148 rows=200000 loops=1)
        Sort Key: k3, k2, k1
        Sort Method: external merge  Disk: 4328kB  ← ディスクソート発生
        ->  Seq Scan on t_grp  (cost=0.00..3274.00 rows=200000 width=12) (actual time=0.006..20.217 rows=200000 loops=1)
Planning Time: 0.352 ms
Execution Time: 136.959 ms

PostgreSQL 17 の実行計画

GroupAggregate  (cost=0.29..2882.29 rows=1000 width=20) (actual time=0.093..16.090 rows=1000 loops=1)
  Group Key: k1, k2, k3  ← インデックス順に変換
  ->  Index Only Scan using t_grp_k1_k2_k3_idx on t_grp  (cost=0.29..1872.29 rows=100000 width=12) (actual time=0.071..8.051 rows=100000 loops=1)
        Heap Fetches: 0
Planning Time: 0.310 ms
Execution Time: 16.191 ms

5.4 pgbenchカスタムスクリプトで計測

-- groupby_test.sql
SELECT k3, k2, k1, count(*) FROM t_grp GROUP BY k3, k2, k1;

pgbench -n -c 4 -T 60 -f groupby_test.sql -U postgres pgbench_test

5.5 結果

PostgreSQL 16

pgbench (16.12)
transaction type: groupby_test.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1373
number of failed transactions: 0 (0.000%)
latency average = 174.955 ms
initial connection time = 35.268 ms
tps = 22.863036 (without initial connection time)

PostgreSQL 17

pgbench (17.8)
transaction type: groupby_test.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 3061
number of failed transactions: 0 (0.000%)
latency average = 78.425 ms
initial connection time = 35.931 ms
tps = 51.004084 (without initial connection time)
バージョン TPS 平均レイテンシ
PG16 22.86 175ms
PG17 51.00 78ms

約2.2倍のスループット改善

5.6 考察

GROUP BYの列順を意識せずに書いたクエリでも、PostgreSQL 17のプランナが自動的にインデックス順（k1, k2, k3）へ変換します。

PostgreSQL 16ではexternal merge Disk: 4328kBが発生していて、ソートがメモリに収まらずディスクを使っています。PostgreSQL 17ではIndex Only Scanで直接インデックス順に読み込むため、ソート処理自体が不要になり実行時間が約8.5倍短縮（136ms→16ms）されました。t3.microのような低メモリ環境ではディスクソートが起きやすく、この改善の恩恵が特に大きくなります。

6. ケース4: 範囲型の@>演算子がインデックスを使えるように

6.1 背景

tsrange(...) @> tsのような範囲包含演算子は、PostgreSQL 16ではインデックスを使えずSeq Scanになっていました。PostgreSQL 17では2つの大小比較に展開されてインデックスが使えるようになります。

6.2 テストデータ作成

CREATE TABLE t_range (id int, ts timestamp);
INSERT INTO t_range
  SELECT g, '2024-08-02'::timestamp - (g || 'hour')::interval
  FROM generate_series(1, 10000) g;
CREATE INDEX ON t_range (ts);
VACUUM ANALYZE t_range;

6.3 実行計画の確認

EXPLAIN ANALYZE
SELECT * FROM t_range
WHERE tsrange('-Infinity', '2023-06-15'::timestamp, '()') @> ts;

PostgreSQL 16 の実行計画

Seq Scan on t_range  (cost=0.00..180.00 rows=50 width=12) (actual time=0.970..0.978 rows=64 loops=1)
  Filter: ('(-infinity,"2023-06-15 00:00:00")'::tsrange @> ts)  ← インデックス使えない
  Rows Removed by Filter: 9936
Planning Time: 0.348 ms
Execution Time: 0.999 ms

PostgreSQL 17 の実行計画

Index Scan using t_range_ts_idx on t_range  (cost=0.29..9.57 rows=64 width=12) (actual time=0.005..0.014 rows=64 loops=1)
  Index Cond: ((ts > '-infinity'::timestamp without time zone) AND (ts < '2023-06-15 00:00:00'::timestamp without time zone))  ← 大小比較に展開
Planning Time: 0.492 ms
Execution Time: 0.036 ms

6.4 pgbenchカスタムスクリプトで計測

-- range_test.sql
SELECT count(*) FROM t_range
WHERE tsrange('-Infinity', '2023-06-15'::timestamp, '()') @> ts;

pgbench -n -c 4 -T 60 -f range_test.sql -U postgres pgbench_test

6.5 結果

PostgreSQL 16

pgbench (16.12)
transaction type: range_test.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 62985
number of failed transactions: 0 (0.000%)
latency average = 3.808 ms
initial connection time = 34.999 ms
tps = 1050.302599 (without initial connection time)

PostgreSQL 17

pgbench (17.8)
transaction type: range_test.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 669947
number of failed transactions: 0 (0.000%)
latency average = 0.358 ms
initial connection time = 36.383 ms
tps = 11172.487384 (without initial connection time)
バージョン TPS 平均レイテンシ
PG16 1,050.30 3.808ms
PG17 11,172.49 0.358ms

約10.6倍のスループット改善

6.6 考察

@>演算子が大小比較に展開されたことで、インデックスが使われるようになり約28倍高速化（0.999ms→0.036ms）されました。PostgreSQL 16では9,936行をフルスキャンしてフィルタリングしていたのに対し、PostgreSQL 17ではインデックスで64行に絞り込んでいます。時系列データや予約管理のような範囲検索を多用するシステムで効果が期待できます。

7. まとめ

ケース PG16 実行時間 PG17 実行時間 改善倍率（EXPLAIN ANALYZE）
CTEプラン 226ms 68ms 約3.3倍
相関IN句 62ms 5ms 約12倍
GROUP BY 136ms 16ms 約8.5倍
範囲型@> 0.999ms 0.036ms 約28倍
ケース PG16 TPS PG16 レイテンシ PG17 TPS PG17 レイテンシ 改善倍率（pgbench）
CTEプラン 6.09 657ms 22.17 180ms 約3.6倍
相関IN句 19.43 205ms 265.05 15ms 約13.6倍
GROUP BY 22.86 175ms 51.00 78ms 約2.2倍
範囲型@> 1,050.30 3.808ms 11,172.49 0.358ms 約10.6倍

PostgreSQL 17のプランナ改善は、既存のクエリを変更せずに速くなるという点で非常に実用的です。特に相関サブクエリと範囲型の改善は業務系システムで効いてくるケースが多いと思います。

8. 計測後の後片付け

# autovacuumを必ず戻す
sudo vi /var/lib/pgsql/data/postgresql.conf
# autovacuum = on に変更

sudo systemctl restart postgresql
psql -U postgres -d postgres -c "SHOW autovacuum;"

9. 参考資料

ページ URL
PostgreSQL 17 リリースノート https://www.postgresql.org/docs/17/release-17.html
SRA OSS PostgreSQL 17検証レポート https://www.sraoss.co.jp/

10. さいごに

今回はプランナ改善に絞って検証しましたが、クエリを一切変えずにここまで差が出るというのは改めて驚きでした。特に相関IN句の13.6倍、範囲型の10.6倍はt3.microという安価な環境でも再現できており、バージョンアップだけで得られる恩恵の大きさを実感しています。次回もPostgreSQL 17の改善点を取り上げる予定です。引き続きよろしくお願いします。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?