皆さん、こんにちは。がーしらです。
PostgreSQLを使ってシステム運用してますが、「データ量が増えると急にインデックスが効かなくなった」、「analyzeすると遅くなった」、「pg_repackすると遅くなった」なんてことはありませんか。
今回はそんな時に役立つPostgreSQLのチューニング（SQLその２～統計情報編～）です。PostgreSQLの機嫌を伺いつつこちらの意図通りに結果を取得する方法を１つご紹介します。ご参考にどうぞ。
インデックスが効かない
テスト段階では正常に動いていたはずなのに、データ量が増えてくると遅いクエリが発生する。
しかもSQLはほとんど一緒でも条件によって速度が変わる、またはデータ量が少ないのに遅いなどの問題に直面したことはありませんか？
そのようなときにSQLの実行計画(explain analyze)を取得してみるとなんだか想定しているインデックスが効いてないなぁという状態。
これ、PostgreSQLの機嫌が悪くなってます。気晴らしにAnalyzeを実行するとたまに機嫌も直りますが、それでもダメな時ももちろんあります。そんな時にPostgreSQLの機嫌をよくする方法を一つご紹介します。
統計情報を確認する
PostgreSQLの機嫌が悪い数ある理由の１つに統計情報が足りておらず、オプティマイザが適当にインデックスを選定しているという場合があります。PostgreSQLからすると「ちっ、材料足りてないんだよ」とキレ散らかしている状態ですね。
こんな時にこちらもキレるともっとひどいことになるのでまずは統計情報を確認してみましょう。
遅いSQLの条件を指定しているテーブル×カラムに対して以下のSQLにて統計情報を取得してみてください。
-- テーブルの列ごとの統計情報を取得
select * from pg_stats where tablename = '{テーブル名}' and attname = '{カラム名}';取得結果のカラムにあるmost_common_valsに着目し、値に今回取得条件に指定した値があるかどうかを確認しましょう。
もし仮に条件にあった場合は他の条件に指定しているカラムも確認してみてください。すべての条件値がそれぞれの統計情報のmost_common_valsに含まれている場合は「がぁっでぇむっ！！！🕶」といってこのページは閉じてもらって結構です。あまり参考にならないかと。
サンプリング数を変更する
most_common_valsに値がない方は、統計情報の取得量とテーブルにおけるデータの量が合っていない可能性があります。統計情報を取得する際のサンプリング数を増やしてみましょう。
通常、PostgreSQLは統計情報を取得する際は設定にある
300 × default_statistics_target（100）
の式で出された30000データを統計情報としてランダムでサンプリングします。
しかしデータ量の分布によってはサンプリング数が足りない可能性が出てきます。テーブル構成の見直しやパーティショニングでも改善は期待できますがそこまでパワーがかけられない場合はサンプリング数を増やしましょう。
default_statistics_targetを変更する方法もありますが全ての統計情報取得に影響が出て地獄を見ることになりますので運用途中なども含めてお勧めしません。
以下のSQLを用いれば対象のテーブルの列単位でサンプリング数を増やすことができます。
-- 対象テーブル.カラムのサンプリング数変更
alter table {テーブル名} alter column {カラム名} set STATISTICS {サンプリング閾値};設定を変更すると300 × サンプリング数の閾値に指定した値にてサンプリングがされるようになるためより細かく統計情報が作られ、PostgreSQL（オプティマイザが）が舌打ちせずにより精密にインデックスの選定を行ってくれ機嫌がよくなるという仕組みです。
なお、上記設定を変更した後は必ずAnalyzeを実施するのを忘れずにしてください。設定変更しただけでは自動Analyzeがかかるまでいつまでたっても統計情報は更新されませんのでご注意下さい。
ちなみにですが、STATISTICSで設定するサンプリング数の閾値については該当テーブルのカラムにおけるデータ内の最小数と最大数を元に導き出すのが良さそうです。
設定変更におけるデメリット
しかしメリットだけのうまい話しは世の中存在しません。トレードオフとして、サンプリング数を増やすのでAnalyzeの実行時間に時間がかかるようになります。
また、サンプリングの性質上何もしなくてもAnalyzeだけで改善されることがあります。その場合、恒久対応をしないと何もしなくても悪化することもあるのでご注意下さい。
いかがでしたでしょうか。上記でPostgreSQLの機嫌をよくすることができる方法を１つご紹介しました。
PostgreSQLは「今年一番じゃなかろうか」というぐらい機嫌が悪い場合もあります。そんな時でも根気強く探っていけば必ずそうなった原因はあると思いますので同志のみなさん、お互いに頑張りましょう！どなたかの参考になって快適なPostgreライフを過ごしていただければと思います。
今回参考にさせていただいた記事
https://thinkit.co.jp/free/marugoto/2/1/16/1.html