🕵️

PostgreSQL 注目機能改善 #3: 実行計画編

FORCIA Tech Blog 運営チーム

2025/08/22に公開

まえがき

PostgreSQLでは頻繁にクエリプランが改善されており、メジャーバージョンが上がる度に何かしらの変更が加わっています。
ひとくちにクエリプランの改善と言ってもその内容はさまざまで、新しいプランの追加、同じプランの処理の改善、他のプランへの暗黙的な置換などがあります。
本稿ではその中でも、プランナ制御用に新しく追加されたパラメータ^[1]に関連する機能に絞って紹介をしたのち、簡単な検証を実施します。

連載について

本連載はPostgreSQLの比較的新しいバージョンで導入、改善された機能に注目し、その紹介と簡単な検証をする連載です。
RDBは枯れた技術と評されることが多いですが、PostgreSQLは1年ごとにメジャーアップデートがあり、その度に多くの機能追加や改善が施されています。
本連載では膨大な改善の中から特定の機能に着目し、各メジャーバージョンでどのような改善が導入されたかにフォーカスします。

検証について

※本稿での検証においては Docker Hub にある postgres:13.21/14.18/15.13/16.9/17.5 のimageを使用しました。
Docker が使える環境では以下のようなワンライナーで同等の環境を再現できるかと思います。

docker run -itd --name postgres -e POSTGRES_PASSWORD=postgres postgres:(version)

Memoize(PostgreSQL 14)

Memoize は、 nested loop join で特定の行が頻繁に参照される場合に行をキャッシュすることで高速化を図るものです。
外部表のカーディナリティが低く、駆動表のカーディナリティが高い場合、駆動表の特定の行がループで何度も呼び出されることになります。そこでループの度に直接テーブルやインデックスをスキャンする代わりにキャッシュを参照することでオーバーヘッドを減らす、というのが目的です。

詳しくはこちらの資料が明るいです。

検証

PostgreSQL 13, 14 で以下のようなテーブル、クエリを実行します。
test.c1 は 10で割ったあまりとしておりカーディナリティが低い一方、 test2.c1 は連番のままとしてカーディナリティが高くなるようにしています。また、nested loop join を強要するため他の結合を縛っています。

-- カーディナリティの低いテーブル
drop table if exists test;
create table test as select generate_series(1, 10000) % 10 as c1;
create index on test (c1);
analyze test;

-- カーディナリティの高いテーブル
drop table if exists test2;
create table test2 as select generate_series(1, 10000) as c1;
create index on test2 (c1);
analyze test2;

set enable_hashjoin to off;
set enable_mergejoin to off;
explain analyze select * from test left join test2 using (c1);

PostgreSQL 13 では以下のような実行計画が得られました。

                                                            QUERY PLAN                             
-----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.29..3400.00 rows=10000 width=12) (actual time=0.024..5.571 rows=10000 loops=1)
   ->  Seq Scan on test  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.008..0.346 rows=10000 loops=1)
   ->  Index Only Scan using test2_c1_idx on test2  (cost=0.29..0.31 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=10000)
         Index Cond: (c1 = test.c1)
         Heap Fetches: 0
 Planning Time: 0.072 ms
 Execution Time: 5.767 ms
(7 rows)

一方で PostgreSQL 14 では以下のような実行計画が得られました。

                                                              QUERY PLAN                           
--------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.30..408.65 rows=10000 width=12) (actual time=0.012..3.346 rows=10000 loops=1)
   ->  Seq Scan on test  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.005..0.411 rows=10000 loops=1)
   ->  Memoize  (cost=0.30..0.34 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=10000)
         Cache Key: test.c1
         Cache Mode: logical
         Hits: 9990  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 1kB
         ->  Index Only Scan using test2_c1_idx on test2  (cost=0.29..0.33 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=10)
               Index Cond: (c1 = test.c1)
               Heap Fetches: 9
 Planning Time: 0.230 ms
 Execution Time: 3.550 ms
(11 rows)

どちらも test2 に対して10000回ループが走っていますが、対象がindexか、 Memoize されたキャッシュかの違いが出ていることが分かります。

また、PostgreSQL 14 で set enable_memoize to off; とすることで、13と同じ実行計画が得られます。

Presorted Aggregate(PostgreSQL 16)

Presorted Aggregate は、集約関数でソートが必要な場合にそれ以前のソート結果を利用できるようになるものです。
array_agg のようなソート処理を要する集約関数は GroupAggregate ノードでソート処理が実行されますが、従来はインデックスなどでソート済の場合も必ずソートが行われていました。この改善によってソートが省略できる場合は group by クエリの高速化が見込めそうです。

検証

PostgreSQL 15, 16 で以下のようなテーブルを作り、クエリを実行します。

drop table if exists test;
-- そこそこサイズのある適当なテーブル
create table test as select generate_series(1, 1000000) as c1, generate_series(1, 1000000) as c2;
analyze test;

-- 比較を見やすくするためjitをoff
set jit to off;
explain analyze select array_agg(c1 order by c1) from test group by c2;

PostgreSQL 15 では以下のような実行計画が得られました。

                                                         QUERY PLAN                                
----------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=127757.34..147757.34 rows=1000000 width=36) (actual time=177.821..773.028 rows=1000000 loops=1)
   Group Key: c2
   ->  Sort  (cost=127757.34..130257.34 rows=1000000 width=8) (actual time=177.801..229.564 rows=1000000 loops=1)
         Sort Key: c2
         Sort Method: external merge  Disk: 17664kB
         ->  Seq Scan on testl  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.059..56.821 rows=1000000 loops=1)
 Planning Time: 0.042 ms
 Execution Time: 797.485 ms
(8 rows)

一方で、 PostgreSQL 16 では以下のような実行計画が得られました。

                                                         QUERY PLAN                                
----------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=127812.34..147812.34 rows=1000000 width=36) (actual time=137.099..460.096 rows=1000000 loops=1)
   Group Key: c2
   ->  Sort  (cost=127812.34..130312.34 rows=1000000 width=8) (actual time=137.087..190.132 rows=1000000 loops=1)
         Sort Key: c2, c1
         Sort Method: external merge  Disk: 17672kB
         ->  Seq Scan on testl  (cost=0.00..14480.00 rows=1000000 width=8) (actual time=0.054..55.467 rows=1000000 loops=1)
 Planning Time: 0.043 ms
 Execution Time: 484.560 ms
(8 rows)

差分は Sort Key に c1 が含まれるかどうかの違いです。実行計画には現れませんが、15は GroupAggregate ノード( array_agg の集約処理)でソートが実行されている一方、16は事前にソートした結果を利用することで GroupAggregate にかかる時間が短縮されています。
今回はインデックスを貼っていませんが、 c2, c1 の順でインデックスを貼ることにより Sort ノードをなくすことができます^[2]。

こちらも、 set enable_presorted_aggregate to off; とすることで16でも15と同様の結果が得られます。

Group by Reordering(PostgreSQL 17)

Group by Reordering は、 order by key に合わせて group by key の順序を変えるようになる機能です。
従来は group by key と order by key が異なる場合に再度ソートが必要でしたが、これをなくすことが狙いのようです。

検証

PostgreSQL 16, 17 で以下のようなテーブル、クエリを実行します。

drop table if exists test;
create table test as select generate_series(1, 10000) % 10 as c1, generate_series(1, 10000) as c2, (random() * 10000)::int as c3;
-- group by c1, c2 に対して効くようなインデックスを貼る
create index on test (c1, c2, c3);
analyze test;

explain analyze select array_agg(c3) from test group by c2, c1;

PostgreSQL 16 では以下のような実行計画が得られました。

                                                     QUERY PLAN                                    
---------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=819.39..1044.39 rows=10000 width=40) (actual time=1.356..4.202 rows=10000 loops=1)
   Group Key: c2, c1
   ->  Sort  (cost=819.39..844.39 rows=10000 width=12) (actual time=1.350..1.605 rows=10000 loops=1)
         Sort Key: c2, c1
         Sort Method: quicksort  Memory: 775kB
         ->  Seq Scan on test  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.005..0.603 rows=10000 loops=1)
 Planning Time: 0.064 ms
 Execution Time: 4.471 ms
(8 rows)

一方で PostgreSQL 17 では以下のような実行計画が得られました。

                                                                  QUERY PLAN                       
----------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.29..514.29 rows=10000 width=40) (actual time=0.015..2.983 rows=10000 loops=1)
   Group Key: c1, c2
   ->  Index Only Scan using test_c1_c2_c3_idx on test  (cost=0.29..314.29 rows=10000 width=12) (actual time=0.009..0.598 rows=10000 loops=1)
         Heap Fetches: 0
 Planning Time: 0.060 ms
 Execution Time: 3.231 ms
(6 rows)

見ての通り16ではインデックスを利用できず Seq Scan が走っていますが、17では Group Key が逆転することで c1, c2, c3 の順で貼られたインデックスを利用したスキャンが走るようになっています。
こちらも、 set enable_group_by_reordering to off; とすることで17でも16と同様の結果が得られます。

注意点として、自分が検証した限りこの機能は order by key が不定の場合に group by key を都合よく並び替える という内容にとどまっているようでした。理由は以下の2点です。

order by key がインデックス順に並んでいれば古いバージョンでも group by key の並び替えは起きる
order by key に group by key と異なる順序を明示的に指定した場合、reorderingが走らない場合がある

前者の検証として、 PostgreSQL 16 に以下のクエリを投げます。

-- group by key と違う、インデックスが有効な order by key を与える
explain analyze select array_agg(c3) from test group by c2, c1 order by c1, c2;

実行計画は以下のようになりました。古いバージョンでも Group Key が並び変わり、インデックスを活用できていることが分かります。

                                                                  QUERY PLAN                       
----------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.29..514.29 rows=10000 width=40) (actual time=0.016..2.962 rows=10000 loops=1)
   Group Key: c1, c2
   ->  Index Only Scan using test_c1_c2_c3_idx on test  (cost=0.29..314.29 rows=10000 width=12) (actual time=0.009..0.518 rows=10000 loops=1)
         Heap Fetches: 0
 Planning Time: 0.059 ms
 Execution Time: 3.195 ms
(6 rows)

後者の検証として、 PostgreSQL 17 に以下のクエリを投げます。

-- インデックスが効かない order by key を明示する
explain analyze select array_agg(c3) from test group by c2, c1 order by c2, c1;

実行計画は以下のようになり、 Group Key の並び替えは起きませんでした^[3]。

                                                     QUERY PLAN                                    
---------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=819.39..1044.39 rows=10000 width=40) (actual time=1.262..3.999 rows=10000 loops=1)
   Group Key: c2, c1
   ->  Sort  (cost=819.39..844.39 rows=10000 width=12) (actual time=1.256..1.511 rows=10000 loops=1)
         Sort Key: c2, c1
         Sort Method: quicksort  Memory: 697kB
         ->  Seq Scan on test  (cost=0.00..155.00 rows=10000 width=12) (actual time=0.007..0.565 rows=10000 loops=1)
 Planning Time: 0.081 ms
 Execution Time: 4.267 ms
(8 rows)

むすび

今回の性能検証では具体例を出せていませんが、検証中にこれらの最適化が逆に性能を落とす場合も確かにありそうという感覚はあったので、細かいチューニングをする上でパラメータでの制御を必要とするケースも出てくるのではないかと思いました。
フォルシアでは array_agg のような集約時の順序が重要なクエリを用いる機会も多いため、今回紹介した改善の中では特にgroup byでのソートがオミットされる Presorted Aggregate の恩恵は大きいのではないかと思います。これをモチベーションにメジャーバージョンも上げていきたいですね。

この記事を書いた人

吉田侑弥
2020年新卒入社
連載の構想は16の新機能を眺めていて思いついたのですが、遅筆なせいで気付けばもう18のリリースが迫っています。

脚注

enable_nestloop のようなプランナにその実行計画の選択を許容するかを決定するパラメータを指します。 ↩︎
15でも同様にSortノードがなくなるため見かけ上実行計画の差はなくなりますが、GroupAggregateノード内でソート処理が走ることには変わりないため16の方がより高速に結果を得られます。 ↩︎
これに関してはテーブル構造やクエリを選べば並び変わる可能性はあるかもしれません ↩︎

FORCIA Tech BlogPublication

――「つなげれば、見える。」――膨大・複雑なデータから必要な情報を的確に探し出す検索テクノロジーを基にしたシステム開発・サービス提供並びに、コンサルティングで企業のDXを推進するフォルシア株式会社テックブログです。エンジニア積極募集中！！

まえがき

Memoize(PostgreSQL 14)

検証

Presorted Aggregate(PostgreSQL 16)

検証

Group by Reordering(PostgreSQL 17)

検証

むすび

この記事を書いた人

Discussion