pg_dbms_statsで統計情報を固定化して、実行計画を制御する
PostgreSQLインサイド

統計情報を固定化して実行計画を制御するチューニング方法について解説します。SQLが実行される仕組みとプランナ（オプティマイザ）については「SQLチューニングの概要」を参照してください。

1. 統計情報を固定化する

PostgreSQLでは、プランナ（オプティマイザ）が入力されたSQL文のクエリをもとに統計情報を参照して、最も速くてコストが低いと予測される方法を選択し、実行計画を作成します。しかし、必ずしもプランナ（オプティマイザ）が最適な実行計画を作成するとは限りません。例えば、大量の更新クエリなどで統計情報の最新化が間にあわない場合や、刻々と統計情報が変化する場合に、プランナ（オプティマイザ）が作成する実行計画が不安定になることがあります。その結果、レスポンスが平準化されなかったり、一時的なスループットの遅延が発生したりします。
その対策の1つとして、pg_dbms_statsを利用して統計情報を固定化し、常にその統計情報が利用されるようにチューニングする方法があります。

pg_dbms_statsとは

pg_dbms_statsは、統計情報を管理し、間接的に実行計画を制御するツールで、Linux上、Windows上およびSolaris上で動作します。PostgreSQLのプランナ（オプティマイザ）が不適切な実行計画を選択した場合の対処として、ユーザーがpg_dbms_statsを利用して統計情報を固定化できます。
pg_dbms_statsには以下の機能があります。

以下にpg_dbms_statsの仕組みを図解します。

参考

• pg_dbms_statsの仕様については、オープンソース・ソフトウェアのWebページを参照してください。
• pg_dbms_statsは便利な機能ですが、注意点もあります。お使いの前には必ず“3. pg_dbms_statsの注意点”をお読みください。

2. pg_dbms_statsを使う

pg_dbms_statsを使い、統計情報を固定化する方法について順を追って、見ていきましょう。検証には以下のようなシステムと業務を想定しています。

• 急なレスポンス低下が許されないシステムのため、VACUUMとANALYZEの実行は自動バキュームではなく管理者が制御する。
• 対象の業務テーブルは頻繁にアクセスされ、データ量の変動が大きく、ANALYZEによる統計情報の更新が間にあわないことが多い。そこで、統計情報を固定化してレスポンスを安定化させる。

なお、今回はPostgreSQL 11.1とpg_dbms_stats 1.3.11を組み合わせた環境での検証例とします。

2.1 準備する

pg_dbms_statsを利用するには、追加モジュールを公開サイトなどから取得してインストールしたあと、以下の準備が必要です。

1. PostgreSQLを起動して本機能を利用するデータベースに対して、CREATE EXTENSIONを実施します。なお、対象のデータベースは「mydb」とします。

$ psql -d mydb -c "CREATE EXTENSION pg_dbms_stats;"

1. postgresql.confファイルのshared_preload_librariesパラメーターに｢pg_dbms_stats｣を追加します。

shared_preload_libraries = 'pg_dbms_stats'

1. PostgreSQLを再起動します。

2.2 統計情報をバックアップする

インデックスのあるテーブルの検索処理に対して、速く安定したレスポンスが得られていたときの統計情報をバックアップします。なお、empテーブルは作成されているものとします。

1. empテーブルの構成とデータ件数を確認します。

   

   • （1）empテーブルの列「empno」に「emp_pkey」というインデックス（主キー）が設定されています。
   • （2）empテーブルの列「age」に「emp_age_index」というインデックスが設定されています。

2. ANALYZEコマンド（SQLコマンド）で統計情報を更新し、対象SQLの実行計画を確認します。

   

   • （1）empテーブルに対して「Bitmap Scan」が選択されたことがわかります。
     • 注意
       バックアップの前に、必ず一度はANALYZEコマンド（SQLコマンド）で統計情報を取得してください。統計情報が存在しない状態でバックアップやロックを実行した場合、実行計画は制御できません。

3. 検証した結果、実行計画に「Bitmap Scan」が選択されたときのレスポンスが速く安定していたので、この統計情報をバックアップします。ここではデータベース単位にバックアップしたいので、dbms_stats.backup_database_stats()関数を指定し、パラメーターにはコメントとして「Bitmap Scan for emp」を指定します。

   

4. 現在保存されているバックアップ情報は、dbms_stats.backup_historyテーブルで参照できます。dbms_stats.backup_historyテーブルはpg_dbms_statsの導入時に生成されるテーブルで、バックアップIDやバックアップ時のタイムスタンプなどの履歴を管理しています。統計情報のバックアップ一覧を表示し、バックアップID（列名はid）を確認します。

   

   • （1）バックアップIDは「1」です。バックアップの取得時間順に1から採番されます。
   • （2）「d」はバックアップ単位がデータベースであることを意味します。

ポイント

バックアップは、以下のオブジェクト単位で指定することができます。

• dbms_stats.backup_database_stats()
• dbms_stats.backup_schema_stats()
• dbms_stats.backup_table_stats()
• dbms_stats.backup_column_stats()

dbms_stats_backup_historyテーブルで情報を一覧表示したとき、「unit」には各オブジェクトの頭文字が表示されます。

2.3 リストア機能を使って統計情報を固定化する

バックアップした統計情報をリストア機能を使って固定化します。

1. 初めに、実行計画が不安定になる状況を作ってみます。
   empテーブルのデータから500件削除します。ANALYZEコマンド（SQLコマンド）で統計情報を更新すると、empテーブルに対して「Seq Scan」が選択されます。続けてempテーブルのデータに2000件追加します。統計情報を更新せずに対象SQLの実行計画を確認します。ここではデータを大量追加した直後にデータ検索要求があり、ANALYZEコマンド（SQLコマンド）による統計情報の更新が間にあわなかったことを想定しています。

   

   • （1）empテーブルに対して「Seq Scan」が選択されたことがわかります。
   • （2）統計情報が更新されていないため、empテーブルに対する推測値のaと実測値のbは大きく異なります。

2. 「2.2 統計情報をバックアップする」でバックアップした「Bitmap Scan」の統計情報が選択されるように、リストア機能で復元して固定します。
   バックアップした統計情報（Bitmap Scan）をバックアップIDを指定してリストアするには、dbms_stats.restore_stats()関数を指定します。パラメーターにはbackup_historyテーブルのid「1」を指定します。

   

   ポイント

   リストア機能には、以下の2種類の方法があります。用途に応じて使い分けてください。

   • バックアップIDを指定する方法
     • dbms_stats.restore_stats()
   • タイムスタンプを指定する方法
     • dbms_stats.restore_database_stats()
     • dbms_stats.restore_schema_stats()
     • dbms_stats.restore_table_stats()
     • dbms_stats.restore_column_stats()

3. 対象SQLの実行計画を確認します。

   

   • （1）empテーブルに対して「Bitmap Scan」が選択されたことがわかります。
   • （2）empテーブルに対する推測値のaと実測値のbは大きく異なります。aは「2.2 統計情報をバックアップする」の手順2.で「Bitmap Scan」を採用したときに参照した統計情報を元に推定された値のためです。なお、ここでANALYZEコマンド（SQLコマンド）を実行してもaは更新されません。
   • （3）手順1.と比べて、実行時間が半分近く短縮されたことがわかります。

このように、統計情報を固定化することで、実行計画の変化を避けて安定したレスポンスを実現できます。

2.4 統計情報の固定化を解除する

pg_dbms_stats独自の統計情報を、PostgreSQL本来の統計情報に戻す場合は、ロック解除機能を使います。

1. 固定を解除するには、dbms_stats.unlock_database_stats()関数を指定します。

   

2. VACUUM ANALYZEコマンド（SQLコマンド）を実施したあと、対象SQLの実行計画を確認します。

   

   • （1）「2.3 リストア機能を使って統計情報を固定化する」の手順1.では「Seq Scan」でしたが、クエリ実行前に統計情報が更新されていると、「Bitmasp Scan」が選択されます。
   • （2）empテーブルに対する推測値のaと実測値のbが一致しており、統計情報が更新され、統計情報の固定化が解除されていることがわかります。

参考

FUJITSU Software Enterprise Postgresでは、バージョン 9.5から、pg_dbms_statsを同梱しています。製品のインストール時にpg_dbms_statsをプリインストールしているので、追加モジュールを取得する必要はありません。

3. pg_dbms_statsの注意点

pg_dbms_statsを使うことで、間接的に実行計画を制御できますが、以下のような注意点があります。

• テーブルのデータサイズの変更による影響
  統計情報を固定化すると、データのサイズや偏りなどデータの特性が変わった場合でも、統計情報が固定化されたままです。必要に応じて、統計情報の固定化を見直す必要があります。
• オブジェクト削除時の注意
  テーブルや列などのオブジェクトが不要になった場合は、pg_dbms_statsのロック解除機能やパージ機能を使ってpg_dbms_stats独自の統計情報を先に削除してください。オブジェクトを先に削除した場合は、pg_dbms_statsのクリーンアップ機能を使ってpg_dbms_stats独自の統計情報を削除してください。

性能チューニングの1つの手段として、pg_dbms_statsの機能・用途を理解して、ご利用のシステムや業務要件に適した方法を選択してください。

2019年8月23日公開

こちらもおすすめ

• チューニング ～ データベースチューニング ～
• チューニング ～ SQLチューニングの概要 ～
• チューニング ～ SQLチューニングを実施する ～
• pg_hint_planで実行計画を制御する

本コンテンツに関するお問い合わせ