PostgreSQL パラレルクエリ 性能検証 (2024年版)

# TL; DR

* 普段使ってるLinuxマシンで、PostgreSQL 16〜17を動かして、Star Schema Benchmarkを走行させた
* パラレルクエリ有効の場合、v16よりv17の方が多少速い
    * パラレルクエリ無効だと、性能がばらつく
    * JITは、昔と比べると、進化しているかもしれない
* q4.3で使用されるパラレルワーカー数が減る問題については、依然として残ったまま

# はじめに

## Advent Calendar

これは、 [PostgreSQL Advent Calendar 2024](https://qiita.com/advent-calendar/2024/postgresql) シリーズ1の24日目の記事です。  
クリスマス・イブですね！  
昨日は、 [@tom-sato](https://qiita.com/tom-sato) さんの[担当でした](https://qiita.com/tom-sato/items/94727f16eb87fc193f2c)。

## この記事は

[某団体](https://www.pgecons.org/)で活動していた頃に、[毎年](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2017/wg1_2017.html) [恒例の](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2018/wg1_2018.html) [検証に](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2019/wg1_2019.html) [しよう](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2020/wg1_2020.html) と目論んだが、  
[ちょうど3年前ぐらい](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2021/wg1_2021.html)に、ひょんな事から、大いなる神々の逆鱗に触れてしまい[^angry]、  
結果として恒例化に失敗どころか、[某団体](https://www.pgecons.org/)を去る羽目に陥り、  
無に帰したものを、 **[昨年に引き続き](https://qiita.com/taiki-k/items/6df5dcfb254cf9f20903)** 、個人でやってみた、というだけのものになります。  

[^angry]: 神々の怒りは、その[某団体](https://www.pgecons.org/)とは無関係ですので、関係者はご心配なさらずに。

# パラレルクエリ とは？

パラレルクエリとは、1つのクエリを複数のスレッドまたはプロセスで分担して並列処理することで、  
単一スレッド/プロセスでの処理よりも高速にクエリを実行する処理方式です。

## PostgreSQL におけるパラレルクエリ

[PostgreSQLにおけるパラレルクエリ](https://www.postgresql.jp/document/15/html/parallel-query.html)は、マルチプロセスによる並列処理となります。  
パラレルクエリが選択されるのは、 対象のテーブルサイズが大きく、実行計画が(PostgreSQL 15の場合)[このページに記載されている](https://www.postgresql.jp/document/15/html/parallel-plans.html)処理のみで構成される場合、かつ、  
パラレルクエリが使用できないパターンが出現していない場合です。  

基本的には、 パラレルクエリが発動する可能性があるのは、大きいサイズのテーブルに対するSELECT文の時で、使用するインデックスがないか、B-Treeの場合のいずれか、と思ってください。

この場合でも、パラレルクエリと通常(パラレルでない)の両方でコストを算出して検討し、  
コストが一番小さい、と判断された場合にパラレルクエリとして実行されます。

どの程度の並列度で実行されるかは、検索に関係するテーブルのサイズによります。  
この点は、[某団体](https://www.pgecons.org/)の成果物に[記載](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2020/wg1_2020.html#id33)がありますので参照してみてください。[^econs_doc]

# 性能検証

## Star Schema Benchmark とは？

今回の検証には、Star Schema Benchmarkを使用しています。

Star Schema Benchmarkは、Business Intelligenceを含むOLAP用途でのRDBMSの性能を測定するために考案されたベンチマーク手法です。  
POSデータやEコマース等における売上データ(巨大なデータ量のテーブル)を中心に置き、分析のための顧客情報や店舗情報(比較的小さなデータ量)を周囲において、中心テーブルと適宜Joinして結果を導きます。  
おそらく、これを図示すると星型に見えることから、この構造を持つものをStar Schemaと呼んでいるものと思います。
詳細については、[某団体](http://www.pgecons.org/)の成果物中にある[Star Schema Benchmarkの説明](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2020/wg1_2020.html#0125c1382bdfbe133d5862d3094cfeac)を読んでください。[^econs_doc]

[^econs_doc]: この文書のパラレルクエリに関する部分は概ね私が書いたものですが、査読ありなので、より正確だと思います。

## 測定条件

測定対象マシンは、以下になります。

* 自作マシン (aka ゲーミングPostgreSQLサーバ)
    * CPU: AMD Ryzen 7 2700X [^clock]
        * 8 Cores, 16 Threads
    * メモリ: 64GB (OCメモリ DDR4-3200相当)
    * OS: Debian **sid** (Kernel 6.11.10 (linux-image-6.11.10-amd64))

[^clock]: 定格クロック 3.7GHz、Boost最大クロック 4.3GHz です。

測定に使用したソフトウェアは、以下になります。

* Star Schema Benchmark[^ssb]  
  これは、[pg_ssb_run](https://github.com/taiki-k/pg_ssb_run)の[README.md](https://github.com/taiki-k/pg_ssb_run/blob/master/README.md)と[SSBの実行方法](https://github.com/taiki-k/pg_ssb_run/blob/master/HOWTO_RUN_SSB.md)に記載されているものに準拠。
    * Scale Factor: 70G
    * データ生成プログラム: [こちら](https://github.com/LucidDB/thirdparty/blob/master/ssb.tar.bz2)
    * ベンチマーク用クエリ: [この論文](https://www.cs.umb.edu/~poneil/StarSchemaB.PDF)から抜粋
    * ベンチマーク走行プログラム:[pg_ssb_run](https://github.com/taiki-k/pg_ssb_run)
        * 検証したPostgreSQLのバージョン毎に5回走行して、平均値を記録とする。
* PostgreSQL  
  すべて、ソースコードからビルドしたもの
    * コンパイラのバージョン
      * `gcc (Debian 14.2.0-11) 14.2.0`
      * `clang version 19.1.6 (1)` (LLVM 19.1.6)
    * バージョン  
      カッコ内は、各STABLEブランチのコミットIDを示す。  
      すべて、zipやtar.gzで配布されている地点以降のコミットID。
        * 16.6相当 (1f47e7b5)
        * 17.2相当 (6304632e)
    * パラメータ  
      デフォルト値から変更しているもののみ記載  
      鍵となるパラメータは太字で表記
      * shared_buffers = 48GB
      * effective_cache_size = 48GB
      * work_mem = 8GB
      * maintenance_work_mem = 2GB
      * wal_buffers = 16MB
      * max_wal_size = 16GB
      * min_wal_size = 4GB
      * checkpoint_completion_target = 0.9
      * **max_worker_processes = 12**
      * max_parallel_maintenance_workers = 2
      * **max_parallel_workers_per_gather = 7** [^calc_wrk]
      * **max_parallel_workers = 7** [^calc_wrk]
      * jit = off (or on)

テーブルにベンチマーク用のデータを載せると、以下のとおりとなります。  
中心となるテーブルは、 lineorder テーブルで、 サイズは41GBです。

```
=# \d+
                                   リレーション一覧
 スキーマ |   名前    |  タイプ  | 所有者 | 永続性 | アクセスメソッド | サイズ | 説明 
----------+-----------+----------+--------+--------+------------------+--------+------
 public   | customer  | テーブル | taiki  | 永続   | heap             | 250 MB | 
 public   | date      | テーブル | taiki  | 永続   | heap             | 336 kB | 
 public   | lineorder | テーブル | taiki  | 永続   | heap             | 41 GB  | 
 public   | part      | テーブル | taiki  | 永続   | heap             | 157 MB | 
 public   | supplier  | テーブル | taiki  | 永続   | heap             | 76 MB  | 
(5 行)
```

測定に際しては、事前に pg_prewarm で、上記の全テーブルの全データをPostgreSQLの共有バッファに載せてから測定を行っています。

[^ssb]: 基本的に[某団体](http://www.pgecons.org/)の成果物での条件と同じ。
[^calc_wrk]: 物理コア数 - 1。8コアなので、7に設定。

## 測定結果

### 通常実行(パラレルクエリなし)での所要時間

まず、各バージョン間の性能傾向を掴むため、  
パラレルクエリなしの状態での所要時間の測定結果を以下に示します。  
単位は 秒 です。(平均値のみの掲載です)

| query_no | PostgreSQL 16.6 | PostgreSQL 17.2 |
| -------- | --------------- | --------------- |
| q1.1     | 50.56971        | 47.2563134      |
| q1.2     | 44.787725       | 40.6629014      |
| q1.3     | 44.610307       | 40.9170858      |
| q2.1     | 81.0005978      | 93.8137494      |
| q2.2     | 58.7538506      | 63.8835716      |
| q2.3     | 52.7438524      | 52.3968052      |
| q3.1     | 131.3919634     | 101.4759128     |
| q3.2     | 64.6795248      | 91.1327222      |
| q3.3     | 58.1525614      | 62.213891       |
| q3.4     | 57.3286502      | 63.9588432      |
| q4.1     | 130.7543524     | 130.7524122     |
| q4.2     | 126.701344      | 128.097293      |
| q4.3     | 80.2257102      | 88.6793928      |

上記の表をグラフ化したものと、平均値と測定最大/最小値の関係(値域)をグラフ化したものを以下に示します。

![parallel_ssb_norm.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/43391f0a-f835-2279-aad7-b2cb51d1a8a3.png)

![parallel_ssb_norm_range.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/a5ed9ee4-531d-a400-62ec-030f7627cad7.png)

こうして見てみると、q1.xが少し速くなった一方で、q2.x、q3.x、q4.xは、  
だいたい遅くなってしまっています。  
ただ、q3.x、q4.xは、17.2における測定値の値域が16.6のそれと比較して  
大きめであることが多そうです。

### パラレルクエリでの所要時間

この記事のメインである、パラレルクエリ実行時の所要時間の測定結果を以下に示します。  
単位は 秒 です。(平均値のみの掲載です)

| query_no | PostgreSQL 16.6 | PostgreSQL 17.2 |
| -------- | --------------- | --------------- |
| q1.1     | 9.5745272       | 8.7301526       |
| q1.2     | 8.9868042       | 8.159054        |
| q1.3     | 9.0334          | 8.1480444       |
| q2.1     | 14.0367158      | 14.078033       |
| q2.2     | 10.8607924      | 10.7213842      |
| q2.3     | 10.2163592      | 10.1150794      |
| q3.1     | 17.5912014      | 17.0293366      |
| q3.2     | 11.7349086      | 11.4104674      |
| q3.3     | 9.980247        | 9.8747862       |
| q3.4     | 9.9434514       | 9.764173        |
| q4.1     | 17.5124954      | 17.3236074      |
| q4.2     | 17.1754764      | 17.067793       |
| q4.3     | 18.8749474      | 18.916888       |

上記の表をグラフ化したものと、平均値と測定最大/最小値の関係(値域)をグラフ化したものを以下に示します。

![parallel_ssb_para.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/e638b4f2-d119-a92d-7a45-d3ae706a18b9.png)

![parallel_ssb_para_range.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/5b820f19-3766-108c-ebb4-70ec2ba6ce90.png)

パラレルクエリなしの場合と比較して、全体的に17.2の方が少し速い、という傾向が見て取れます。  
また、測定値の値域がパラレルクエリなしの場合と比較しても小さそうです。

### JITもONにしてみる

パラレルクエリに追加して、JITも有効にしてみました。
パラレルクエリ+JIT実行時の所要時間の測定結果を以下に示します。  
単位は 秒 です。(平均値のみの掲載です)

| query_no | PostgreSQL 16.6 | PostgreSQL 17.2 |
| -------- | --------------- | --------------- |
| q1.1     | 6.8412658       | 6.6653028       |
| q1.2     | 6.1870382       | 6.023434        |
| q1.3     | 6.1916298       | 6.0629124       |
| q2.1     | 12.9881872      | 13.1024486      |
| q2.2     | 10.7622862      | 10.9954388      |
| q2.3     | 10.01731        | 10.1614568      |
| q3.1     | 16.188616       | 16.2469172      |
| q3.2     | 11.2618384      | 11.2461124      |
| q3.3     | 9.7331572       | 9.7440572       |
| q3.4     | 9.7106514       | 9.706675        |
| q4.1     | 16.584265       | 16.5854232      |
| q4.2     | 16.3078134      | 16.2286842      |
| q4.3     | 17.8932338      | 17.804396       |

上記の表をグラフ化したものと、平均値と測定最大/最小値の関係(値域)をグラフ化したものを以下に示します。

![parallel_ssb_para_jit.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/dd9e76d7-584e-0576-4338-705f4617efb8.png)

![parallel_ssb_para_jit_range.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/ec9228fd-fb77-61ae-c7dc-4bb7da44455a.png)

パラレルクエリのみ有効の場合と比較して、全体的に速くなりました。(特にq1.x、q3.1、q4.3)  
ただ、正直言って、効果は限定的のような気がします。

### q4.3のパラレルワーカー数

ずっと気になっている、 [q4.3でパラレルワーカー数が減る問題](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2018/wg1_2018.html#c46d758e799709d706379a9c54af1f84)が直っているかを確認しましょう。

以下は、実際に実行された実行計画から抜き出した、パラレルワーカー使用予定数(planned)と実際の使用数(launched)を示す表です。

| version          | workers_planned | workers_launched |
| ---------------- | --------------- | ---------------- |
| PostgreSQL 16.6  | 4               | 4                |
| PostgreSQL 17.2  | 4               | 4                |

今回の設定の場合、直っていれば、少なくともplannedは **7** になるはずですが、  
これは、やっぱり直ってないですね。

以下は、PostgreSQL 17.2における、q4.3の実行計画を [pev2](https://explain.dalibo.com/) を用いて[図示したもの](https://explain.dalibo.com/plan/b7bdg6cc4hdcf41g)です。  

![ssb_q43_pg17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/214816/4b42cfe3-5ea1-eb5b-0e9b-e2eb13b2567c.png)

ルートノード(一番上)から辿っていって、一番左に来てるテーブルが、customerテーブルなので、  
全体のパラレルワーカー数が、これに引っ張られています。

## 所感

### パラレルクエリを動かすと性能は安定する

(設定変更が面倒なので)CPUのクロックはシステムの判断によって、動的に変化させています。  
パラレルクエリなしの場合、1コアしか動かないため、システム全体のCPU使用率は 6%程度(≒100/16)でした。  
このためなのか、CPUのクロックが2.2GHz近辺であることが多く、時々3.7GHz〜4.2GHzになるぐらいでした。[^clock]  
これに対して、パラレルクエリあり(JIT有効を含む)の場合、q4.3を除いて、おおむね4.2GHz〜4.3GHzで推移していました。[^cold]

[^cold]: 熱容量に余裕があったのでしょう。部屋が寒かったので。

当然の帰結だと思いますが、クロックの変動が少なければ、性能が安定しますので、  
結果としてパラレルクエリありの方が、測定値の値域が狭い傾向にありました。

なお、q4.3は以下の問題が解決していないことから、5コアの動作にとどまり、  
システム全体のCPU使用率は 30%程度(≒100/16×5)で推移し、CPUのクロックは、  
割と2.2GHz近辺であることが多かったです。  
結果、やはり、測定値がばらついていました。

正直、『各バージョン間の性能傾向を掴むため』にパラレルクエリなしの性能を測定しましたが、  
測定値のばらつきが大きかったので、今回の平均値での比較では、正しい性能傾向が出てきていないかもしれません。

### パラレルクエリ有効の場合、v16よりもv17の方が速い

パラレルクエリありの場合、v17の方が多少速かったですね。

新しいバージョンの方が速い、というのは素晴らしいことだと思います。(たいがい遅くなるので)  
ただ、昨年の記事でv15からv16でq4.3の応答速度が元に戻ってしまったところは、v17でもそのままのようです。

これは、何故なんでしょうね…

### パラレルクエリ+JITは、得意・不得意があるかもしれない

JITを有効にした場合では、q1.xは全クエリで高速化した一方で、  
q1.x以外はq3.1とq4.3が高速化した以外は、大した効果が出ませんでした。  
ただ、[某団体](http://www.pgecons.org/)での以前の検証では[あえなく撃沈していた](https://pgecons-sec-tech.github.io/tech-report/html_wg1_2019/wg1_2019.html#d891fe32729c7f8f5cf887706df06ecc)ことを考えると、  
JIT(というより、LLVM、Clangか？)の改良が進んだものと思います。

q1.xは、単にテーブルのスキャンと結合だけのクエリですが、  
q2.x、q3.x、q4.xはテーブルのスキャン、結合、集約(sum())、ソートが含まれます。  
また、q2.x、q3.x、q4.xの順でスキャンするテーブル数が多く、  
また結合条件の数も多くなります。  

これらから考えると、クエリが複雑なものは、実行コードも複雑になるため、  
JITコンパイルにかかる時間が大きくなったり、JITコードの実行時間も長くなったりするのかな、  
と思います。(たぶん、コンパイル時間の方が、影響が大きいでしょう) [^jit]

パラレルクエリが使えるときは、パラレルクエリに身を任せてしまったほうが、  
良いのかもしれません。

[^jit]: 本来、複雑なクエリにおける処理の最適化・高速化を目的としていたはずなので、これでは本末転倒な気が…

### q4.3は魔境かも？

q4.3において、パラレルワーカー数が小さくなる、という問題はPostgreSQL 9.6の頃から観測されています。  
直接の原因となるコードも特定してはいますが、データセットによっては再現しないこともあり、  
プランナーの精度の問題と捉えることもできます。  

なので、この問題をコミュニティに提起しても、単純に「このコードを修正すればOK！」という方向に議論が向くかどうかは、個人的には、ちょっと懐疑的です。  
この問題をコミュニティに提起するときは、澤田さんとか藤井さんとかを味方につけてから提起したいと思います。


# おわりに

今回は、PostgreSQLにおいて、Star Schema Benchmarkを用いて、パラレルクエリの性能評価をやってみました。  
ちょっと、突っ込んだ調査をした方が良い点もありましたので、時間があったら調査してみたいと思います。

PostgreSQLを動かせる性能が良さげなマシンを持っている方は、このベンチマークを使って、  
PostgreSQLの性能測定や調査に活かしたり、単純に自分のマシンの自慢をしたりしてみてはいかがでしょうか？

明日は、ついにクリスマス当日ですね！  
担当は、[@Ha-kun](https://qiita.com/Ha-kun)さんになります。