3.1.1. 各WGとWG1の活動テーマ¶

PGECons 技術部会ではPostgreSQLの普及を促進する技術的な課題の解決をテーマとし、「大規模基幹業務に向けたPostgreSQLの適用領域の明確化」を大きな目標に活動しております(2012年7月6日開催のPGEConsセミナーより)。

この目標に向けて技術部会では課題を以下の課題領域に分類し、三つのワーキング・グループ(WG)で分担することとしました。

• WG1（新技術検証ワーキンググループ）
  • 新しい技術の実検証から有用性や課題を検討
  • PostgreSQLの最新バージョンの性能傾向の調査・検討
• WG2（移行ワーキンググループ）
  • 利用者観点で整備した「移行フレームワーク」に基づき、成果物のユーザビリティ向上、 内容の最新化などを実施
• WG3（課題検討ワーキンググループ）
  • データベース管理者やアプリケーション開発者が抱える、現場の課題に向けた課題に対するテーマを設定

3.3.1. 2018年度の活動テーマ¶

2018年度は、2018年10月18日にリリースされた PostgreSQL 11を対象として以下のようなテーマで検証を実施しました。

• 定点観測（スケールアップ）
• Windows版PostgreSQL性能
• JITコンパイル
• パラレルクエリ

以下、各検証テーマごとの概要を紹介します。

3.3.2. 定点観測（スケールアップ）¶

例年通り、PostgreSQL 新旧バージョンにおけるスケール性能を比較する検証を行いました。 参照系クエリの性能は 10 と 11 で差は見られませんでした。しかし、同検証で副次的に集約計算性能の向上を観察することができました。 更新系クエリの性能は 11 で性能向上が見られました。これは clog に関する処理の変更が主な原因でした。 また、昨年度発覚した 10 の更新系クエリの性能低下について再検証したところ、推測していた通りセマフォの変更が原因であることが確認できました。

3.3.3. Windows版PostgreSQL性能¶

昨年に引き続き、Linux上で実施していた定点観測と同様の測定をWindows版PostgreSQLでも実施し、Linux版PostgreSQLと、Windows版PostgreSQLの性能の傾向を評価しました。 同一スペックのサーバ上でLinux/WindowsのOSの差異による測定を実施した結果、参照系はCPU数を超えない範囲の同時接続数、つまり過負荷状態でなければ、Windows版PostgreSQLは性能面ではLinux版と遜色はないと考えられます。 更新系は、同時接続数が32を超える場合に、Linux版と比較してWindows版のtpsが大きく劣るという結果となりました。

3.3.4. JITコンパイル¶

PostgreSQL11で追加されたJITコンパイルについて、有効なクエリ種の調査と実行時間の削減効果の検証を行いました。 四則演算と集約関数を含むクエリに有効であることがわかりました。 また、カラムのデータ型の違いによっても削減効果の違いがあることがわかりました。 OLAP系の大規模なクエリを実行した場合には、削減効果は見られたもののクエリの内容によって効果に差があることがわかりました。

3.3.5. パラレルクエリ¶

PostgreSQL 9.6にてメニーコアを有効活用するパラレルクエリが実装され、バージョン10以降で継続的に強化されています。 今回は、BIのようなOLAPにおいて使用される形式のクエリを模したベンチマークテストであるStar Schema Benchmarkで定義されているクエリを題材とし、OLAPにおいてバージョン9.5〜11で、パラレルクエリが実装されたことによりどのように性能が変化するのかを検証しました。 また、バージョン11にて実装された、B-Treeインデックスのパラレル作成について、パラメータによって、どのように性能が変化するのかを検証しました。 検証の結果、テーブルサイズによって決定される並列度までは性能が向上しましたが、実行計画の変化に起因して性能が頭打ちになる場合があることがわかりました。 性能が頭打ちになる現象については、並列度を決定するロジックが不完全であることが原因とわかりました。

3.4.1. 2017年度までの活動テーマの紹介¶

2012年度は、企業システムで使われる機器構成で、PostgreSQLのスケールアップ、スケールアウトによる性能特性、性能限界を検証しました。 企業システムへのPostgreSQL採用や、システム構成を検討するための、一つの指針として「2012年度WG1活動報告書」として情報を公開しています。

2013年度は、2012年度に引き続き2013年9月9日にリリースされたPostgreSQL 9.3を対象としたスケールアップの定点観測を実施、PostgreSQL 9.3新機能による性能影響も合わせて評価することとしました。 また、更新スケールアウト構成が可能なPostgres-XCの測定パターンを変えた再測定により、最適な利用指針を探る評価を実施することとしました。 さらに、2013年度の新たな取り組みとしてデータベースの性能向上に着目、データベースのI/O負荷分散機能であるパーティショニングや、ハードウェアを活用した性能向上の検証を実施しました。

2014年度は、2013年度に引き続き2014年12月7日にリリースされたPostgreSQL 9.4を対象としたスケールアップの定点観測を実施、そして9.4新機能のWAL改善を評価するために更新系処理を新たに評価することとしました。 また、新たな取り組みとして物理環境以外の環境におけるデータベースの性能評価に着目、KVMを使った仮想化環境と、LinuxコンテナのDocker環境の検証を実施しました。

2015年度は、2016年1月7日にリリースされた PostgreSQL 9.5を対象とした、スケールアップの定点観測を実施しました。 これは昨年度に引き続き、参照系・更新系の双方で性能を調査するものです。 PostgreSQL 9.5での2つの新機能、BRINインデックスとParallel Vacuumについて、その利用ノウハウが得られるような検証を行いました。最後に、基盤となるLinux OSの主要なディストリビューションの一つであるRed Hat Enterprise Linux 6と7とでPostgreSQLの性能を比較しました。

2016年度は、2016年9月にリリースされた PostgreSQL 9.6を対象とした、スケールアップの定点観測を実施しました。 これは例年通り、参照系・更新系の双方で性能を調査するものです。 PostgreSQL 9.6の新機能のパラレルクエリや改善が行われたVACUUMについて効果を検証しました。 また、Pgpool-IIのスケールアウト性能、JSON/JSONB データ型に関する性能評価を行いました。

2017年度は、2017年10月にリリースされたPostgreSQL 10を対象とした、スケールアップの定点観測を実施しました。 これは例年通り、参照系・更新系の双方で性能を調査するものです。 PostgreSQL 10で強化されたパラレルクエリの性能検証、新機能であるロジカルレプリケーションを利用した負荷分散の効果検証、同期・非同期レプリケーション構成や混在環境における性能検証、FDW機能利用時の性能検証を行いました。

4.3.1. ハードウェア構成¶

図 4.1 検証ハードウェア構成

本検証では上図構成のうち「DBサーバ#2」と「負荷掛け用」を用いました。

4.3.2. ソフトウェア構成¶

検証環境のソフトウェア構成を示します。

4.3.3. カーネル設定¶

• OS デフォルト
• ハイパースレッディングは利用しない

4.3.4. PostgreSQL 設定¶

PostgreSQL設定ファイル postgresql.conf での設定は以下の通りです。これらは参照系検証と更新系検証で共通です。

listen_addresses = '*' ... 負荷掛け用マシンからの接続用
max_connections = 500 ... 多めに設定
shared_buffers = 40GB
work_mem = 1GB
maitenance_work_mem = 20GB
checkpoint_timeout = 60min
max_wal_size = 160GB

4.3.5. 環境¶

以下の手順で、データベースクラスタを作成しました。

initdbでデータディレクトリを作成し、上記に示した設定をpostgresql.confに記述します。

$ initdb --no-locale --encoding=utf-8
$ vi $PGDATA/postgresql.conf

PostgreSQLを起動してベンチマーク用のデータベースを作成します。

$ pg_ctl start
$ createdb [dbname]

ここからは、参照系性能と更新系性能についてそれぞれ検証していきます。

5.2.1. ハードウェア構成¶

図 5.1 検証ハードウェア構成

本検証では上図構成のうち「DBサーバ#1」と「負荷掛け用」を用いました。

5.2.2. ソフトウェア構成¶

検証環境のソフトウェア構成を示します。

5.3.1. データベースクラスタ構成¶

Windows版PostgreSQLをインストーラでインストールすると、インストール中にデフォルトのデータベースクラスタを作成します。 今回の性能検証では、データベースクラスタを外部ストレージには配置せず、またデータベース格納領域とWAL格納領域の分離は行わない構成で実施しています。

5.3.2. 検証用ベンチマーク¶

検証用のベンチマークとして、PostgreSQL標準のベンチマークツールpgbenchを使用しました。 （pgbenchの詳細については、「定点観測(スケールアップ検証)」を参考にしてください。

5.3.3. 仮測定¶

本測定の前に、インストールしたPostgreSQLが正常に動作することを確認するため、仮測定を行いました。 ここで、参照系の性能結果に異常が見られたため本検証ではEDB installer版ではなく、BigSQL installer版を用いることとしました。

5.3.3.1. 参照系仮測定¶

走行時間は60秒を指定しました。 同時実行数として1, 2, 4, 8, 16, 32, 48, 64, 96を与えて実行しました。 参照系測定のために、以下のカスタムクエリをpgbenchに与えて実行しました。

\set naccounts 100000 * :scale
\set row_count 10000
\set aid_max :naccounts - :row_count
\set aid random(1, :aid_max)

SELECT count(abalance) FROM pgbench_accounts WHERE aid BETWEEN :aid and :aid + :row_count;

仮測定時の参照系検証結果(tps)を以下に示します。

上記の通り、EDB installer版PostgreSQL11.1では思ったような負荷がかかっておらず、バージョン間の性能観測を行うことは困難と判断しました。 代替としてBigSQL installer版のPostgreSQL11.1ではEDB installer版PostgreSQL11.1と同等（もしくはそれ以上）の性能が出ることが確認できたため、以降の検証のPostgreSQLバージョンとしては、BigSQL installer版PostgreSQL 11を利用することとしました。

5.3.3.2. 更新系仮測定¶

走行時間は60秒を指定しました。 同時実行数として1, 2, 4, 8, 16, 32, 48, 64, 96を与えて実行しました。 更新系測定のために、以下のカスタムクエリをpgbenchに与えて実行しました。

\set naccounts 100000 * :scale
\set aid_val random(1, :naccounts)
UPDATE pgbench_accounts SET filler=repeat(md5(current_timestamp::text),2) WHERE aid = :aid_val;

仮測定時の更新系検証結果(tps)を以下に示します。

上記の仮測定の結果を確認し、BigSQL installer版を用いて、参照系・更新系ともにLinux版PostgreSQLとの比較を行う方針としました。

5.3.4. 本測定¶

本測定では測定時間を300秒として、仮測定と同様の測定を実行しました。

5.3.5. PostgreSQL 設定¶

PostgreSQL設定ファイル postgresql.conf での設定は以下の通りです。これらは参照系検証と更新系検証で共通です。

listen_addresses = '*' ... 負荷掛け用マシンからの接続用
max_connections = 500 ... 多めに設定
shared_buffers = 16384 ... Windows版ではshared_buffersに大きな値を設定しても意味がないため
work_mem = 1GB
maitenance_work_mem = ... Windows版PostgreSQLでは上限値がLinux版よりも小さいため、20GBではなく6GBを設定した。
checkpoint_timeout = 60min
max_wal_size = 160GB

5.3.6. OSリソース情報の収集¶

Linuxで使われているvmstat/sarといったOSのリソース情報の取得コマンドはWindowsにはありません。 代わりにtypeperfコマンドでOSのリソース情報を取得します。 リソースがどのように利用されているかを記録するためtypeperfコマンドを参照系・更新系それぞれで以下の通り実行しました。 今回の検証では、typeperfコマンドを以下のように実行することで、CPU使用率、メモリ使用量、ディスクへの秒あたりの書き込み量を取得するようにしました。

typeperf -sc 900 -si 10  "\processor information(_Total)\% Processor Time" "\Memory\Committed Bytes" "\LogicalDisk(C:)\Avg. Disk sec/Read" "\LogicalDisk(C:)\Avg. Disk sec/Write" > select-res.txt
typeperf -sc 2300 -si 10  "\processor information(_Total)\% Processor Time" "\Memory\Committed Bytes" "\LogicalDisk(C:)\Avg. Disk sec/Read" "\LogicalDisk(C:)\Avg. Disk sec/Write" > update-res.txt

5.4.1. 参照系¶

参照系検証結果(tps)を以下に示します。

参照系検証時の平均CPU使用率を以下に示します。

参照系については同時接続数32までは、同時接続数を上げることでほぼ線形にtpsが増加していきます。 同時接続数32～64の区間では、tpsは変わらず、レイテンシが増加するという傾向になりました。 この区間では、Linux版PostgreSQL 11よりも高いtpsとなっています。 この区間で、Windows版PostgreSQLのtpsがLinux版PostgreSQLよりも高いtpsになっている原因については、現時点では不明であり、今後の課題と考えています。 Linux版PostgreSQLとWindows版PostgreSQLのCPUの使用状況を比較してみると、Linux版PostgreSQLでもCPUは十分に使われており、 CPUの使用状況が原因ではないと考えられます。 今後、どういった情報を収集してLinux版PostgreSQLとの挙動を比較すれば良いかも含めて検討が必要です。

同時実行数が64を超えると急激にtpsが低下し、またCPU使用率も大きく減少しています。 このとき、Linux版PostgreSQL 11よりもtpsは悪化し、またCPU使用率についてもLinux版PostgreSQLと比較すると大きく低下するという結果になっています。

過負荷状態では、CPUネックとは別の原因がネックになっていると考えられます。プロセス間の排他待ちの影響がLinux版と比較すると大きくなっていると推測していますが、今年度の測定では、それを示す情報の取得はできませんでした。 次年度の測定では、同時接続数が過大になったときのtpsおよびCPU使用率の低下原因の追求が必要と考えます。

5.4.2. 更新系¶

更新系検証結果(tps)を以下に示します。

更新系検証時の平均CPU使用率を以下に示します。

更新系に関しては、同時接続数32でtpsのピーク値となり、以降はほぼtpsは横ばいとなりました。 Linux版と比較すると、同時接続数48以降は大きくtpsが小さくなるという結果になっています。 更新系の平均CPU使用率を見ると、参照系とは異なり、同時接続数64をピークとして最大でも30%程度の使用率となっています。 また、Linux版更新系の平均使用率と比較すると、全般にCPU使用率が低い傾向にあります。 このことから、Windows環境では更新時のI/O負荷がLinuxと比較して高いことが推測されます。

6.2.1. JITコンパイル¶

JITコンパイルとは、ソフトウェアの実行時にコードのコンパイルを行い実行速度の向上を図る手法です。 反復される処理を、インタプリタ形式からコンパイルしてバイナリを実行する形式に変更することで高速化を実現しています。

6.2.2. PostgreSQLでのJITコンパイル¶

PostgreSQLでJITコンパイルを利用可能にするためには、以下のパッケージが必要になります。

上記パッケージがインストールされた状態でPostgreSQLのビルド時に"--with-llvm"を指定することにより、LLVMを使ったJITコンパイルが組み込まれます。 具体的な方法は、SRA OSS社が公開している PostgreSQL11検証報告書 をご参照ください。

高速化の対象として、WHERE句やターゲットリスト、集約関数などといった式の評価やディスク上のタプルをインメモリ形式に変換するタプル変形、そして関数のインライン化があります。 インライン化はクエリ中の関数呼び出しや演算子をバイナリに変換し、関数呼び出しのオーバーヘッドを削減します。 JITコンパイルが有効に働く状況は、CPU使用率の高い状態が長時間継続するようなクエリを実行している状態とされています。 つまり、式表現が複雑であり、参照する行数が多い解析系のクエリで有効であるといえます。 逆に、短時間で完了するクエリではJITのオーバーヘッドの方が大きくなってしまいます。（下記の実行計画例を参照）

JITコンパイルを有効にするには、postgresql.confファイルのjitパラメータ（後述）をonに設定する必要があります。 さらに、JITコンパイルが実行されるか否かは、クエリのコストに依存します。 コストはjit_above_costパラメータ（後述）で与えられる閾値と比較され、コストの方が大きければJITコンパイルが実行されます。 JITコンパイルが実行される場合、jit_inline_above_costパラメータ（後述）の閾値およびjit_optimize_above_costパラメータ（後述）の閾値と比較され、コストの方が大きければ関数のインライン化または最適化がそれぞれ追加で実行されます。

JITコンパイルの実行状況は、実行計画から確認できます。

                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate  (cost=16.27..16.29 rows=1 width=8) (actual time=6.049..6.049 rows=1 loops=1)
  ->  Seq Scan on pg_class  (cost=0.00..15.42 rows=342 width=4) (actual time=0.019..0.052 rows=356 loops=1)
Planning Time: 0.133 ms
JIT:
  Functions: 3
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 1.259 ms, Inlining 0.000 ms, Optimization 0.797 ms, Emission 5.048 ms, Total 7.104 ms
Execution Time: 7.416 ms

「JIT:」以下に実行したJITコンパイルの詳細が表示されます。

6.2.3. JITコンパイルに関するパラメータ¶

JITコンパイル時に関連する主なパラメータには以下があります。

6.3.1. 検証ハードウェア環境¶

検証環境のハードウェア構成の概略を示します。

6.3.2. 検証ソフトウェア環境¶

検証環境のソフトウェア環境を示します。

6.4.1. 検証項目¶

JITコンパイル検証では以下の3点を検証します。

6.4.2. データモデル¶

本検証で使用するデータモデルは、Star Schema Benchmark(SSB)に準拠します。 データの生成プログラムは、 こちらのGitHubリポジトリ から入手したものを使用しました。 また、テーブル定義は こちら から入手したものを使用しました。

# zipファイルを解凍する

$ bzip2 -d ssb.tar.bz2
$ tar xf ssb.tar
$
$ make

# データ生成（-sはスケールファクタ（後述）を指定するオプション、-Tはテーブルの種類を指定するオプション）

$ ./dbgen -s 1 -T c #customerテーブル
$ ./dbgen -s 1 -T d #dateテーブル
$ ./dbgen -s 1 -T l #lineorderテーブル
$ ./dbgen -s 1 -T p #partテーブル
$ ./dbgen -s 1 -T s #supplierテーブル

# テーブルを定義する(コマンドは省略）

# テーブルにデータを挿入する（以下のコマンドを各テーブルごとに実行する）

$ sed -e 's/|$//' customer.tbl | psql -d jit-test -c "copy customer from STDIN ( delimiter '|');"

SSBはその名前の通り、スター・スキーマ型のデータモデルを採用しています。 スタースキーマ型のデータモデルは、1つのファクトテーブルと複数のディメンジョンテーブルで構成されています。 ファクトテーブルとは分析対象となる実績値が格納される明細データであり、データ件数は億単位（本検証では約6億件）になります。 ディメンジョンテーブルとはビジネス上の分析の軸になるマスターデータであり、データ件数は少ないのが特徴です。 SSBではlineorderテーブルがファクトテーブルであり、商品の購入履歴が時系列に蓄積されます。 そしてSSBのディメンジョンテーブルにはcustomerテーブル、supplierテーブル、partテーブル、dateテーブルがあり、顧客やサプライヤなどの識別キーや名称などが格納されます。

図 6.1 Star Schema Benchmarkのテーブル構成

なお、Scale Factor=100GBとしています。 Scale Factor(SF)とは、ベンチマーク全体のテーブル規模を示しています。 SSBで使用されるテーブルの行数はSFによって以下のように計算できます。 dateテーブルは7年分の日付情報を持つため、常に一定の大きさになっています。

本検証における各テーブルのサイズは以下の通りです。

      relname          |    rows    |    bytes
-----------------------+------------+--------------
customer               | 3000000    | 374030336
date                   | 2315       | 311296
lineorder              | 599985000  | 63557517312
part                   | 1399570    | 164265984
supplier               | 999604     | 114376704

6.4.3. DB設定¶

性能測定にあたり、デフォルトから変更したパラメータは以下のとおりです。 本検証ではJITコンパイルの性能を確認するため、パラレルクエリとして実行されないようにmax_parallel_workers_per_gatherパラメータの値を0としています。

6.4.4. 測定手順¶

測定に際しては、SQLの実行前にpg_prewarmを利用して、テーブルのデータをディスクから一度読込み、PostgreSQLのバッファ上、もしくはOSのディスクバッファ上に常駐させた状態にします。

SELECT relname, pg_prewarm(oid)
FROM pg_class
WHERE relnamespace in (
  SELECT oid
  FROM pg_namespace
  WHERE nspname='public'
) AND (
  relkind='r'
);

select lo_extendedprice * lo_discount
from lineorder;

select lo_revenue - lo_supplycost
from lineorder;

select sum(lo_extendedprice)
from lineorder;

select avg(lo_extendedprice)
from lineorder;

select count(lo_extendedprice)
from lineorder;

select count(*)
from lineorder;

select max(lo_extendedprice)
from lineorder;

select stddev(lo_extendedprice)
from lineorder;

select var_samp(lo_extendedprice)
from lineorder;

select sum(lo_extendedprice)
from lineorder_n;

select sum(lo_extendedprice)
from lineorder_r;

select sum(lo_extendedprice)
from lineorder_d;

select  sum(lo_extendedprice * lo_discount) as revenue
from lineorder, date
where lo_orderdate = d_date_key and
      d_year = 1993 and
      lo_discount between 1 and 3 and
      lo_quantity < 25;

select  sum(lo_extendedprice * lo_discount) as revenue
from lineorder, date
where lo_orderdate = d_date_key and
      d_yearmonthnum = 199401 and
      lo_discount between 4 and 6 and
      lo_quantity between 26 and 35;

select  sum(lo_extendedprice * lo_discount) as revenue
from lineorder, date
where lo_orderdate = d_date_key and
      d_weeknuminyear = 6 and
      d_year = 1994 and
      lo_discount between 5 and 7 and
      lo_quantity between 26 and 35;

select sum(lo_revenue), d_year, p_brand1
from lineorder, date, part, supplier
where lo_orderdate = d_datekey and
      lo_partkey = p_partkey and
      lo_suppkey = s_suppkey and
      p_category = 'MFGR#12' and
      s_region = 'AMERICA'
group by d_year, p_brand1
order by d_year, p_brand1;

select sum(lo_revenue), d_year, p_brand1
from lineorder, date, part, supplier
where lo_orderdate = d_datekey and
      lo_partkey = p_partkey and
      lo_suppkey = s_suppkey and
      p_brand1 between 'MFGR#2221' and 'MFGR#2228' and
      s_region = 'ASIA'
group by d_year, p_brand1
order by d_year, p_brand1;

select sum(lo_revenue), d_year, p_brand1
from lineorder, date, part, supplier
where lo_orderdate = d_datekey and
      lo_partkey = p_partkey and
      lo_suppkey = s_suppkey and
      p_brand1 = 'MFGR#2221' and
      s_region = 'EUROPE'
group by d_year, p_brand1
order by d_year, p_brand1;

select c_nation, s_nation, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_orderdate = d_datekey and
      c_region = 'ASIA' and
      s_region = 'ASIA' and
      d_year between 1992 and 1997
group by c_nation, s_nation, d_year
order by d_year asc, revenue desc;

select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_orderdate = d_datekey and
      c_nation = 'UNITED STATES' and
      s_nation = 'UNITED STATES' and
      d_year between 1992 and 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;

select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_orderdate = d_datekey and
      ( c_city = 'UNITED KI1' or
        c_city = 'UNITED KI5') and
      ( s_city = 'UNITED KI1' or
        s_city = 'UNITED KI5') and
      d_year between 1992 and 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;

select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_orderdate = d_datekey and
      ( c_city = 'UNITED KI1' or
        c_city = 'UNITED KI5') and
      ( s_city = 'UNITED KI1' or
        s_city = 'UNITED KI5') and
      d_yearmonth = 'Dec1997'
group by c_city, s_city, d_year
order by d_year asc, revenue desc;

select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit
from date, customer, supplier, part, lineorder
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_partkey = p_partkey and
      lo_orderdate = d_datekey and
      c_region = 'AMERICA' and
      s_region = 'AMERICA' and
      (p_mfgr = 'MFGR#1' or
       p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;

select d_year, s_nation, p_category, sum(lo_revenue - lo_supplycost) as profit
from date, customer, supplier, part, lineorder
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_partkey = p_partkey and
      lo_orderdate = d_datekey and
      c_region = 'AMERICA' and
      s_region = 'AMERICA' and
      ( d_year = 1997 or
        d_year = 1998) and
      (p_mfgr = 'MFGR#1' or
       p_mfgr = 'MFGR#2')
group by d_year, s_nation, p_category
order by d_year, s_nation, p_category;

select d_year, s_city, p_brand1, sum(lo_revenue - lo_supplycost) as profit
from date, customer, supplier, part, lineorder
where lo_custkey = c_custkey and
      lo_suppkey = s_suppkey and
      lo_partkey = p_partkey and
      lo_orderdate = d_datekey and
      c_region = 'AMERICA' and
      s_nation = 'UNITED STATES' and
      ( d_year = 1997 or
        d_year = 1998) and
      p_category = 'MFGR#14'
group by d_year, s_city, p_brand1
order by d_year, s_city, p_brand1;

6.5.1. 検証1¶

検証1での結果を以下に示します。

図 6.2 JITコンパイルON/OFFでの実行時間の比較

削減率は、100×(t_off-t_on)/(t_off)で求められます。 t_offはJITコンパイルがOFFの場合の実行時間、t_onはJITコンパイルがONの場合の実行時間です。 いずれの演算を含むクエリにおいても約20%～30%実行時間が削減され、JITコンパイルを実行したときの方が実行時間が短くなることがわかります。 乗算と減算を含むクエリの実行時間がそのほかの演算を含むクエリの実行時間よりも長いのは、lineorderテーブルのもつ約6億行のレコードごとに演算を実行した結果、約6億行の演算結果が返却されるためと考えられます。

6.5.2. 検証2¶

検証2での結果を以下に示します。

図 6.3 JITコンパイルON/OFFでの実行時間の比較

データ型によってJITコンパイルの効果に差が出ることがわかります。 特に、numeric型は他のデータ型に比べて実行時間が長くなっています。 これはnumeric型が可変長サイズであるためと考えられます。

6.5.3. 検証3¶

検証3での結果を以下に示します。

図 6.4 JITコンパイルON/OFFでの実行時間

OLAP系の実用的なクエリを実行する際にも、JITコンパイルを実行することで実行時間が短くなることが確認できました。 また、クエリによって効果に差が見られました。

6.6.1. 検証1¶

検証1においては、乗算を含むクエリとsum関数の含むクエリ2種類の実行計画を確認します。

                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineorder  (cost=0.00..15259254.00 rows=600061440 width=4) (actual time=13.632..49875.825 rows=600037902 loops=1)
  Buffers: shared hit=7758486
Planning Time: 0.040 ms
JIT:
  Functions: 2
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.309 ms, Inlining 0.957 ms, Optimization 8.485 ms, Emission 4.106 ms, Total 13.857 ms
Execution Time: 66032.955 ms

                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineorder  (cost=0.00..15259254.00 rows=600061440 width=4) (actual time=0.006..71122.610 rows=600037902 loops=1)
  Buffers: shared hit=7758486
Planning Time: 0.043 ms
Execution Time: 87332.431 ms

                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=15259254.00..15259254.01 rows=1 width=8) (actual time=70981.559..70981.559 rows=1 loops=1)
  Buffers: shared hit=7758486
  ->  Seq Scan on lineorder  (cost=0.00..13759100.40 rows=600061440 width=4) (actual time=0.004..27183.683 rows=600037902 loops=1)
        Buffers: shared hit=7758486
Planning Time: 0.038 ms
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.565 ms, Inlining 1.459 ms, Optimization 20.451 ms, Emission 12.119 ms, Total 34.594 ms
Execution Time: 70982.215 ms

                                                           QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=15259254.00..15259254.01 rows=1 width=8) (actual time=90474.587..90474.587 rows=1 loops=1)
  Buffers: shared hit=7758486
  ->  Seq Scan on lineorder  (cost=0.00..13759100.40 rows=600061440 width=4) (actual time=0.004..28574.686 rows=600037902 loops=1)
        Buffers: shared hit=7758486
Planning Time: 0.047 ms
Execution Time: 90474.615 ms

6.6.2. 検証2¶

検証2においては、整数型であるinteger型とnumeric型を設定した際の実行計画を取得しました。 integer型の実行計画は検証1の考察（sum関数を含むクエリ）で示しているため、ここではnumeric型の実行計画のみを示します。

• JITコンパイルがONの場合

                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=15530386.20..15530386.21 rows=1 width=32) (actual time=86556.379..86556.380 rows=1 loops=1)
  Buffers: shared hit=8029987
  ->  Seq Scan on lineorder_n  (cost=0.00..14030306.36 rows=600031936 width=7) (actual time=0.005..28851.551 rows=600037902 loops=1)
        Buffers: shared hit=8029987
Planning Time: 0.051 ms
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.557 ms, Inlining 2.549 ms, Optimization 20.533 ms, Emission 12.185 ms, Total 35.824 ms
Execution Time: 86556.987 ms

• JITコンパイルがOFFの場合

                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=15530386.20..15530386.21 rows=1 width=32) (actual time=107847.607..107847.607 rows=1 loops=1)
  Buffers: shared hit=8029987
  ->  Seq Scan on lineorder_n  (cost=0.00..14030306.36 rows=600031936 width=7) (actual time=0.004..28785.690 rows=600037902 loops=1)
        Buffers: shared hit=8029987
Planning Time: 0.048 ms
Execution Time: 107847.637 ms

integer型のときの実行計画と同様に、オーバーヘッドになっている部分を特定することはできませんでしたが、シーケンシャルスキャンにかかる時間はJITコンパイルのON/OFFによらずほぼ同じであり、Aggregate処理による出力が開始/完了する時間はJITコンパイルがONの場合の方が早くなっていました。 integer型とnumeric型の間での差は、Aggregate処理における高速化度合いの差にあることがわかります。

6.6.3. 検証3¶

検証3においては、削減率が最も高かったNo.1-1のクエリと、最も低かったNo.2-2のクエリから実行計画を取得しました。

                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=18719494.34..18719494.35 rows=1 width=8) (actual time=44864.530..44864.530 rows=1 loops=1)
  Buffers: shared hit=7758524
  ->  Hash Join  (cost=74.19..18663739.86 rows=11150896 width=8) (actual time=112.756..44343.859 rows=11913972 loops=1)
        Hash Cond: (lineorder.lo_orderdate = date.d_datekey)
        Buffers: shared hit=7758524
        ->  Seq Scan on lineorder  (cost=0.00..18259561.20 rows=78025470 width=12) (actual time=112.507..39017.385 rows=78550619 loops=1)
              Filter: ((lo_discount >= 1) AND (lo_discount <= 3) AND (lo_quantity < 25))
              Rows Removed by Filter: 521487283
              Buffers: shared hit=7758486
        ->  Hash  (cost=69.66..69.66 rows=362 width=4) (actual time=0.225..0.226 rows=365 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 21kB
              Buffers: shared hit=38
              ->  Seq Scan on date  (cost=0.00..69.66 rows=362 width=4) (actual time=0.043..0.188 rows=365 loops=1)
                    Filter: (d_year = 1993)
                    Rows Removed by Filter: 2191
                    Buffers: shared hit=38
Planning Time: 0.101 ms
JIT:
  Functions: 18
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.511 ms, Inlining 1.941 ms, Optimization 72.381 ms, Emission 38.003 ms, Total 113.835 ms
Execution Time: 44866.132 ms

                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=18719494.34..18719494.35 rows=1 width=8) (actual time=64377.092..64377.093 rows=1 loops=1)
  Buffers: shared hit=7758524
  ->  Hash Join  (cost=74.19..18663739.86 rows=11150896 width=8) (actual time=0.242..63766.864 rows=11913972 loops=1)
        Hash Cond: (lineorder.lo_orderdate = date.d_datekey)
        Buffers: shared hit=7758524
        ->  Seq Scan on lineorder  (cost=0.00..18259561.20 rows=78025470 width=12) (actual time=0.006..58183.140 rows=78550619 loops=1)
              Filter: ((lo_discount >= 1) AND (lo_discount <= 3) AND (lo_quantity < 25))
              Rows Removed by Filter: 521487283
              Buffers: shared hit=7758486
        ->  Hash  (cost=69.66..69.66 rows=362 width=4) (actual time=0.232..0.232 rows=365 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 21kB
              Buffers: shared hit=38
              ->  Seq Scan on date  (cost=0.00..69.66 rows=362 width=4) (actual time=0.033..0.202 rows=365 loops=1)
                    Filter: (d_year = 1993)
                    Rows Removed by Filter: 2191
                    Buffers: shared hit=38
Planning Time: 0.102 ms
Execution Time: 64377.128 ms

                                                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=16129050.94..16131822.99 rows=6713 width=21) (actual time=88508.083..88720.456 rows=56 loops=1)
  Group Key: date.d_year, part.p_brand1
  Buffers: shared hit=7792538
  ->  Sort  (cost=16129050.94..16129727.17 rows=270492 width=17) (actual time=88505.310..88627.316 rows=961749 loops=1)
        Sort Key: date.d_year, part.p_brand1
        Sort Method: quicksort  Memory: 99713kB
        Buffers: shared hit=7792538
        ->  Hash Join  (cost=70125.25..16104645.50 rows=270492 width=17) (actual time=1050.155..87659.761 rows=961749 loops=1)
              Hash Cond: (lineorder.lo_orderdate = date.d_datekey)
              Buffers: shared hit=7792538
              ->  Hash Join  (cost=70030.26..16100831.24 rows=270492 width=17) (actual time=750.644..87139.416 rows=961749 loops=1)
                    Hash Cond: (lineorder.lo_suppkey = supplier.s_suppkey)
                    Buffers: shared hit=7792500
                    ->  Hash Join  (cost=41091.26..16064069.52 rows=1364746 width=21) (actual time=622.302..84999.357 rows=4813031 loops=1)
                          Hash Cond: (lineorder.lo_partkey = part.p_partkey)
                          Buffers: shared hit=7778538
                          ->  Seq Scan on lineorder  (cost=0.00..13759100.40 rows=600061440 width=16) (actual time=0.014..30166.398 rows=600037902 loops=1)
                                Buffers: shared hit=7758486
                          ->  Hash  (cost=41051.46..41051.46 rows=3184 width=13) (actual time=622.161..622.161 rows=11270 loops=1)
                                Buckets: 16384 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 657kB
                                Buffers: shared hit=20052
                                ->  Seq Scan on part  (cost=0.00..41051.46 rows=3184 width=13) (actual time=0.051..620.122 rows=11270 loops=1)
                                      Filter: (((p_brand1)::text >= 'MFGR#2221'::text) AND ((p_brand1)::text <= 'MFGR#2228'::text))
                                      Rows Removed by Filter: 1388730
                                      Buffers: shared hit=20052
                    ->  Hash  (cost=26461.59..26461.59 rows=198193 width=4) (actual time=127.785..127.785 rows=199889 loops=1)
                          Buckets: 262144  Batches: 1  Memory Usage: 9076kB
                          Buffers: shared hit=13962
                          ->  Seq Scan on supplier  (cost=0.00..26461.59 rows=198193 width=4) (actual time=0.016..102.187 rows=199889 loops=1)
                                Filter: ((s_region)::text = 'ASIA'::text)
                                Rows Removed by Filter: 800111
                                Buffers: shared hit=13962
              ->  Hash  (cost=63.33..63.33 rows=2533 width=8) (actual time=299.477..299.477 rows=2556 loops=1)
                    Buckets: 4096  Batches: 1  Memory Usage: 132kB
                    Buffers: shared hit=38
                    ->  Seq Scan on date  (cost=0.00..63.33 rows=2533 width=8) (actual time=298.936..299.241 rows=2556 loops=1)
                          Buffers: shared hit=38
Planning Time: 0.190 ms
JIT:
  Functions: 46
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.996 ms, Inlining 3.770 ms, Optimization 194.606 ms, Emission 100.255 ms, Total 301.627 ms
Execution Time: 88729.503 ms

                                                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate  (cost=16129050.94..16131822.99 rows=6713 width=21) (actual time=94027.557..94265.541 rows=56 loops=1)
  Group Key: date.d_year, part.p_brand1
  Buffers: shared hit=7792538
  ->  Sort  (cost=16129050.94..16129727.17 rows=270492 width=17) (actual time=94024.314..94150.612 rows=961749 loops=1)
        Sort Key: date.d_year, part.p_brand1
        Sort Method: quicksort  Memory: 99713kB
        Buffers: shared hit=7792538
        ->  Hash Join  (cost=70125.25..16104645.50 rows=270492 width=17) (actual time=779.793..93198.298 rows=961749 loops=1)
              Hash Cond: (lineorder.lo_orderdate = date.d_datekey)
              Buffers: shared hit=7792538
              ->  Hash Join  (cost=70030.26..16100831.24 rows=270492 width=17) (actual time=779.182..92970.569 rows=961749 loops=1)
                    Hash Cond: (lineorder.lo_suppkey = supplier.s_suppkey)
                    Buffers: shared hit=7792500
                    ->  Hash Join  (cost=41091.26..16064069.52 rows=1364746 width=21) (actual time=636.921..90822.476 rows=4813031 loops=1)
                          Hash Cond: (lineorder.lo_partkey = part.p_partkey)
                          Buffers: shared hit=7778538
                          ->  Seq Scan on lineorder  (cost=0.00..13759100.40 rows=600061440 width=16) (actual time=0.006..31115.221 rows=600037902 loops=1)
                                Buffers: shared hit=7758486
                          ->  Hash  (cost=41051.46..41051.46 rows=3184 width=13) (actual time=636.823..636.824 rows=11270 loops=1)
                                Buckets: 16384 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 657kB
                                Buffers: shared hit=20052
                                ->  Seq Scan on part  (cost=0.00..41051.46 rows=3184 width=13) (actual time=0.020..634.619 rows=11270 loops=1)
                                      Filter: (((p_brand1)::text >= 'MFGR#2221'::text) AND ((p_brand1)::text <= 'MFGR#2228'::text))
                                      Rows Removed by Filter: 1388730
                                      Buffers: shared hit=20052
                    ->  Hash  (cost=26461.59..26461.59 rows=198193 width=4) (actual time=141.636..141.636 rows=199889 loops=1)
                          Buckets: 262144  Batches: 1  Memory Usage: 9076kB
                          Buffers: shared hit=13962
                          ->  Seq Scan on supplier  (cost=0.00..26461.59 rows=198193 width=4) (actual time=0.010..116.053 rows=199889 loops=1)
                                Filter: ((s_region)::text = 'ASIA'::text)
                                Rows Removed by Filter: 800111
                                Buffers: shared hit=13962
              ->  Hash  (cost=63.33..63.33 rows=2533 width=8) (actual time=0.592..0.592 rows=2556 loops=1)
                    Buckets: 4096  Batches: 1  Memory Usage: 132kB
                    Buffers: shared hit=38
                    ->  Seq Scan on date  (cost=0.00..63.33 rows=2533 width=8) (actual time=0.008..0.335 rows=2556 loops=1)
                          Buffers: shared hit=38
Planning Time: 0.197 ms
Execution Time: 94271.519 ms

6.6.3.3. 削減率が低い原因の考察¶

削減率が低いクエリについて条件を変更して再検証しました。 具体的には、shared_buffersパラメータを64GBから100GBに変更、固定し、work_memパラメータを1,2,4GB、maintenance_work_memパラメータを8,16,32GBと変化させた際に削減率が変わるかを検証しました。

図 6.5 JITコンパイルON/OFFでの実行時間

表 6.12 JITコンパイルON/OFFでの実行時間（秒）¶

クエリNo.	JIT:OFF	JIT:ON	削減率(%)
original	119.962	109.932	8.361
W1-M8	139.948	126.646	9.505
W1-M16	141.113	127.229	9.839
W1-M32	142.326	127.772	10.225
W2-M8	140.028	126.374	9.751
W2-M16	142.223	127.922	10.055
W2-M32	148.642	129.573	12.829
W4-M8	123.327	115.550	6.306
W4-M16	126.472	117.974	6.719
W4-M32	130.843	123.784	5.395

結果としては、削減率が向上する場合はあったものの、実行時間が本検証よりも長くなってしまいました。 ここから、パラメータ設定ではなく、クエリの構造を紐解くことで原因がわかるのではないかと考えられます。

7.1.1. Star Schema Benchmarkとは¶

Star Schema Benchmarkとは、 Star Schema Benchmarkの論文 によって公表されている、TPC-HをもとにBIで用いられるものを模したファクトテーブル、ディメンジョンテーブル、クエリが設計されているベンチマークツールです。

大規模なデータを取り扱うので、I/O周りはもちろんのこと、多数のジョイン操作や集約演算が行われることから、通常のOLTPよりもCPUの処理性能と、CPUをどの程度うまく使えているかがベンチマーク結果を大きく左右します。

Star Schema Benchmarkでは、4種類/13本のクエリが定義されており、そのすべてについて検証を行いました。

7.2.1. パラレルクエリ¶

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

例えばSELECT文で1000万レコードを全件検索する場合、検索で使用するプロセスが1個だけだと1プロセスが1000万レコードをすべて検索する必要があります。 それに対して、例えば、検索で使用するプロセスが10プロセスあって、それらで分担することができれば、1プロセス当たりの検索対象は100万レコードと10分の1に減ります。 複数のプロセスで分担することで高速化を図るため、メニーコア環境での使用で、効果を発揮します。

複数のプロセスで検索処理を分担して実行することで、1プロセスあたりの検索自体にかかる実行時間は短くなりますが、ある処理を複数に分割する配分の制御や受け渡し、そして複数で処理した結果を集めて一つにまとめる処理などの実行時間が長くなります。 複数のプロセスで短縮できる時間があまり少ないと、パラレルクエリに変更することでむしろ余計に実行時間がかかってしまうこともあります。

そのため、プランナは多くの実行プランの中から、パラレルクエリのほうが効率的に処理できると判断した場合のみパラレルクエリの実行プランを選択します。

7.2.2. PostgreSQLでのパラレルクエリ¶

2018年現在、PostgreSQLで実装されているパラレルクエリに関する処理は、パラレルスキャン、パラレルジョイン、パラレル集約、B-Treeインデックス作成の四種類です。

前者三種類については、プランナが処理を分担したほうが効率的だと判断した場合、パラレルクエリのプランを採用します。

explain (analyze , verbose)で確認したパラレルクエリ プラン例

                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..112816.10 rows=38400 width=52) (actual time=0.198..224.017 rows=99740 loops=1)
  Output: log_id, date, "time", product_id, place_id, machine_id, app_id, access_time_second, access_count, err_code
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Seq Scan on public.access_log  (cost=0.00..107976.10 rows=9600 width=52) (actual time=0.017..209.797 rows=19948 loops=5)
        Output: log_id, date, "time", product_id, place_id, machine_id, app_id, access_time_second, access_count, err_code
        Filter: ((access_log.log_id % '77'::bigint) = 0)
        Rows Removed by Filter: 1516052
        Worker 0: actual time=0.016..214.828 rows=20088 loops=1
        Worker 1: actual time=0.016..214.518 rows=19967 loops=1
        Worker 2: actual time=0.020..218.061 rows=20817 loops=1
        Worker 3: actual time=0.015..213.654 rows=20054 loops=1

パラレルクエリでは、親プロセスに加えワーカーがバックグラウンドワーカープロセスとして起動されて処理を実行するため、検索プロセス数は親プロセスとワーカーの合計となります。そして、各プロセスで処理した結果をGatherノードで集約します。

また、パラレルクエリで起動されるワーカー数は、テーブルサイズによりデフォルトで起動する数が決まっています。

7.2.3. パラレルクエリに関連するパラメータ¶

パラレルクエリ時に関連する主なパラメータには以下のパラメータがあります。なお、max_parallel_workers_per_gatherパラメータのデフォルト値がバージョン9.6では0のため、デフォルト設定ではパラレルクエリは実行されません。バージョン10以降では同パラメータのデフォルト値は2であるため、デフォルト設定でもパラレルクエリが実行されます。

7.3.1. 検証ハードウェア構成¶

検証環境のハードウェア構成の概略を示します。

本パラレルクエリ検証ではDBサーバ上でSQLを直接実行する形式としました。 検証では下記構成の「DBサーバ#2」を使用しました。

図 7.1 検証ハードウェア構成

7.3.2. 検証ソフトウェア構成¶

検証環境のソフトウェア構成を示します。

7.4.1. 検証項目¶

パラレルクエリ検証では以下の3点を検証します。

7.4.2. データモデル¶

本検証で使用するデータモデルは、Star Schema Benchmarkにおいて定義されているデータモデルに準拠します。

Scale Factorは100GBを採用しました。各テーブルのサイズは、以下のとおりです。

  relname  |   rows    |    bytes
-----------+-----------+-------------
 part      |   1400000 |   164265984
 supplier  |   1000000 |   114376704
 customer  |   2999990 |   374030336
 date      |      2556 |      311296
 lineorder | 600113000 | 63557664768

7.4.3. 使用クエリについて¶

本検証では、Star Schema Benchmarkで定義される全クエリを使用します。

これは Star Schema Benchmarkの論文 の"3.1 Query Definition"に掲載されているものであり、 JITコンパイル　性能検証 で使用したものと同一です。

7.4.4. DB設定¶

性能測定にあたり、デフォルトから変更したパラメータは以下のとおりです。

7.4.5. 測定手順¶

測定に際しては、SQLの実行前にpg_prewarmを利用して、テーブル・インデックスのデータをディスクから一度読み込み、PostgreSQLのバッファ上、もしくはOSのディスクバッファ上に常駐させた状態にします。

SELECT relname, pg_prewarm(oid)
FROM pg_class
WHERE relnamespace in (
  SELECT oid
  FROM pg_namespace
  WHERE nspname='public'
) AND (
  relkind='r' OR relkind='i'
);

7.5.1. 検証A(パラレルクエリを有効にしたことによる効果)¶

上記の条件で検証を行った結果、バージョン9.6以降では、全クエリでパラレルクエリが動作しました。

バージョン毎の処理時間のグラフと表を以下に示します。

図 7.2 バージョンと処理時間の比較

検証の結果、クエリの処理時間について、バージョン9.5との比較で、バージョン9.6で2.34倍(q4.1)～2.87倍(q3.1)、バージョン10で2.42倍(q4.1)～3.04倍(q3.1)、バージョン11で2.54倍(q2.3)～3.20倍(q4.2)の高速化が認められました。

7.5.2. 検証B(並列処理数(起動するワーカーの数)の違いによる検証結果)¶

図 7.3 max_parallel_workersの値と処理時間の比較 (バージョン10 / workers = gather)

図 7.4 max_parallel_workersの値と処理時間の比較 (バージョン10 / workers = gather*4)

図 7.5 max_parallel_workersの値と処理時間の比較 (バージョン11 / workers = gather)

図 7.6 max_parallel_workersの値と処理時間の比較 (バージョン11 / workers = gather*4)

検証の結果、全クエリが、並列処理数の増加にともなって、処理時間が減少する傾向にありました。 グラフの形状からは、処理時間と並列処理数の間には、反比例に近い関係があると推測できます。

また、workers = gatherとworkers = gather*4を比較すると、バージョン10とバージョン11では、並列処理数が2から8の間では、gather*4の方が高速である傾向が確認できました。

ただ、バージョン11のq4.3は、並列処理数4以上の性能が頭打ちとなっており、バージョン11特有の問題が発生している可能性があります。

7.5.3. 検証C¶

図 7.7 max_parallel_maintenance_workersの値と処理時間の比較

検証の結果、並列処理数の増加にともなって、処理時間が減少する傾向にありました。 グラフの形状からは、処理時間と並列処理数の間には、反比例に近い関係があると推測できます。

7.5.4. 対照群(パラレルクエリoff)¶

図 7.8 バージョンと処理時間の比較

検証の結果、クエリの処理時間について、パラレルクエリoffの状態で、バージョン9.5との比較でバージョン9.6では0.91倍(q2.1)～1.01倍(q1.1)、バージョン10では0.94倍(q2.2)～1.21倍(q1.2)、バージョン11では0.94倍(q4.1)～1.29倍(q1.1)の高速化が認められました。

ここから、パラレルクエリ以外の部分でバージョン9.6とバージョン10、バージョン10とバージョン11の間において改善が行われ、大部分が高速化したことが伺えます。

7.6.1. 検証A¶

検証Aでは、パラレルクエリのないバージョン9.5よりバージョン9.6の方が高速であり、バージョン9.6よりもバージョン10、バージョン11の方が概ね高速である傾向が見られました。 ただし、バージョン10とバージョン11の間の差は、概ねバージョン11の方が高速であるものの、q3.1ではバージョン10が最も早く、q4.1とq4.2ではバージョン11が大幅に高速でした。

q3.1では、バージョン10とバージョン11で結合順が変化しており、これが原因となってバージョン11で速度が低下した可能性があります。

q4.1とq4.2では結合順は変化しておらず、ディメンジョンテーブルであるpart, supplier, dateの各テーブルのSeqScanに要する時間が、バージョン10と比較して1/3となっていることが確認できました。

パラレルクエリでのJoinノードにおける右側(Inner)のテーブルの検索処理は、バージョン10までは各パラレルワーカーがそれぞれのテーブルを全検索する関係で、検索時間が各パラレルワーカーでの所要時間分だけ増加していました。 バージョン11での高速化は、新機能であるハッシュテーブルの共有化により、テーブルスキャンが1回になった結果であると考えます。 SSBでのディメンジョンテーブルは、テーブルサイズが数百MBとなるものもあるため、ハッシュテーブルの共有化によるスキャン回数低減が大きな効果をもたらしたものと言えます。

以上から、バージョン11からの新機能であるハッシュテーブルの共有化は、複数のテーブルの結合操作時に、テーブルの大きさが大きいほど効果が出やすいと考えます。

なお、2016年度のパラレルクエリ検証 で使用した検証用クエリを使用しての検証も行いましたが、 こちらの結果 からはバージョン10とバージョン11の間の差はほとんどなく、この2バージョンのパラレルクエリの基本性能は互角と言って差し支えないと考えます。

7.6.2. 検証B¶

7.6.2.2. バージョン11における SSB q4.3 の性能¶

バージョン11において、SSBのq4.3の性能が並列処理数4で頭打ちとなりました。

バージョン9.6とバージョン10ではこの現象は起きておらず、バージョン11固有の現象であることがわかります。 今回の検証で使用したテーブルのサイズからは、並列処理数の理論値は9であるため、並列処理数9付近まで性能が改善することを予想していました。

そこで、実行計画上の並列処理数を確認しました。その結果を以下に示します。 実行計画上の並列処理数は、パラレルクエリ使用時に現れる Gather ノード内の"Workers Planned"の値として、EXPLAINの実行結果内に示されます。

表 7.19 SSB q4.3における並列処理数の設定と実行計画での値¶

max_parallel_workers_per_gather	Workers Planned
0	―
2	2
4	4
6	4
8	4
10	4

以上から、実行計画として並列処理数が4より大きい値にならないことがわかりました。 次に、現象が発生していないバージョン10と現象が発生するバージョン11の実行計画を比較します

図 7.9 Star Schema Benchmark q4.3の実行計画(バージョン10)

図 7.10 Star Schema Benchmark q4.3の実行計画(バージョン11)

バージョン10とバージョン11の間で、実行計画の形が変化していることがわかります。 ここで、各テーブルの物理サイズと起動予定ワーカー数を調べました。

表 7.20 SSBテーブル(SF=100GB)におけるテーブルサイズと起動予定ワーカー数¶

テーブル名	テーブルの物理サイズ	起動予定ワーカー数
lineorder	59 GB	9
customer	357 MB	4
date	336 kB	0
supplier	109 MB	3
part	157 MB	3

上記の中で、起動予定ワーカー数が9となるのは lineorder テーブルのみであり、一方で起動予定ワーカー数が4となるのは customer テーブルのみです。 ここで、上記のバージョン11の実行計画を確認すると、バージョン11の実行計画は、最上位にあるのHash Joinの左側(Outer)にcustomerテーブルのSeqScanが存在します。[11] 一方で、問題が発生していないバージョン10の実行計画では、lineorderテーブルのSeqScanが最上位のHash Joinの左側(Outer)から、左に辿っていった末端に存在します。

[11]	実行計画を出力するツールの制限でパラレルクエリ関連の情報が出力されていませんが、上記バージョン11の実行計画中のSeqScan、Hash JoinはそれぞれParallel Seq Scan、Parallel Hash Joinです。

このため、このクエリ全体の起動予定ワーカー数を決定するものは、最上位のJoinノードにおける左側(Outer)側の起動予定ワーカー数ではないか、という仮説を立て、バージョン11のソースコードを確認しました。 この結果、実行計画の最適化機構(Optimizer)内の結合方法を検討する部分のコードに、以下のコードがあることを確認しました。

/* This is a foolish way to estimate parallel_workers, but for now... */
pathnode->jpath.path.parallel_workers = outer_path->parallel_workers;

以上のコードがもととなり、クエリ全体の起動予定ワーカー数が customer テーブル由来の起動予定ワーカー数で規定されたものと考えます。 なお、このコードはバージョン9.6から存在しており、本件と同様の問題が、バージョン9.6以降のすべてのバージョンで起こりうると考えます。

本件については、PostgreSQLの開発コミュニティに報告の上、ロジックが改良されるよう働きかけていきます。

7.6.3. 検証C¶

B-Treeインデックスをパラレルで作成する処理において、複合インデックスの対象列が3以上となった場合に、2以下の場合と比較して、およそ1.25倍〜1.35倍の時間がかかるようになりました。

インデックスの作成は、作成対象のテーブルにアクセスする必要があるため、対象列の増加に伴って処理量が増えたためであると想定しました。 この場合、対象列と作成時間の差に関して、比例の関係となることになります。 そこで、対象列と作成時間の差を比較しました。結果を以下に示します。

以上から、対象列1と対象列2、対象列3と対象列4で、作成時間にほとんど差が見られず、比例関係にはないことがわかり、したがって、対象列数の増加に伴うものではないことがわかります。

作成時間に影響を与える要因としては、TOASTへのアクセスが発生する場合です。 TOASTではデータ圧縮されている他、テーブルデータの一部のみがTOASTに配置される場合もあり、この場合TOASTアクセスに伴うオーバヘッドが発生します。 しかし、TOASTにデータが配置されるのは、テーブルの1行のサイズが 1物理ページ(デフォルト 8kB)を 超える場合のみであり、今回の lineorder テーブルは、1行が94Bにとどまっているため、当てはまりません。

他の要因としては、インデックスのサイズが大きくなる場合です。 B-Treeインデックスの大きさは、複合列にまたがるインデックスの場合、各列に格納されている値の一意な組み合わせの数に依存します。 そこで、今回検証した列の組み合わせにおいて、組み合わせ数を算出しました。

以上から、複合インデックスの対象列が1の場合と2の場合の間で組み合わせ数が変わっておらず、2の場合と3の場合の間で大幅に増加、3の場合と4の場合の間で微増にとどまっていることがわかります。

これは、列数2以上の各複合インデックス作成時間と列数1のインデックス作成時間の差の傾向と一致します。 ここから、対象列数が3以上となった場合にインデックス作成時間が増加したのは、対象列における一意な組み合わせの数が増加したことによるものと考えます。