pg_dbms_stats -- 統計情報の管理を行い、間接的に実行計画を制御します。
PostgreSQL は ANALYZE コマンドによりテーブルやインデックスからサンプリングした値を集計して統計情報として保持しています。 クエリ・オプティマイザは、この統計情報を利用してクエリのコストを計算し、最もコストの低い実行計画を選択します。このため、データの量や特性が変化したり、統計情報の精度が不十分であったりした場合には、選択される実行計画が変化する場合があります。
pg_dbms_stats パッケージはこのような予期せぬ実行計画の変化を防ぐための機能拡張です。プランナの処理に割り込んで、プランナが参照する統計情報を事前に作成したダミー統計情報に差し替えることで、選択される統計情報を固定します。「実行計画が運用中に急に変化し、システムの性能が低下する」というリスクを抑えたい場合に有効です。
pg_dbms_statsが統計情報を固定できるのオブジェクトは以下のとおりです。
pg_dbms_statsには、プランナが使用する統計情報を操作する機能として以下の8つがあります。なお、エクスポートを除く各機能は、SQL関数経由で利用します。各関数の詳細はオブジェクト一覧を参照してください。
バックアップに含める統計情報の範囲は、データベース(現在接続中のもの)・スキーマ・テーブル・列のいずれかの単位で指定できます。 例えばあるスキーマに含まれている全ての表とそれらの全ての列の統計情報を保存しておきたい場合は、スキーマ単位でバックアップします。基本的にはデータベース単位またはスキーマ単位などの大き目の単位でバックアップしておくことをお勧めします。
現在保存されているバックアップに関する情報は、dbms_stats.backup_historyテーブルで参照できます。dbms_stats.backup_historyテーブルの詳細についてはテーブルを参照してください。
リストア時に指定したオブジェクト以外の統計情報以外は変更されず元のまま残ります。リストアするバックアップ統計情報の指定方法は、以下の二種類があります。
基本的には任意世代以前のバックアップを削除できますが、不用意にバックアップを削除してしまうことを防ぐために、パージ後にデータベース単位のバックアップがひとつも残らない状況での一括削除はできません。この制限は、ユーザが強制削除を指定することで回避可能です。
統計情報をロックする範囲は、データベース(現在接続中のもの)・スキーマ・テーブル・列のいずれかの単位で指定できます。
ロックを解除すると、プランナがpg_classやpg_statisticを参照するようになります。統計情報のロックを解除する範囲は、データベース(現在接続中のもの)・スキーマ・テーブル・列のいずれかの単位で指定できます。なお、ロックと異なる単位を指定してロック解除することも可能です。
ロックを解除せずに列やテーブルを削除すると、使用しないダミー統計情報が残ります。このダミー統計情報を一括削除します。
用途に応じて、以下の二種類の統計情報をエクスポートできます。なお、エクスポートにはCOPYコマンドを使用するので、エクスポートファイル出力先ディレクトリにはPostgreSQL実行ユーザでファイルを作成できるディレクトリを指定してください。
サンプルファイルは、「pg_config --docdir」で表示されるディレクトリにあるextensionサブディレクトリにインストールされています。
インポートする統計情報の範囲は、データベース(現在接続中のもの)・スキーマ・テーブル・列のいずれかの単位で指定できます。インポートするファイルは、PostgreSQL実行ユーザが読み取れるディレクトリに配置してください。
pg_dbms_stats のインストール方法は、標準の拡張(EXTENSION)と同様です。
make を使ってビルドできます。PostgreSQLをインストールしたOSユーザでインストールしてください。 このとき、pg_config でビルドに必要な情報を収集しますので、複数バージョンがインストールされた環境では PATH 環境変数に注意して下さい。
$ cd pg_dbms_stats $ make $ su # make install
pg_dbms_stats は PostgreSQL の拡張(EXTENSION)ですので、この機能を利用するデータベースにスーパーユーザで接続して CREATE EXTENSION コマンド を実行してください。
コマンドの例を以下に示します。 dbnameは対象となるデータベース名を意味します。
$ psql -d dbname -c "CREATE EXTENSION pg_dbms_stats"
登録解除は DROP EXTENSION コマンドで可能です。登録を解除しても dbms_stats スキーマはそのまま残りますので、必要に応じて削除して下さい。
pg_dbms_statsを有効にするには、以下の例のようにpg_dbms_statsの共有ライブラリをロードしてください。 全てのセッションでpg_dbms_statsを有効にするには、postgresql.confのshared_preload_libraries GUCパラメータに'pg_dbms_stats'を追加してからサーバを再起動して下さい。
postgres=# LOAD 'pg_dbms_stats'; LOAD postgres=#
注意: pg_dbms_stats をデータベースに登録せずに、ロード後にSQL文を実行すると以下に示す例のようなエラーとなります。 pg_dbms_stats を使うときは、データベースへの登録を忘れないように注意してください。
test=# SELECT * FROM test;
ERROR: schema "dbms_stats" does not exist
LINE 1: SELECT relpages, reltuples, curpages FROM dbms_stats.relati...
^
QUERY: SELECT relpages, reltuples, curpages FROM dbms_stats.relation_stats_locked WHERE relid = $1
test=#
pg_dbms_statsをロードしたうえで無効にしたい場合は、以下の例のようにpg_dbms_stats.use_locked_statsをoffに設定してください。
test=# SET pg_dbms_stats.use_locked_stats TO off; SET test=# SELECT * FROM test; -- 通常の基準でプランを作成 ... test=# SET pg_dbms_stats.use_locked_stats TO on; SET test=# SELECT * FROM test; -- ダミー統計情報でプランを作成 ...
全てのセッションでpg_dbms_statsを無効にするには、postgresql.confでpg_dbms_stats.use_locked_statsをoffに設定してからサーバを再起動して下さい。 PostgreSQL 9.1の環境でpg_dbms_stats.use_locked_statsをpostgresql.confファイルで設定するためにはcustom_variable_classesにpg_dbms_statsを加える必要があります。
# postgresql.conf shared_preload_libraries = 'pg_dbms_stats' custom_variable_classes = 'pg_dbms_stats' # 9.2以降は廃止されたため記述不要 pg_dbms_stats.use_locked_stats = offPostgreSQL 9.2以降ではcustom_variable_classesは廃止されているため、pg_dbms_stats.use_locked_statsを標準のGUCパラメータと同様に記述することができます。
pg_dbms_stats をアンインストールするときは、以下の手順を実行してください。 dbnameは対象となるデータベース名を意味します。
$ cd pg_dbms_stats $ su # make uninstall
$ psql -d dbname -c "DROP EXTENSION pg_dbms_stats"
$ psql -d dbname -c "DROP SCHEMA dbms_stats"
大きく分けて「バックアップ主体」「ロック主体」「エクスポート主体」の3つの運用方法があります。 どの運用方法が適しているか判断できない場合には、まず「バックアップ主体」で運用することをお奨めします。
サービス運用中に統計情報を日々バックアップし、問題が生じたらリストアする場合には backup_xxx() と restore_xxx() 関数を使用します。 特に問題がない限り、データベース単位でバックアップしてください。
統計情報をリストアする方法は、バックアップ世代を表すバックアップIDを指定する方法と、どの時点の統計情報に復元するかを表すタイムスタンプを指定する方法の2種類です。 バックアップIDを指定してリストアする場合、 バックアップIDが一致するデータを使ってリストアします。 リストアしたいバックアップ世代のIDを指定してリストアしてください。 タイムスタンプを指定してリストアする場合、 オブジェクトごとに、指定した日時以前のバックアップデータを使ってリストアします。 リストアしたい日時を指定してデータベース単位でリストアしてください。 特に問題がない限り、バックアップIDを指定する方法でリストアしてください。
-- 日々バックアップを取り、その後 ANALYZE する。
test=# SELECT dbms_stats.backup_database_stats('comment');
backup_database_stats
-----------------------
1
(1 row)
test=# ANALYZE;
ANALYZE
test=#
-- 1日前のバックアップを書き戻し、その値でロックする。 test=# SELECT dbms_stats.restore_database_stats(now() - '1 day');
注意: タイムスタンプを指定してリストアする場合、リストア対象のオブジェクトに含まれるテーブルや列それぞれについて、指定したタイムスタンプ以前で最新の統計情報をリストアします。 このため、単位の小さなオブジェクトでバックアップしていたとき、指定したタイムスタンプ以前にバックアップしていないテーブルや列の統計情報はリストアされず、統計情報がユーザの想定通りにならない場合がありますので注意してください。
統計情報バックアップデータをリストアしたときの例を以下に示します。 なお、time列は各テーブルの統計情報をバックアップした時のタイムスタンプを意味します。
test=# SELECT b.id, b.time, r.relname
FROM dbms_stats.relation_stats_backup r
JOIN dbms_stats.backup_history b ON (r.id=b.id)
ORDER BY id;
id | time | relname
----+------------------------+-----------------
4 | 2012-01-01 00:00:00+09 | public.droptest
5 | 2012-01-02 12:00:00+09 | public.test
(5 rows)
test=# SELECT dbms_stats.restore_database_stats('2012-01-03 00:00:00+09');
restore_database_stats
------------------------
test
droptest
(2 rows)
test=#
サービス開始時に統計情報をロックして、そのまま運用する場合には、lock_xxx() 関数を使用します。
test=# SELECT dbms_stats.lock_database_stats(); lock_database_stats --------------------- droptest test (2 rows) test=#
サービス運用中の統計情報をエクスポートし、他のデータベースにインポートすることで実行計画を再現したい場合には、export_xxx_stats-
$ cd pg_dbms_stats
$ psql -d test -f export_effective_stats-9.1.sql
BEGIN
COMMIT
$ psql -d test2 -c "SELECT dbms_stats.import_database_stats('$PWD/export_stats.dmp')"
import_database_stats
-----------------------
(1 row)
$
注意:
統計情報をエクスポートするとき、現在のパスにexport_stats.dmpファイルを作成します。
このとき、同名のファイルが既に存在する場合、新しいエクスポート結果で上書きします。
上書きを避けたい場合は、既存のファイル名を変更するなどして対応してください。
また、統計情報をインポートするときに指定する情報は、対象となるファイルの絶対パスです。
相対パスを指定すると以下に示す例のようなエラーとなりますので注意してください。
test=# select dbms_stats.import_database_stats('export_stats.dmp');
ERROR: relative path not allowed for dbms_stats_export to file
test=#
また、エクスポートファイルにはCOPYのバイナリフォーマットを利用しているため、エクスポート元とインポート先でアーキテクチャやPostgreSQLバージョンに差がある場合は正常に動作しない場合があります。詳しくは、COPYコマンドのドキュメントを参照して下さい。
pg_dbms_stats を使用する際には、以下の使用上の注意と制約があります。
pg_dbms_statsは、ANALYZEで作成された統計情報の代わりに pg_dbms_stats が保持する独自の統計情報を PostgreSQL に使わせることで実行計画を制御します。使用する統計情報は、以下のいずれかの方法で選択できます。
pg_dbms_stats では、独自の統計情報をいくつかのテーブル/ファイル群で管理します。
なお、pg_dbms_stats が提供するいずれの機能も、PostgreSQL が元々使用してる統計情報 (pg_catalog.pg_classやpg_catalog.pg_statistic) を変更しません。
pg_dbms_statsの各機能は、前節で説明した独自の統計情報テーブルの内容をSQL文で更新することで実現されています。ただし、ユーザが直接SQL文を実行すると操作ミスによる削除やデータ不整合などが起こりうるため、ユーザインターフェースとして各機能に対応するSQL関数を提供しており、これらを使っての操作が強く推奨されています。
pg_dbms_stats は ANALYZE を無効化も禁止もしません。 ANALYZE は pg_statistic システムカタログを変更しますが、最終的に pg_dbms_stats の独自統計情報で値を隠されます。 効果の無い ANALYZE を繰り返すのは資源の無駄と考えるのであれば、autovacuum による ANALYZE を無効化してください。 無効化は、下記のように ANALYZE の必要性判定の閾値を上げることで行います。 ただし、無効化しなくても異常な動作をすることはありません。
全体設定の場合
# postgresql.conf autovacuum_analyze_threshold = 100000 autovacuum_analyze_scale_factor = 100
個別設定の場合
test=# ALTER TABLE tbl SET ( autovacuum_analyze_threshold = 100000, autovacuum_analyze_scale_factor = 100);
実行計画を作成する際、クエリ・オプティマイザは以下の統計情報または実際のデータの状態に基づいてコストを計算します。pg_dbms_stats は、これら全ての情報をロックすることができます。実際のファイルサイズが変化しても、ロックしたときのファイルサイズを継続して参照します。