はじめに

2019年2月下旬に、pg_plan_advsrというPostgreSQLのツールがリリースされたので、遅まきながら、どんなものか調べてみた。

pg_plan_advsr is 何？

現状は日本語文書はないので、とりあえず、README.mdの書いてあった内容をGoogle先生にかけてみた。

pg_plan_advsrは、フィードバックループを使用した自動実行プランチューニングを提供するPostgreSQLの拡張機能である。
この拡張は、多数の結合と集約を含む分析クエリがあり、実行時間を短縮するための効率的な計画を立てたい場合に役立ちます。

とな。

現時点(2019-03-03)では、まだPOC段階であり（Readmeも一部TABな箇所がある）、商用環境での利用は時期尚早っぽいけど、どんなコンセプトの拡張機能なのかくらいは見ておこうかと。

動作要件

意外と動作要件はいろいろあるので、わりと面倒くさい（現時点では）。

PostgreSQL 10系以降で使える。(10.3以前は、たぶん未検証というだけ？）
- PostgreSQLバイナリをRPM等でインストールした環境では、postgresql-develパッケージも必要なので注意重点。
- 現状は、ソースビルドによる構築のみ対応。RPM等の提供はなし。
pg_hin_planとpg_store_planに依存している。pg_hint_planは結構有名なモジュールなので、使ったことがある人もいると思うけど、pg_store_planは自分自身もあまり使ったことがないんだよな・・・。
- 単にパッケージとして依存しているだけではなく、pg_hint_plan/pg_store_planの一部のソースコードをpg_plan_advsrのビルド環境にコピーする必要がある。
OSはRHEL7系と書いてあるけど、これもたぶん、6系は未検証というだけだと思う。
- 動作環境として、Widows OSについては明記されてはいない。pg_hint_planについては、WindowsOS対応版があるらしいというのは聞いたことがあるが、pg_store_plansについては不明。なので、pg_plan_advsrもWindowsOS上で動作するかは現時点では不明。

入れてみる

現状、ソースからビルドしたPostgreSQL 11.2が入っているEC2環境(configureオプションとしては、特に特別なオプションは不要)があるので、それに合わせてインストールしてみる。

必要なファイルの入手と展開

github上のREADME.mdだとPostgreSQL 10ベースで記述されているので、PostgreSQL 11用にちょこちょこ読み替えて試してみる。

$ wget https://github.com/ossc-db/pg_hint_plan/archive/REL11_1_3_2.tar.gz
$ wget https://github.com/ossc-db/pg_store_plans/archive/1.3.tar.gz

pg_hint_planはPostgreSQL 11対応のリリースバージョンがあるので、それを使うことにする。
pg_store_plans 1.3については、PostgreSQL 11への対応は明記されていないように見えるが、pg_plan_advsrのページに書かれている要件では、pg_store_plans => 1.3と記載されているので、そのとおりの版のtar.gzファイルをダウンロードしておく。

pg_plan_advsr自体はまだリリース用のソースパッケージも用意されていないので、github上のリポジトリをgit cloneして入手する必要がある。

$ git clone https://github.com/ossc-db/pg_plan_advsr.git pg_plan_advsr
$ ls -ld pg_plan_advsr/
drwxrwxr-x. 4 nuko nuko 4096 Mar 10 14:26 pg_plan_advsr/
$

pg_hint_plansと、pg_store_plansのソースパッケージを展開する。

$ tar xfz REL11_1_3_2.tar.gz
$ ls -ld pg_hint_plan-REL11_1_3_2/
drwxrwxr-x. 9 nuko nuko 4096 Nov 13 16:01 pg_hint_plan-REL11_1_3_2/
$ tar xfz 1.3.tar.gz
$ ls -ld pg_store_plans-1.3/
drwxrwxr-x. 6 nuko nuko 4096 Feb 28 15:51 pg_store_plans-1.3/
$

（うーん、同じ組織が作った似たようなモジュールなのに、フtar.gzァイル名や展開されるディレクトリ名の付与規則が全然違うのはなんでだぜ？）

依存モジュールのソースファイルのコピー

ここが、今の所イケてない部分ではあるのだけど、pg_plan_advsrをインストールするために、pg_hint_planやpg_store_plansのソースの一部をpg_plan_advsrのビルド用ディレクトリにコピーする必要がある。（たぶん、こういうインストールの問題もあるから、現状はPoCという位置づけなのだろう・・・）

$ cp pg_hint_plan-REL11_1_3_2/pg_stat_statements.c pg_plan_advsr/
$ cp pg_hint_plan-REL11_1_3_2/normalize_query.h pg_plan_advsr/
$ cp pg_store_plans-1.3/pgsp_json*.[ch] pg_plan_advsr/
$

依存モジュールのビルドとインストール

$ cd pg_hint_plan-REL11_1_3_2/
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ cd ..
$ cd pg_store_plans-1.3/
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$ cd ..
$

とりあえず、依存モジュールに関しては、CentOS7 + PostgreSQL 11環境では、特に問題なくビルドとインストールはできました。

pg_plan_advsrのビルドとインストール

さて、やっと本丸であるpg_plan_advsrのビルドをやってみる。

$ cd pg_plan_advsr
$ make USE_PGXS=1
$ make USE_PGXS=1 install
$

なお、今使っているgcc (GCC) 7.3.1 20180303 (Red Hat 7.3.1-5)だと、以下のような警告が出る。（とりあえず、issueは出しておいた）。

pg_plan_advsr.c:1854:5: warning: this ‘else’ clause does not guard... [-Wmisleading-indentation]
     else
     ^~~~
pg_plan_advsr.c:1860:2: note: ...this statement, but the latter is misleadingly indented as if it were guarded by the ‘else’
  appendStringInfo(str, "host=%s port=%s dbname=%s user=%s", pghost, pgport, dbName, login);
  ^~~~~~~~~~~~~~~~

なお、以前使っていた超古い環境(gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16))だと警告は出なかった。gccのどのバージョンから、この警告が出るようになったのかは調べてない。

まあ、警告は出ちゃうけど、とりあえず、ここまでは問題なく実施できた。

データベースへの登録と準備

PostgreSQLの設定パラメータ

pg_plan_advsrおよび依存モジュール(pg_hint_plan, pg_store_plans)は、PostgreSQLサーバ起動時にロードする必要があるため、PostgreSQLの設定パラメータshared_preload_librariesにモジュール名を指定する必要がある。

また、現状はパラレルクエリ、宣言パーティション、JIT対応は「テストされていない」と記述されている。動作自体「してしまうのか」はよくわからない。

そのためか、現状は、パラレルクエリを無効化するために、max_parallel_workers_per_gatherとmax_parallel_workersのパラメータに0を設定する必要があるようだ。

ただ、実際のところこれらの値は_PG_init()初期化関数内でチェックしているわけではなく、上記のパラメータ（おそらくはJIT関連のパラメータも）が有効になっていても、PostgreSQL自体は起動する。現状は「動作保証外」くらいの緩い扱いなのだろう。¹

また、pg_plan_advsrに関連するPostgreSQLの設定として、以下のプランナ挙動に関連する3つのパラメータを必要に応じて変更するようだ。

パラメータ名	意味	変更内容
geqo_threshold	遺伝的問い合わせ最適化が有効になるFROM句数。デフォルト値は12。
from_collapse_limit	副問い合わせを上位の問い合わせに併合する。デフォルト値は8。
join_collapse_limit	明示的な`JOIN`構文を`FROM`項目のリストに修正する。デフォルト値は、from_collapse_limitと同値。

でも、どういう値を設定すれば良いのかは、試行錯誤しながら考えて設定するのかなあ・・・。

データベースへの登録

pg_plan_advsrおよび依存モジュールである、pg_hint_plan, pg_store_plansモジュールは、利用するデータベースに登録する必要がある。このため、この3つのモジュールを、CREATE EXTENSIONコマンドで登録する必要がある。以下の例では、imdbという名前のデータベースを作成し、その上に3つの拡張機能をインストールした例である(pg_hint_planってdescription書いてないのか・・・)。

imdb=# CREATE EXTENSION pg_hint_plan;
CREATE EXTENSION
imdb=# create extension pg_store_plans;
CREATE EXTENSION
imdb=# create extension pg_plan_advsr;
CREATE EXTENSION
imdb=# \dx
                                 List of installed extensions
      Name      | Version |   Schema   |                     Description
----------------+---------+------------+------------------------------------------------------
 pg_hint_plan   | 1.3.2   | hint_plan  |
 pg_plan_advsr  | 0.0     | public     | auto plan tuning
 pg_store_plans | 1.3     | public     | track plan statistics of all SQL statements executed
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

なお、現状のpg_plan_advsrの制御ファイル(pg_plan_advsr.control)には、requiresの設定はないため、pg_hint_planやpg_store_plans拡張機能がインストールされていなくても、CREATE EXTENSIONコマンドは成功してしまうため、これらの依存拡張機能をうっかりインストールし忘れて、pg_plan_advsrを動作させようとしてハマることもあるかもしれない。²

検証準備

効果を確認するといっても、単純なクエリではその効果を確認することはできない。pg_plan_advsrには効果を確認するためのサンプルモデルが用意されているので、今回もそれを使って動作を確認してみる。合わせて、どんなモデルなクエリを使っているのかも調査してみた。以下、そのための準備内容をつらつら書いていく。

検証用のモデルの内容は https://github.com/ossc-db/pg_plan_advsr/blob/master/JOB/how_to_setup.md を参照（あるいはgit cloneしたリポジトリ内の、JOBフォルダ配下を参照）。

ロード用データの中身

ロード用のデータは、別途、IMDbのサイトからwgetでダウンロードする。今回は、/data/imdbというファルダを作成して、この下にデータを展開する。

$ cd /data/imdb
$ wget https://homepages.cwi.nl/~boncz/job/imdb.tgz

サイズは1.2GBくらいあるので、環境によってはちょい時間がかかる（弊社内の社内NWからDLしたら、たぶん、数時間コースかも・・・）。展開前は1.2GB、展開語は3.7GBほどのサイズのデータだ。

ここに格納されているのは、スキーマ定義のファイルschematext.sqlとロード用のCSVファイル。

schematext.sqlの中を確認すると、21種類のテーブルがある。なお、特にDDLのコメントはないので表名や列名から内容を推測するしかないのかな・・・と思ったけど、たぶん、IMDbというインターネット・ムービー・データベース（出典日本語Wikipedia）を模擬したものなのかな、と勝手に推測してみる。

まず、試験用のデータベースをcreatedbユーティリティで作成する。次に、さきほどダウンロードしたimdb.tgz内にある、schematext.sqlをpsqlで実行してテーブルを作成する。今回は、imdbという名前のデータベースを作成し、そのデータベース上にテーブルを作成する。

$ createdb -U postgres imdb
$ cd /data/imdb/
$ psql -U postgres imdb -f schematext.sql
$ psql -U postgres imdb -c "\d"
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | aka_name        | table | postgres
 public | aka_title       | table | postgres
 public | cast_info       | table | postgres
 public | char_name       | table | postgres
 public | comp_cast_type  | table | postgres
 public | company_name    | table | postgres
 public | company_type    | table | postgres
 public | complete_cast   | table | postgres
 public | info_type       | table | postgres
 public | keyword         | table | postgres
 public | kind_type       | table | postgres
 public | link_type       | table | postgres
 public | movie_companies | table | postgres
 public | movie_info      | table | postgres
 public | movie_info_idx  | table | postgres
 public | movie_keyword   | table | postgres
 public | movie_link      | table | postgres
 public | name            | table | postgres
 public | person_info     | table | postgres
 public | role_type       | table | postgres
 public | title           | table | postgres
(21 rows)

その後、データロードを行う。残念ながらロード用のスクリプトload_csv.sqlはそのまま使えないので、ロード元データのパスを書き換える必要がある。今回の環境では、/data/imdbディレクトリにCSVファイル群を置いたので、以下のように書き換えた。

$ cd pg_plan_advsr/JOB
$ vi load_csv.sql

書き換え後のイメージ。

\copy aka_name        from '/data/imdb/aka_name.csv' csv escape '\'
\copy aka_title       from '/data/imdb/aka_title.csv' csv escape '\'
・・・

書き換えたらpsqlでロードを実行する。

$ time psql -U postgres imdb -f load_csv.sql
COPY 901343
COPY 361472
COPY 36244344
COPY 3140339
COPY 4
COPY 234997
COPY 4
COPY 135086
COPY 113
COPY 134170
COPY 7
COPY 18
COPY 2609129
COPY 14835720
COPY 1380035
COPY 4523930
COPY 29997
COPY 4167491
COPY 2963664
COPY 12
COPY 2528312

real    5m25.637s
user    0m9.454s
sys     0m2.878s

t2.mediumの環境だとロードに5分くらいかかるので、ワインでも飲みながらまったり待ちましょう。

ロード後のテーブルのレコード数を以下に示す。

表名	レコード数
aka_name	901,343
aka_title	361,472
cast_info	36,244,344
char_name	3,140,339
comp_cast_type	4
company_type	234,997
company_type	4
complete_cast	135,086
info_type	113
keyword	134,170
kind_type	7
link_type	18
movie_companies	2,609,129
movie_info	14,835,720
movie_info_idx	1,380,035
movie_keyword	4,523,930
movie_link	29,997
name	4,167,4917
person_info	2963664
role_type	12

テーブル定義について簡単にまとめると、

どのテーブルもprimary keyとなるid列をもっている。
primary keyによって暗黙的に生成されるインデックス以外のインデックスは設定されていない。

レコード数の観点で見ると、cast_infoとmovie_infoが1000万件以上のデータが格納された比較的大きなテーブルだと思われる。

インデックス作成

各テーブルに存在するPrimary Keyによって暗黙的に生成されるインデックス以外のインデックスを作成する手段として、psql -f ./join-order-benchmark/fkindexes.sqlという手順がhow_to_setup.mdには記載されている。このファイルを使って外部キーのインデックスを作成するためには、以下のように

$ git clone https://github.com/gregrahn/join-order-benchmark

でリポジトリを取得し、join-order-benchmarkフォルダ内にある、fkindexes.sqlをpsqlを使って実行させる。

$ psql -U postgres imdb -f join-order-benchmark/fkindexes.sql

このインデックス作成も5, 6分程度待つので、お茶でも飲みながら一息つきましょうか。

オプション：なお、後々の実験のために、外部キーインデックスを削除するスクリプトも作っておくといいかも(fkindexes.sqlのCREATE INDEX文をDROP INDEX文に置換する)。

JOBフォルダ内にあるクエリjoin-order-benchmarkとシェル

JOIBフォルダ（"JOB"というは"Join Order Benchmark"の略称らしい）には、以下のファイルが格納されている。

ファイル名	種類	内容
31c_hinted.sql	SQLファイル	パラレルクエリを無効化した後、31c_org.sqlをpg_hint_planのヒント句付きで実行する。
31c_org.sql	SQLファイル	pg_hint_plan, pg_plan_advsrのパラメータを設定せずに、以下のクエリを実行する。 cast_info, company_name, info_type, info_type, keyword, movie_companies, movie_info, movie_info_idx, movie_keyword, name, titleの11テーブルの結合を行う検索。
31c_test.sql	SQLファイル	pg_hint_plan, pg_plan_advsr関連のパラメータを有効にしてから、31c_org.sql のクエリを実行する。
all_reset.sql	SQLファイル	pg_hint_planの管理テーブル(`hint_plan.hints`)の`TRUNCATE`、pg_plan_advsrの管理テーブル(`plan_repo.norm_queries`, `plan_repo.raw_queries`, `plan_repo.plan_history`)の`TRUNCATE`、pg_strore_planの初期化関数(`pg_store_plans_reset()`)の実行を行う。
analyze_table.sql	SQLファイル	IMDbの全テーブルに対して`ANALYZE`を実行する。でも、なんでデータベース全体の`ANALYZE`じゃいけないんだろ？pg_plan_advsr用の管理テーブルをANALYZEしちゃいけない理由でもあるのだろうか。
auto_tune_31c.sh	シェルファイル	31c_test.sql を16回実行した後に、pg_plan_advsrの管理テーブル`plan_repo.plan_history`の内容を表示する。
how_to_setup.md	Markdownファイル	JOBフォルダ内容の説明
load_csv.sql	SQLファイル	データロード用SQLファイル。書き換えが必要（上記参照）

動かしてみる

とりあえず動作環境は出来上がったようなので、クエリを投げて、pg_plan_advsrの効果を見てみる・・・と書きたいところだが、how_to_setup.mdには、サンプルシェルの動かし方、動かす順番は書いてあるものの、「どういう結果になった」「これがpg_plan_advsrの効果だ」みたいな説明がまったくない・・・自分で動かして確かめてみろということか。

ということで、まずは、how_to_setup.mdの手順に従って、シェルを動かしてみよう。まずはimdbのテーブルの統計情報を更新するためにanalyze_table.sqlを実行する。

$ psql -U postgres imdb -e -f ./analyze_table.sql
analyze aka_name         ;
ANALYZE
・・・
（以下略）

ANALYZEが終わったら、いよいよ./auto_tune_31c.shを実行する・・・と、その前に、PostgreSQL設定パラメータのlog_min_duration = 1000に設定して、毎回発行される重そうなクエリの実際の処理時間をログに出力するようにしておく。（contrib/auto_explainでスロークエリの実行計画は出さないの？というツッコミもあるかもしれないが、それは後のお楽しみにとっておく・・・）。

./auto_tune_31c.shだが、実行ロールや実行対象のデータベースを何も指定できないので、自分でシェルを書き換えるか、環境変数PGUSERやPGDATABASEを事前に設定しておく必要がある。今回は環境変数を設定して実行する。³

さて、今度こそ./auto_tune_31c.shの実行である。

TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
TRUNCATE TABLE
 pg_store_plans_reset
----------------------

(1 row)

=== 1 ===
SET
SET
SET
SET
SET
psql:31c_test.sql:63: INFO:  ---- pgsp_queryid ----------------
                1458904999
psql:31c_test.sql:63: INFO:  ---- pgsp_planid -----------------
                3464057368
psql:31c_test.sql:63: INFO:  ---- Execution Time --------------
                5328.335 ms
psql:31c_test.sql:63: INFO:  ---- Hints for current plan ------
SEQSCAN(it2) SEQSCAN(k) BITMAPSCAN(mk) INDEXSCAN(mi_idx) INDEXSCAN(mc)
INDEXSCAN(cn) INDEXSCAN(ci) INDEXSCAN(mi) SEQSCAN(it1) INDEXSCAN(n)
INDEXSCAN(t)
NESTLOOP(ci cn it1 it2 k mc mi mi_idx mk n t)
NESTLOOP(ci cn it1 it2 k mc mi mi_idx mk n)
NESTLOOP(ci cn it1 it2 k mc mi mi_idx mk)
NESTLOOP(ci cn it2 k mc mi mi_idx mk)
NESTLOOP(ci cn it2 k mc mi_idx mk)
NESTLOOP(cn it2 k mc mi_idx mk)
NESTLOOP(it2 k mc mi_idx mk)
NESTLOOP(it2 k mi_idx mk)
NESTLOOP(k mi_idx mk)
NESTLOOP(k mk)
LEADING( ((((((((it2 ((k mk )mi_idx ))mc )cn )ci )mi )it1 )n )t ) )
psql:31c_test.sql:63: INFO:  ---- Rows hint (feedback info)----
ROWS(ci cn it1 it2 k mc mi mi_idx mk n t #2825)
ROWS(ci cn it1 it2 k mc mi mi_idx mk n #2825)
ROWS(ci cn it1 it2 k mc mi mi_idx mk #2825)
ROWS(ci cn it2 k mc mi_idx mk #1471)
ROWS(cn it2 k mc mi_idx mk #1547)
ROWS(it2 k mc mi_idx mk #598256)
ROWS(it2 k mi_idx mk #63701)
ROWS(k mi_idx mk #191689)
ROWS(k mk #76714)
psql:31c_test.sql:63: INFO:  ---- Join count ------------------
                10
psql:31c_test.sql:63: INFO:  ---- Total diff rows of joins ----
                943899
psql:31c_test.sql:63: INFO:  ---- aplname ---------------------
                psql
（ここから実行計画ログ）
（中略）
 id | pgsp_queryid | pgsp_planid | diff_of_joins | execution_time
----+--------------+-------------+---------------+----------------
 33 |   1458904999 |  3464057368 |        943899 |       5328.335
 34 |   1458904999 |   866935215 |        119664 |        928.607
 35 |   1458904999 |   879392481 |         84550 |        648.514
 36 |   1458904999 |  2379296002 |         41572 |       1304.509
 37 |   1458904999 |  2999729080 |        124268 |       1045.313
 38 |   1458904999 |  4250720877 |         12080 |        682.502
 39 |   1458904999 |  1302411070 |        101162 |        719.199
 40 |   1458904999 |  2313250992 |        110413 |        747.104
 41 |   1458904999 |  1983337886 |         25800 |       1070.474
 42 |   1458904999 |  1463429373 |        120713 |       1030.758
 43 |   1458904999 |  3661910712 |          8911 |        766.421
 44 |   1458904999 |  3559462145 |          8996 |        778.863
 45 |   1458904999 |  4139435367 |        339556 |       2076.958
 46 |   1458904999 |  1119136869 |             0 |        627.893
 47 |   1458904999 |  1119136869 |             0 |        626.657
 48 |   1458904999 |  1119136869 |             0 |        627.421
(16 rows)

最後に、pg_plan_advsrの管理テーブルplan_repo.plan_historyの内容を表示して終了する。

さて、この実行ログ内には、同じクエリを発行したときの、実行計画（EXPLAIN ANALYZE)ログも出力されている。このログから、最終的に推定コスト値(cost)の数値を抜き出して、execution_timeと並べると以下のようになる。

実行回数	cost	execution_time(ms)
1	11466.65	5346.767
2	13927.51	947.564
3	21444.34	667.478
4	23328.26	1323.405
5	54780.03	1064.769
6	56603.80	702.465
7	58886.90	738.215
8	59034.31	765.923
9	59819.97	1091.462
10	67378.86	1049.849
11	76758.49	787.494
12	81597.52	798.319
13	85630.71	2097.033
14	101216.70	647.777
15	101216.70	648.455
16	101216.70	648.277

実行計画生成時に一番低いcostのものだからといって、実行時間が一番小さくなるとは限らない。現状のPostgreSQL+pg_hint_planだけで、最速のexecution_timeの実行計画選定のための情報を得ることができそうだ。

今後の調査

今回はサンプルとして提供された、31c_test.sqlを使って試してみた。このSQLは明示的にpg_hint_planのヒント情報は使っていないが、今後は、明示的にヒントを与えた場合や、プランナ向けのパラメータgeqo_thresholdを変更したときに、どういった挙動の変化になるかを確認しようと思う。

その他

Authorに書かれている(@yamatattsu)のはTwitterユーザID？
現時点ではそういうユーザはTwitter上にはないようだけど・・・

脚注

個人的には、_PG_init()内で明示的にチェックしてもいいんじゃないか、という気はしているけど、今後の開発でこれらパラレルクエリ制約等のPostgreSQL新機能の制約が解除されるのであれば、POCの現時点ではあえてチェックを入れなくてもいいのかもしれない。ただ、正式版としてリリースするなら、そうしたチェックを入れたほうが良いのではないか？ ↩
おそらく現段階ではPOCということもあるので、とくに対応はしていないと思うが、正式版リリース以降もpg_plan_advsrがpg_hint_plan, pg_store_plansに依存する方針が変わらないのであれば、requiredを明示的に記述して、インストール漏れなどの事故を防いだほうが良さそうに思える。 ↩
環境変数PGDATABASEやPGUSERをPostgreSQLサーバ起動時に認識させ、search_pathを適宜書き換えないと、pg_plan_advsrは正しく動作しない（取得した情報等を書き込むときにエラーになっていまう。これで数時間ハマった。これはpg_plan_advsr内で、libpqによる接続とクエリの実行を行っているのが原因だが、そういう作りにするなら、pg_plan_advsrの変数として(例：pg_plan_advsr.conninfo)を追加する仕組みも考えたほうが良さそう（複数DB時の扱いをどうするかは、更に悩ましいが）→libpqじゃなくて、SPI使うのじゃダメなのかな？これって ↩