default_statistics_targetの7001～7500の間には何かあると思う件

PostgreSQLではデフォルトのサンプリング数は3万(default_statistics_targetのデフォルトが100でそれに300を掛けた値)で、30万件以上のテーブルでは10％以下のサンプリングレートとなります。例えば100万件のテーブルでは3%, 1000万件のテーブルでは0.3%です

(なおここでは単純なサンプリング件数を書いていますが、ヒストグラムを格納するビンという概念もあり、default_statistics_targetを上げることでビンの数も増えるので、実際には件数から算出したサンプリングレートのみでなくビンの影響も考慮した方が良いとなります)

過去の状態や履歴を蓄積するテーブルでは1000万件を超えるテーブルも珍しくはないので、サンプリングレートの低さは気になります

(なお商用のRDBを調べてみると基本10%、巨大テーブルでは独自アルゴリズムによって少ないサンプリング数で全体の10%を実現、などという記述がありました)

サンプリングレートが低いことで、統計情報が実際のデータ分布と乖離し、実行計画が狂いやすく(インデックス使ってくれないなど)なると想定されます

PostgreSQLではサンプリングレートを上げる場合、以下方法があります

• DB全体: default_statistics_targetパラメータの値を上げる
• カラム単位: カラムに対してALTER COLUMN ... SET STATISTICSで値を設定する
  • カラム間のデータ分布の相関を表現する拡張統計という方法もある

いずれも一丁一旦ありますが、今回はDB全体: default_statistics_targetパラメータの値を上げる方を検証しました。

default_statistics_targetを上げると統計情報のサンプリング数は増えサンプリングレートは上がるのですが、一方ANALYZEにより時間がかかるようになるというデメリットがあります。

なおANALYZEのロックモードはSHARE UPDATE EXCLUSIVEです。超ざっくりは以下です

• SELECT/DML(INSERT/DELETE/UPDATE)とは競合しない (同時実行可能)
• DDL(CREATE/ALTER/DROP/TRUNCATE等)とは競合する (後から実行された方はロック待ちになる)

ANALYZEにより時間がかかるようになって大丈夫かの影響調査は、上記のロックモードを元にアプリケーション側で想定することとなります。

default_statistics_targetの値を上げるとANALYZEの時間がどのくらい増えるかを検証するため、まずはmaxのdefault_statistics_target＝10000でANALYZEを実行し、件数/バイト数/カラム数の多いテーブルほど顕著に時間が増えることを確認しました。

(default_statistics_target=10000では、サンプリング数は300万件となり、1000万件のテーブルではサンプリングレートは30%となります)

で、ここから本題なのですが、上記検証の際、特定のテーブルで何時間待てども応答が返ってこない状態となりました。

切り分けでdefault_statistics_targetの値を調整してみたり、件数/バイト数/カラム数の影響を調査したところ、以下の結論となりました

default_statistics_target=7000ではANALYZEは時間はかかって正常終了するが、default_statistics_target=7500ではANALYZEは終わらない

上記結論は、default_statistics_target=10000でANALYZEが終わらないテーブルに対し、default_statistics_targetの値を切り分けて得た結論です。7250などさらに切り分けることは可能ですが、7000で時間はかかって正常終了するが7500以上では終わらない、というところまで確認して検証を切り上げました。

7500でANALYZEが終わらないテーブルは、それなりに件数/バイト数/カラム数が多いですが、テーブル定義が異なる、より件数/バイト数/カラム数が多いテーブルで必ず発生するわけではないため、傾向以上のものを確認できてはいません。
また、素人考えで、7000でANALYZEが正常終了するテーブルも、件数が倍々になったら終わらなくなるのでは??と思い、7500でANALYZEが終わらないテーブルを数億件/数百GBといった極端な件数/バイト数にして検証しましたが、7000では正常終了で7500で終わらない、という結果は同じでした。

今回はサンプリングレートを上げること、その際のANALYZE時間の増加具合を把握することを目的にdefault_statistics_targetをmaxの10000など大きな値に設定して検証しましたが、当初予想としては値を上げるほどサンプリングレートは上がりANALYZEに時間がかかると想定していたものの、default_statistics_targetを一定の値以上にするとまったくANALYZEが終わらない状態になる、という想定外の結果を得たため、あえてその点を強調した記事としました。

補足

• default_statistics_targetを1000ずつ上げていくと、ANLYZE時間は比例的に増加する (完全な比例ではないがある程度相関する)
• default_statistics_target=7500以上でANLYZE実行時、序盤はストレージIOリードが支配的だが、中盤以降はストレージIOリードが0になり、CPUが支配的になる (ただしずっと1スレッドでANLYZEを実行中のように見える、という形で、サーバはマルチコアのため全体のCPU使用率は低い)
• 特定のPostgreSQL verではなく、複数のverで発生を確認
• 7500でANALYZEが終わらないテーブルの定義に特別なものは見受けられない (テーブル定義、データ型、インデックス数など)
• 件数/バイト数/カラム数が多いテーブルで起こる傾向がある
• 件数/バイト数/カラム数が少ないテーブルでは発生を確認できないので、件数/バイト数/カラム数を減少していくと閾値見つかるのかもしれない (そこまでやってない)
• default_statistics_target=10000の場合、特定の複数のテーブルで、最大で9h待って終わらず強制終了。ちな100だと数分で、7000だと40分程度

その他

web上でdefault_statistics_targetのチューニング事例を調べましたが、あまり良い事例は見つかりませんでした。値を上げるとANALYZEに時間がかかるよとか、開発環境では10000にしてみました！とか商用で一旦1000に上げてみた、的な内容は見つかったのですが、どの程度の影響があったか、実行計画が改善したりANALYZEの時間がどうなったなどの具体的な事例や情報はありませんでした。公式/SIer/PGConあたりの資料で「統計情報の精度をxxxx程度に維持するのが推奨」「xxxxのデータ量ではパラメータの値はxxxx」のような分かりやすい指標があると嬉しいのですが..

追記

良い情報もありました

デフォルトの統計情報計算式はテーブルのレコード数を 1,000,000 レコードと想定。
10,000,000 レコードのテーブルでは STATISTICS = 114 程度にすると必要なサンプリングが
行われる

でもこっちと書いてあること違うような..

デフォルトの統計ターゲットは 100 ですが、Paul が指摘しているように、これは実際には 30,000 行のサンプルを収集することを意味します。

上記サイトにはなんでサンプリングレートがdefault_statistics_target x 「300」なのかの理由が書いてあって個人的にLGTM

Postgres のソースコードをざっと見て、この魔法の数字 300 がどこから来たのかを理解してみることにしました。Postgresのソースにアクセスしてanalyze.c ファイルを見てみると、Postgres のソースドキュメントでこの説明が、ヒストグラム作成のためのランダムサンプリングに関する 1998 年の論文に基づいていることがわかります。その考え方は、100 個の値とそのヒストグラム（頻度など）を探すというものです。そして、実際に代表的なサンプルを得るために、それよりも大きな特定の数値を調べる必要があります。その論文では最終的に 305 という値が導き出され、Postgres ではそれを 300 に簡略化しています。