PostgreSQLのインデックスアドバイザツール（pg_qualstats）を使ったインデックス設計の自動化

はじめに

今回はPostgreSQLの統計情報収集ツールであるPoWA（PostgreSQL Workload Analyzer）で提供されているインデックスアドバイザ（pg_qualstats）が独立した機能として提供されるという情報を知ったので、どんなものかさっそく試してみようと思います。

pg_qualstatsは、これまでは大本のPoWAでインデックスの最適化をするための情報収集用のEXTENSIONとして使われていたものです。機能としては、Query情報のWHEREステートメントとJOIN句で見つかった述語の統計情報を保持して、その収集した情報をもとに最適なINDEXの条件を出すそうです。

ちなみにバージョン2からが独立版としての正式リリースみたいです。なので、今回試した時点では正式リリース版ではないのでご注意ください。（現在は、2.0.0dev版となっていました。リリース版は1.0.9。）

1. 機能概要（何ができるのか）

• ワークロードを流すとイイ感じのINDEXの設定例を出してくれる。

つまり、インデックスをどういうふうに設計するのかピンと来ていない人でも、いい感じのインデックス設計ができるツールということです。

イイ感じのインデックスを設計するのって、テーブル定義、データの内容、流すワークロード等を把握していないと作れないので、こういうツールでイイ感じのものが出るのなら、積極的に使うのもありかと思います。

2. 環境情報

今回は以下の環境で動作確認を行ってみました。

• VirtualBox
• CentOS 8
• PostgreSQL 12.1

3. インストール手順

3.1. PostgreSQL12

# dnf install libicu-devel -y
# wget https://yum.postgresql.org/12/redhat/rhel-8-x86_64/postgresql12-12.1-2PGDG.rhel8.x86_64.rpm
# wget https://yum.postgresql.org/12/redhat/rhel-8-x86_64/postgresql12-devel-12.1-2PGDG.rhel8.x86_64.rpm
# wget https://yum.postgresql.org/12/redhat/rhel-8-x86_64/postgresql12-libs-12.1-2PGDG.rhel8.x86_64.rpm
# wget https://yum.postgresql.org/12/redhat/rhel-8-x86_64/postgresql12-server-12.1-2PGDG.rhel8.x86_64.rpm
# rpm -ivh ./postgresql12*
# su - postgres
$ vi ~/.pgsql_profile

以下の内容を設定する。
PATH=/usr/pgsql-12/bin:$PATH
PGDATA=/var/lib/pgsql/12/data
export PATH PGDATA

$ source ~/.pgsql_profile

3.2. pg_qualstats

pg_qualstatsは、内部でllvmを使っているようなので、事前に色々と入れておく必要があります。

# rpm -ivh https://dl.fedoraproject.org/pub/epel/8/Everything/x86_64/Packages/e/epel-release-8-8.el8.noarch.rpm
# dnf --enablerepo=epel install ccache -y
# dnf clang llvm -y

あとはpg_qualstatsをmakeするだけ。

# su - postgres
$ git clone https://github.com/powa-team/pg_qualstats.git
$ cd pg_qualstats/
$ make
$ make install

4. 動作確認

本当にいい感じのインデックスを提案してくれるのかな？

4.1. PostgreSQL起動

ライブラリを読み込む必要があるため、postgresql.confに設定が必要なのでご注意ください。

$ initdb -D ~/12/data --no-locale --encoding=utf8
$ echo "shared_preload_libraries = 'pg_qualstats'" > ~/12/data/postgresql.conf
$ pg_ctl -D ~/12/data start

4.2. EXTENSION登録

続いて、対象のデータベースのEXTENSIONに登録します。

$ psql postgres
postgres=# CREATE EXTENSION pg_qualstats;
CREATE EXTENSION
postgres=# \dx
                             List of installed extensions
     Name     | Version  |   Schema   |                  Description                   
--------------+----------+------------+------------------------------------------------
 pg_qualstats | 2.0.0dev | public     | An extension collecting statistics about quals
 plpgsql      | 1.0      | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# \d
                 List of relations
 Schema |         Name          | Type  |  Owner   
--------+-----------------------+-------+----------
 public | pg_qualstats          | view  | postgres
 public | pg_qualstats_all      | view  | postgres
 public | pg_qualstats_by_query | view  | postgres
 public | pg_qualstats_pretty   | view  | postgres
(4 rows)

VIEW

それぞれのVIEWの説明は以下のとおりです。

Name	Overview
pg_qualstats	filters calls to pg_qualstats() by the current database.
pg_qualstats_all	sums the counts for each attribute / operator pair, regardless of its position as an operand (LEFT or RIGHT), grouping together attributes used in AND clauses.
pg_qualstats_by_query	returns only predicates of the form VAR OPERATOR CONSTANT, aggregated by queryid.
pg_qualstats_pretty	performs the appropriate joins to display a readable aggregated form for every attribute from the pg_qualstats view

pgbench実行後のVIEWの出力結果はコチラ。

postgres=# select * from pg_qualstats;
 userid | dbid  | lrelid | lattnum | opno | rrelid | rattnum | qualid | uniquequalid | qualnodeid | uniquequalnodeid | occurences | execution_count | nbfiltered | min_err_estimate_ratio | max_err_estimate_ratio | mean_err_estimate_ratio | stddev_err_estimate_ratio | min_err_estimate_num | max_err_estimate_num | mean_err_estimate_num | stddev_err_estimate_num | c
onstant_position | queryid |   constvalue   | eval_type 
--------+-------+--------+---------+------+--------+---------+--------+--------------+------------+------------------+------------+-----------------+------------+------------------------+------------------------+-------------------------+---------------------------+----------------------+----------------------+-----------------------+-------------------------+--
-----------------+---------+----------------+-----------
     10 | 13447 |  16675 |       1 |   96 |        |         |        |              | 4254011112 |       2407242025 |          1 |          100000 |      99999 |                      0 |                      0 |                       0 |                         0 |                    0 |                    0 |                     0 |                       0 |  
              67 |         | 68127::integer | f
     10 | 13447 |  16675 |       1 |   96 |        |         |        |              | 4254011112 |       1425050501 |          1 |          100000 |      99999 |                      0 |                      0 |                       0 |                         0 |                    0 |                    0 |                     0 |                       0 |  
              66 |         | 83276::integer | f
     10 | 13447 |  16675 |       1 |   96 |        |         |        |              | 4254011112 |       1951216183 |          1 |          100000 |      99999 |                      0 |                      0 |                       0 |                         0 |                    0 |                    0 |                     0 |                       0 |  
              50 |         | 32786::integer | f
(3 rows)

postgres=# select * from pg_qualstats_all;
 dbid  | relid | userid | queryid | attnums | opno | qualid | occurences | execution_count | nbfiltered | qualnodeid 
-------+-------+--------+---------+---------+------+--------+------------+-----------------+------------+------------
 13447 | 16675 |     10 |         | {1}     |   96 |        |          3 |          300000 |     299997 | 4254011112
(1 row)

postgres=# select * from pg_qualstats_by_query;
 uniquequalnodeid | dbid  | userid | qualnodeid | occurences | execution_count | nbfiltered | queryid |   constvalues    |       quals        
------------------+-------+--------+------------+------------+-----------------+------------+---------+------------------+--------------------
       1425050501 | 13447 |     10 | 4254011112 |          1 |          100000 |      99999 |         | {83276::integer} | {"(16675,1,96,f)"}
       1951216183 | 13447 |     10 | 4254011112 |          1 |          100000 |      99999 |         | {32786::integer} | {"(16675,1,96,f)"}
       2407242025 | 13447 |     10 | 4254011112 |          1 |          100000 |      99999 |         | {68127::integer} | {"(16675,1,96,f)"}
(3 rows)

postgres=# select * from pg_qualstats_pretty ;
 left_schema |    left_table    | left_column |   operator   | right_schema | right_table | right_column | occurences | execution_count | nbfiltered 
-------------+------------------+-------------+--------------+--------------+-------------+--------------+------------+-----------------+------------
 public      | pgbench_accounts | aid         | pg_catalog.= |              |             |              |          3 |          300000 |     299997
(1 row)

関数

ちなみにいくつかの関数も登録されています。以下であげたもの以外にもちらほら。

Name	Overview
pg_qualstats_exemple_queries	蓄積したすべてのクエリを返却する
pg_qualstats_exemple_query	蓄積された情報から、指定したqueryidのクエリを返却する（存在しない場合はNULL）
pg_qualstats_names	蓄積したすべてのクエリのテキスト情報を返却する
pg_qualstats_index_advisor	積した情報に基づいて最適なINDEX条件を提示する
pg_qualstats_reset	蓄積した統計情報をすべて削除する

各関数の詳細はいずれREADME.mdに記載されるでしょうが、現段階ではEXTENSIONで登録する際に実行されているsqlファイルを見たほうが早そうです。

例えばアドバイザ用の関数は以下のように定義されています。（pg_qualstats/pg_qualstats--2.0.0dev.sqlより抜粋）

CREATE OR REPLACE FUNCTION pg_qualstats_index_advisor (
    min_filter integer DEFAULT 1000,
    min_selectivity integer DEFAULT 30,
    forbidden_am text[] DEFAULT '{}')
    RETURNS json
AS $_$
DECLARE
（以下略）・・・

第一と第二引数はインデックス最適化のための閾値を設定する項目で、第三引数はアクセスメソッドの指定みたいですね。

4.3. index suggestions

pgbenchでやってみた結果

お手軽な実験ということで、試しにPKEYを消してINDEXがひとつもないpgbenchでやってみました。

INDEXを削った状態で$ pgbench -c 2 -T 30を実行してから、デフォルト閾値でアドバイザの関数を叩いてみると・・・

$ psql postgres
postgres=# select pg_qualstats_index_advisor();
                                   pg_qualstats_index_advisor                                    
-------------------------------------------------------------------------------------------------
 {"indexes" : ["CREATE INDEX ON public.pgbench_accounts USING btree (aid)"], "unoptimised" : []}
(1 row)

おぉ！なにかアドバイス的な情報が出ましたね。
pgbenchでの結果なので「まぁそうだよね。」という結果ですが、これが凝ったクエリだとどうなるのか見てみたいですね。

ゲキテキなBefore After

Before

$ pgbench -c 1 -T 30 postgres

number of transactions actually processed: 573
latency average = 52.429 ms
tps = 19.073385 (including connections establishing)
tps = 19.076093 (excluding connections establishing)

After（アドバイザ通りにCREATE INDEXしたあとの結果）

$ pgbench -c 1 -T 30 postgres

number of transactions actually processed: 23850
latency average = 1.258 ms
tps = 794.984303 (including connections establishing)
tps = 795.110924 (excluding connections establishing)

これは劇的！！

おわりに

今回はまだdev版ということで、ちょっと遊んでみただけですが、リリース版が出たら再度試してみようと思います。（次はpgbenchじゃなくてもう少し良さげな題材で試してみたい。）

あと時間があれば、アドバイザの内部ロジック（storeしたQueryのデータの使い方とか）がどうなっているのかも興味があるので、そちらは裏で見てみよう。