まえがき
本連載はPostgreSQLの比較的新しいバージョンで導入、改善された機能に注目し、その紹介と簡単な検証をする連載です。
RDBは枯れた技術と評されることが多いですが、PostgreSQLは1年ごとにメジャーアップデートがあり、その度に多くの機能追加や改善が施されています。
本連載では膨大な改善の中から特定の機能に着目し、各メジャーバージョンでどのような改善が導入されたかにフォーカスします。
本稿ではwindow関数の改善を紹介します。
PostgreSQL 15, 16 では window関数の性能が改善しました。
window関数は主に分析系のクエリなどでよく使われるイメージですが、フォルシアでも複雑な検索クエリを書く際にしばしば利用します。本稿ではwindow関数の改善内容を紹介したのち、簡単な検証を実施します。
検証について
※本稿での検証においてはDocker Hubにある postgres:14.13/15.8/16.4のimageを使用しました。
Dockerが使える環境では以下のようなワンライナーで同等の環境を再現できるかと思います。
docker run -itd --name postgres -e POSTGRES_PASSWORD=postgres postgres:(version)
また、クエリの例では以下手順で作ったテーブルを注釈なく使用します。
-- ユーザーが投稿した内容を格納するテーブルを想定
create table posts (
post_id integer primary key,
user_id integer not null,
post text not null
);
-- 100人のユーザーがそれぞれ1000件ずつの投稿を持っている
insert into posts select generate_series(1,100000), generate_series(0,99999) % 100 + 1, gen_random_uuid();
analyze posts;
window関数を適用する必要がない行への処理が打ち切られる (PostgreSQL 15)
PostgreSQL 15 では、row_number, rank, count のwindow関数を実行した際、window関数を適用する必要がない行への処理が打ち切られるようになりました。
select * from (
select
row_number() over (partition by user_id order by post_id desc) as rn,
*
from posts where user_id < 50
) s
where rn <= 3;
上記のクエリは、「user_idが50未満のユーザーの、最新の投稿3件をそれぞれ取得する」というものです。このようなクエリを実行する場合、14までのプランナであれば、以下のような手順を踏む必要がありました。
- postsテーブルから、user_id が50未満のレコードを抽出する
-
- の結果をuser_id, post_id desc でソートする
- ソートした結果に対してrow_numberを割り振る
- 割り振ったrow_numberが3以下のレコードを抽出する
これが15以降では改善され、以下のような手順で実行されるようになりました。
- postsテーブルから、user_id が50未満のレコードを抽出する
-
- の結果をuser_id, post_id desc でソートする
- ソートした結果に対してrow_numberを割り振り、3以下のものを抽出する（4以上になるレコードには適用せず、次のpartitionの割り振りを始める）
要するに、従来は一度対象行の全てに割り振った後で絞り込む必要があったが、割り振りと絞り込みが同時に実行できるようになった、ということです。これはpartition keyあたりの対象行数が多いほど、また最終的に取得したい行数が少ないほど効果的です。
実際にPostgreSQL 14, 15でexplain analyzeした結果を見てみます。
14
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on s (cost=5999.67..7591.19 rows=16323 width=53) (actual time=39.716..99.821 rows=147 loops=1)
Filter: (s.rn <= 3)
Rows Removed by Filter: 48853
-> WindowAgg (cost=5999.67..6979.07 rows=48970 width=53) (actual time=39.714..88.315 rows=49000 loops=1)
-> Sort (cost=5999.67..6122.09 rows=48970 width=45) (actual time=39.706..53.010 rows=49000 loops=1)
Sort Key: posts.user_id, posts.post_id DESC
Sort Method: external merge Disk: 2648kB
-> Seq Scan on posts (cost=0.00..2185.00 rows=48970 width=45) (actual time=0.005..14.290 rows=49000 loops=1)
Filter: (user_id < 50)
Rows Removed by Filter: 51000
Planning Time: 0.618 ms
Execution Time: 100.349 ms
(12 rows)
14ではWindowAggの後にSubquery Scanが走り、その中でfilterが実行されていることがわかります。
15
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=5987.50..6964.04 rows=48827 width=53) (actual time=38.024..57.509 rows=147 loops=1)
Run Condition: (row_number() OVER (?) <= 3)
-> Sort (cost=5987.50..6109.57 rows=48827 width=45) (actual time=38.015..47.781 rows=49000 loops=1)
Sort Key: posts.user_id, posts.post_id DESC
Sort Method: external merge Disk: 2640kB
-> Seq Scan on posts (cost=0.00..2185.00 rows=48827 width=45) (actual time=0.005..14.212 rows=49000 loops=1)
Filter: (user_id < 50)
Rows Removed by Filter: 51000
Planning Time: 0.202 ms
Execution Time: 57.853 ms
(10 rows)
一方で15ではSubquery Scanが走らなくなり、WindowAggの中で Run Condition に合致するレコードにのみ実行されていることが読み取れます。
実行時間は条件を揃えた測定ではないためあまり参考にはなりませんが、少なくとも遅いということはなさそうです[1]。
注意点としては、計算そのものは省略されるが行を抽出する処理がなくなるわけではない点です。
15の実行計画では、100000行のうち user_id < 50 で49000行に絞り、さらに row_number (?) <= 3 で147行に絞るという順で行を取得しています。100000行からいきなり147行に絞るわけではないので、思っているほど高速化には寄与しないケースが多いです。
今回は単純な計算でしたが、非常に複雑で計算量の多い処理をwindow関数内で実行してしまっている場合などにはバージョンアップの恩恵が大きいかもしれません。
フレーム化オプションの自動置換、打ち切り処理の他関数への展開 (PostgreSQL 16)
PostgreSQL 16 では、フレーム化オプションのRANGEモードの場合、置換できるものはROWモードとして処理されるようになりました。[2]
フレーム化条件が異なるwindow関数を実行する場合、条件ごとにWindowAggが実行されます。このときROWモードとRANGEモードで、実際には同じフレーム化条件であっても表記の違いによって別の条件とみなされていたものが、16以降は書き換えにより同じ条件とみなされるようになった、ということです。
select
row_number() over (partition by user_id order by post_id desc) as rn,
row_number() over (partition by user_id order by post_id desc rows between current row and current row) as rn2,
row_number() over (partition by user_id order by post_id desc range between current row and current row) as rn3
from posts where user_id < 50;
上記のクエリは、打ち切り処理の検証で割り振ったrow_numberと同じものをそれぞれrowモード、RANGEモードのフレーム化オプションを付けて実行するクエリです。
これらのフレーム化条件は表記が違うだけで同じもののため、16ではWindowAggがまとめられます。
実際にPostgreSQL 15, 16でexplain analyzeした結果を見てみます。
15
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=6021.80..8729.45 rows=49230 width=32) (actual time=40.509..145.241 rows=49000 loops=1)
-> WindowAgg (cost=6021.80..7867.93 rows=49230 width=24) (actual time=40.497..108.322 rows=49000 loops=1)
-> WindowAgg (cost=6021.80..7006.40 rows=49230 width=16) (actual time=40.494..79.691 rows=49000 loops=1)
-> Sort (cost=6021.80..6144.88 rows=49230 width=8) (actual time=40.484..50.823 rows=49000 loops=1)
Sort Key: user_id, post_id DESC
Sort Method: quicksort Memory: 3663kB
-> Seq Scan on posts (cost=0.00..2185.00 rows=49230 width=8) (actual time=0.004..15.887 rows=49000 loops=1)
Filter: (user_id < 50)
Rows Removed by Filter: 51000
Planning Time: 0.178 ms
Execution Time: 154.621 ms
(11 rows)
16
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=6020.10..7250.35 rows=49210 width=32) (actual time=44.240..79.923 rows=49000 loops=1)
-> Sort (cost=6020.10..6143.12 rows=49210 width=8) (actual time=44.231..54.184 rows=49000 loops=1)
Sort Key: user_id, post_id DESC
Sort Method: quicksort Memory: 3068kB
-> Seq Scan on posts (cost=0.00..2185.00 rows=49210 width=8) (actual time=0.005..17.448 rows=49000 loops=1)
Filter: (user_id < 50)
Rows Removed by Filter: 51000
Planning Time: 0.136 ms
Execution Time: 88.766 ms
(9 rows)
15ではWindowAggが3回行われていましたが、16では1回にまとめられていることがわかります。
個人的な感想ですが、これは同じになることがわかっているのであればクエリを書く人間が最初から同じフレーム化オプションで書くべきだと思いました。。
むすび
Window関数の打ち切りはRDBMSによって仕様がまちまちですが、PostgreSQLの仕様はその中でもかなり幅広く、柔軟にサポートされている部類です[3]。この例に限らず、近年のPostgreSQLは分析集計系の操作に役立つ改善が頻繁に入っているため、メジャーアップデートに着目していると思わぬ発見があるかもしれません。
この記事を書いた人
吉田 侑弥
2020年新卒入社
最近引っ越しをして屋根付き駐輪場を獲得しましたが、肝心の自転車が長年の雨ざらし環境でボロボロになっており追加投資の判断を迫られています。
-
掲載はしていませんが、複数回同じクエリを実行しても両者のExecution Timeは概ね同じような傾向を示しました。 ↩︎
-
他にも、15で導入された打ち切り処理がntile, cume_dist, percent_rankへも適用されるようになる改善が入りました。 ↩︎
-
参考: https://use-the-index-luke.com/ja/sql/partial-results/window-functions ↩︎
Discussion