PostgreSQL

更新日:2024年4月17日

pg_dbms_stats とは

pg_dbms_stats は ANALYZE が収集する統計情報を管理するためのツールです。

PostgreSQL の SQL 実行は、ANALYZE コマンドによって収集される統計情報をもとに、コスト計算を行い、最も早く実行できると予想される実行プランが選択されます。
もし、統計情報の精度が不十分な状態で SQL を実行すると、効率の悪い実行プランが選択され、性能低下の要因となります。

統計情報を常に最新にしておくことで、PostgreSQL のプランナは常に最適なプランを選択し実行することが可能ですが、プランナの不具合など、なんらかの原因によっては、最適なプランが選択されないケースがあります。

そのような事態に陥った場合、PostgreSQL 本体の機能だけでは対処が難しいため、pg_dbms_stats や pg_hint_plan を使って、想定するプランが選択されない理由を調査したり、一時的に実行プランを固定することができます。

今回は、使用する統計情報の管理が可能な pg_dbms_stats について紹介します。

pg_dbms_stats のしくみ

PostgreSQL では、クライアントから SQL を受けると、プランナと呼ばれる処理で統計情報をもとに、実行計画が作成されます。

SQL 実行時の流れ

pg_dbms_stats は、プランナが参照する統計情報を管理するツールです。モジュールをロードすると、プランナは pg_dbms_stats の統計情報を参照するようになります。

pg_dbms_stats モジュールロード時

また、pg_dbms_stats 統計情報のバックアップを取得しておくことができ、リストアすることで、任意の地点の統計情報を呼び出すことができます。
リストアで呼び出した統計情報は、ANALYZE によって更新されるまでの一時的なものですが、統計情報を固定(ロック)することも可能です。

ここでは、望ましくない実行計画が選択されるケースについて、pg_dbms_stats を使って統計情報を固定し、望ましい実行計画が選択されるように設定してみます。

インストール

ソースコードは こちらのページからダウンロードできます。

Linux (RHEL) むけには rpm パッケージも用意されています。
Windows むけのバイナリ配布物は公式サイトからリリースされていませんが、Windowsむけのビルドも可能です。SRA OSS の PostgreSQLサポートサービスでは pg_dbms_stats の Windowsバイナリを提供しています。

今回は　ソースコードから Linux にインストールする方法をご紹介します。

インストールに使用した PostgreSQL のソースコード内の contrib ディレクトリへ移動し、ダウンロードした tar.gz ファイルを展開し、ビルド、インストールします。
pg_dbms_stats の マニュアルページ には、動作環境として PostgreSQL 9.1、9.2 と記載がありますが、ここでは執筆時の最新バージョンである PostgreSQL 9.3.5 にて検証します。

$ cd postgresql-9.3.5/contrib/
$ tar zxf pg_dbms_stats-1.3.3.tar.gz
$ cd pg_dbms_stats-1.3.3
$ make
$ make install

インストールが完了後、PostgreSQL を起動し利用するデータベースに pg_dbms_stats モジュールを登録します。

$ psql
psql (9.3.5)
Type "help" for help.

postgres=# CREATE EXTENSION pg_dbms_stats;
CREATE EXTENSION

postgres=# LOAD 'pg_dbms_stats';
LOAD

SQL 実行時は、”LOAD” 文にてモジュールをロードすることで、pg_dbms_stats が利用できますが、毎回実行するのは手間ですので、postgresql.conf の shared_preload_libraries を設定しておきます。

shared_preload_libraries = 'pg_dbms_stats'

動作確認

検証データ作成

まず、検証データを作成します。
pgbench というベンチマークツールを使い、100 万件のデータをロードし、ANALYZE にて統計情報更新後 50 万件のデータを SELECT してみます。
-i は初期化、-s はスケール(10万件×10)を指定しています。

$ pgbench -i -s 10
$ psql
postgres=# ANALYZE;
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
    QUERY PLAN 
--------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..23670.15 rows=499486 width=97) (actual time=0.027..181.968 rows=500001 loops=1)
   Index Cond: ((aid >= 200000) AND (aid <= 700000))
 Total runtime: 235.792 ms
(3 rows)

次に無数の更新を発生させ、同じクエリを発行します。
(-c は同時コネクション数、-T は 60 秒間の測定を行うオプションです。)

$ pgbench -c 5 -T 60
$ psql
postgres=# ANALYZE;
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
    QUERY PLAN 
--------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts (cost=0.00..31551.00 rows=501860 width=97) (actual time=40.193..279.338 rows=500001 loops=1)
 Filter: ((aid >= 200000) AND (aid <= 700000))
   Rows Removed by Filter: 499999
 Total runtime: 334.066 ms
(4 rows)

無数の更新後 ANALYZE によって統計情報が更新される前は、Index Scan にて 235 ms かかっていますが、統計情報更新後は Seq Scan が選択され 334ms かかっています。

EXPLAIN ANALYZE の実行結果は、メモリに載った状態の結果を得るため、それぞれ 2 回以上実行し、2回目以降の値を掲載しています。
メモリに載っていない初回実行では、上記 Seq Scan は 26800 ms 程かかっています。

この現象は、本来 postgresql.conf のパラメータを調整することで Index 利用が優位の場合は Index Scan が選択されるようチューニングできますが、ここでは、この実行プランの変更を pg_dbms_stats によって抑制できることを確認してみます。

(お手元の環境で検証を行う場合は、前者が Index Scan になり、後者が Seq Scan に変わるように WHERE 句にて取得する件数を調整してください。)

統計情報のバックアップとリストア

まず、Index Scan が使われる状態の統計情報を保存します。
CLUSTER 文は指定した INDEX 順に、タプルの格納位置を並び替える SQL です。
CLUSTER 後に ANALYZE することで、INDEX 利用が優位な統計情報となります。

postgres=# CLUSTER pgbench_accounts USING pgbench_accounts_pkey;
CLUSTER
postgres=# ANALYZE;
ANALYZE
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.00..23654.74 rows=499169 width=97) (actual time=0.062..216.804 rows=500001 loops=1)
   Index Cond: ((aid >= 200000) AND (aid <= 700000))
 Total runtime: 271.711 ms
(3 rows)

Index Scan 選択を確認できたら、dbms_stats.backup_database_stats() にて統計情報をバックアップします。
引数には任意のコメントを入力します。戻り値のバックアップ ID は、リストア時に使用します。

postgres=# SELECT dbms_stats.backup_database_stats('Index Scan');
 backup_database_stats
-----------------------
                     1
(1 row)

検証データを準備したときと同じように、無数に更新を発生させ、Index を利用しない方が早いと判断されるよう統計情報を更新します。
Seq Scan が選択されていることを確認し、統計情報をバックアップします。

$ pgbench -c5 -T60
$ psql
postgres=# ANALYZE;
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts (cost=0.00..31622.00 rows=501987 width=97) (actual time=40.592..269.513 rows=500001 loops=1)
 Filter: ((aid >= 200000) AND (aid <= 700000))
   Rows Removed by Filter: 499999
 Total runtime: 323.785 ms 
(4 rows)

postgres=# SELECT dbms_stats.backup_database_stats('Seq Scan');
 backup_database_stats 
-----------------------
                     2
(1 row)

統計情報のバックアップ一覧は、backup_history テーブルにて確認できます。

postgres=# SELECT * FROM dbms_stats.backup_history;
 id |             time              | unit |  comment
----+-------------------------------+------+------------
  1 | 2014-10-01 10:43:14.302692+09 | d    | Index Scan
  2 | 2014-10-01 10:47:32.848737+09 | d    | Seq Scan
(2 rows)

統計情報のリストアは、backup_history の id を引数として dbms_stats.restore_stats() を実行します。
(今回は触れませんが、日時を指定してリストアすることも可能です。)

postgres=# SELECT dbms_stats.restore_stats(1);
        restore
-----------------------
 pgbench_accounts
 pgbench_accounts_pkey
 pgbench_branches
 pgbench_branches_pkey
 pgbench_history
 pgbench_tellers
 pgbench_tellers_pkey
(7 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..23844.98 rows=503178 width=97) (actual time=0.019..195.555 rows=500001 loops=1)
   Index Cond: ((aid >= 200000) AND (aid <= 700000))
 Total runtime: 249.305 ms
(3 rows) 

postgres=# SELECT dbms_stats.restore_stats(2);
   restore_stats 
-----------------------
 pgbench_accounts
 pgbench_accounts_pkey
 pgbench_branches
 pgbench_branches_pkey
 pgbench_history
 pgbench_tellers
 pgbench_tellers_pkey
 (7 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts (cost=0.00..31606.00 rows=497157 width=97) (actual time=37.753..256.228 rows=500001 loops=1)
   Filter: ((aid >= 200000) AND (aid <= 700000))
   Rows Removed by Filter: 499999
 Total runtime: 309.954 ms
(4 rows)

それぞれバックアップ時の統計情報を呼び出すことで、スムーズな調査が実施できます。
今回は紹介していませんが、統計情報のインポート機能を利用することで検証環境にて、調査を行うことも可能です。

統計情報の固定化

誤ったプラン選択により SQL 実行に時間がかかっていることが判明した場合、その問題が解決するまで、実行計画を固定しておきたいケースがあります。
pg_dbms_stats では統計情報を固定化することで、実行計画の変更を抑制することが可能です。

統計情報の固定には、dbms_stats.lock_<対象>_stats を使います。
ここでは、上記検証で利用した SELECT 文についてのみ Index Scan に固定するため、バックアップ ID =1 をリストア直後に pgbench_accounts テーブルの aid カラムの統計情報をロックしてみます。

postgres=# SELECT dbms_stats.restore_stats(1);
     restore_stats
-----------------------
 pgbench_accounts
 pgbench_accounts_pkey
 pgbench_branches
 pgbench_branches_pkey
 pgbench_history
 pgbench_tellers
 pgbench_tellers_pkey
(7 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..23844.98 rows=503178 width=97) (actual time=0.026..201.753 rows=500001 loops=1)
   Index Cond: ((aid >= 200000) AND (aid <= 700000))
 Total runtime: 256.119 ms
(3 rows)

postgres=# SELECT dbms_stats.lock_column_stats('public', 'pgbench_accounts', 'aid');
 lock_column_stats 
-------------------
 pgbench_accounts
(1 row)

統計情報を固定後は、pgbench によって無数の更新を行っても、常に Index Scan が選択されるようになります。

$ pgbench -c5 -T60
$ psql
postgres=# ANALYZE;
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..23844.98 rows=503178 width=97) (actual time=0.028..190.523 rows=500001 loops=1) 
   Index Cond: ((aid >= 200000) AND (aid <= 700000))
 Total runtime: 245.081 ms
(3 rows)

固定した統計情報の解除は、dbms_stats.unlock_<対象>_stats を使います。
ここでは、pgbench_accounts テーブルを指定して解除してみます。

$ psql
postgres=# SELECT dbms_stats.unlock_table_stats('public', 'pgbench_accounts');
 unlock_table_stats
--------------------
(0 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts WHERE aid >= 200000 AND aid <= 700000;
     QUERY PLAN 
--------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts (cost=0.00..31678.00 rows=496671 width=97) (actual time=39.953..265.779 rows=500001 loops=1)
   Filter: ((aid >= 200000) AND (aid <= 700000))
   Rows Removed by Filter: 499999
 Total runtime: 319.821 ms
(4 rows)

おわりに

PostgreSQL の統計情報管理ツール pg_dbms_stats はいかがでしたでしょうか。

上記で紹介した他に、保存中の統計情報を削除(パージ)する dbms_stats.purge_stats やインポートする
dbms_stats.import_<対象>_stats などがあります。
機能や使い方の詳しい説明は、日本語マニュアルがありますので、ユーザマニュアル をご覧ください。