質問への回答
PostgreSQLはpg_statisticに列ごとのヒストグラムを維持し、複数列フィルターの選択性を推定する際に列間の統計的独立性を仮定します。相関の強い列（例えば、車両データベースのcityとstate_code、またはmakeとmodel）でフィルタリングすると、プランナーは個々の選択性を掛け算して実際の行数を大幅に過小評価します。この基数エラーは、オプティマイザーが大きなテーブルに対してNested Loop結合を好む原因となり、パフォーマンスに壊滅的な影響を与えます。
これを解決するには、CREATE STATISTICSを使用して拡張統計オブジェクトを作成します。これにより、多変量相関データが構築されます。具体的には、dependencies型は列間の関数依存関係を追跡し、プランナーがstate_code = 'CA'でフィルタリングすることがcityをカリフォルニアの値に制約することを認識できるようにし、掛け算エラーを回避します。
-- 相関する列のための拡張統計を作成 CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- 統計をポピュレート ANALYZE vehicles;
実生活の状況
ある物流プラットフォームは、50百万行のshipmentsテーブルをcustomersと結合した出荷追跡ダッシュボードに苦労しました。クエリはorigin_stateとorigin_cityでフィルタリングされましたが、'Springfield'という都市の95%の行は実際には'IL'にありましたが、プランナーはそれぞれの述語を独立に一致する出荷の2%しかないと仮定していました。彼は500行と推定し、Nested Loop結合を選択し、数百万の顧客レコードを反復して90秒後にタイムアウトしました。
考えられた修正の一つは、セッション内でSET enable_nestloop = offを使用してNested Loop結合を完全に無効にすることでした。これによりHash Joinが強制され、この特定のクエリは3秒で完了しましたが、深刻なリスクを伴いました：グローバルな設定変更は接続プール全体に伝播し、小さなテーブルでインデックスルックアップが良好に機能する他の正当なNested Loopプランが後退する可能性があります。さらに、この回避策は、クエリの前にパラメータを設定するためのアプリケーションレベルのコード変更を必要としました。
別のオプションは、(origin_city, origin_state)に対して複合インデックスを作成することでした。これによりインデックスの選択が改善されましたが、基数の過小評価は解決されず、プランナーは依然としてインデックススキャンから少数の行が出現すると考え、Nested Loop戦略を維持し、単にカバリングインデックスを介してそれをより速く実行しました。さらに、広範な複合インデックスは追加で4GBのディスクスペースを消費し、高速のshipmentsテーブルでの書き込み操作を遅くしました。
チームは最終的にCREATE STATISTICS stats_origin_correlationをorigin_city、origin_state FROM shipmentsを実行し、続いてANALYZEを実行することで拡張統計を導入しました。このアプローチはクエリのリライトを必要とせず、ほとんどストレージオーバーヘッドを追加しませんでした。展開後、プランナーは45,000行を正しく推定し、Hash Joinを選択し、クエリのレイテンシを400ミリ秒に短縮し、無関係なワークロードの最適なプランを維持しました。
候補者がよく見落とす点
拡張統計はどのようにANALYZEコマンドで更新され、なぜ統計オブジェクトは作成後すぐに未使用に見えるか？
ANALYZEは、ターゲットテーブルで明示的に呼び出されるか、統計オブジェクトが存在した後にオートバキュームがテーブルを処理する際にのみ拡張統計を計算します。多くの候補者はCREATE STATISTICSが計画に即座に影響を与えると仮定していますが、カタログテーブルpg_statistic_extとpg_statistic_ext_dataは次の分析サイクルまで空のままです。したがって、プランナーは、ANALYZE shipments;が多変量データをポピュレートするまで、単一列のヒストグラムと独立性の仮定を使用し続けます。非nullのdependenciesまたはndistinct値があるかどうかをpg_stats_extビューで確認することで使用を確認できます。
CREATE STATISTICSの中のdependenciesとndistinctの機能的違いは何か、どのクエリパターンがそれぞれに利益をもたらすか？
Dependenciesは、一つの列が別の列を決定する機能的関係を捉え（例えば、zip_codeがcityを決定する）、WHERE句の選択性推定を直接修正します。Ndistinctは列グループの異なる組み合わせの正確な数を計算し、フィルター選択性ではなくGROUP BYとDISTINCTの推定を改善します。候補者はこれらをしばしば混同し、相関のある列でのGROUP BYを含む遅いクエリに対してdependenciesを作成したり、その逆を行ったりします。最適な結果を得るためには、両方のタイプを指定します：CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...)
相関のある列にわたるOR条件を使用するクエリに対して拡張統計が役立たないのはなぜか？
拡張統計は、選択性の掛け算が発生するAND句のみをサポートします。OR（例えば、city = 'Springfield' OR state = 'IL'）でフィルタリングすると、PostgreSQLはP(A) + P(B) - P(A ∩ B)の式を用いて選択性を計算しますが、統計は結合に対する共同選択性を追跡するため、交差項に依存関係係数を適用することはできません。候補者はこの制限を見落とし、CREATE STATISTICSを使用してORに基づく基数エラーを修正しようとし、クエリのリライト（例えば、UNION ALLブランチに分割する）や部分インデックスが必要になります。