プランナーが使用する統計 - PolarDB - Alibaba Cloud ドキュメントセンター

単一列の統計

前のセクションで見たように、クエリプランナは、クエリプランを適切に選択するために、クエリによって取得される行の数を推定する必要があります。このセクションでは、システムがこれらの推定に使用する統計について簡単に説明します。

統計の1つの構成要素は、各テーブルおよびインデックス内のエントリの総数、ならびに各テーブルおよびインデックスによって占有されるディスクブロックの数である。この情報は、テーブルpg_class、列reltuplesおよびrelpagesに保持されます。これに似たクエリでそれを見ることができます:

SELECT relname、relkind、reltuples、relpages
pg_classから
どこrelname好き 'tenk1% ';

           relname | relkind | reltuples | relpages
    ----------------------+---------+-----------+----------
     tenk1 | r | 10000 | 358
     tenk1_hundred | i | 10000 | 30
     tenk1_thous_tenthous | i | 10000 | 30
     tenk1_unique1 | i | 10000 | 30
     tenk1_unique2 | i | 10000 | 30
    (5行)

ここでは、tenk1にはインデックスと同様に10000行が含まれていますが、インデックスは (当然のことながら) テーブルよりもはるかに小さいことがわかります。

効率上の理由から、reltuplesおよびrelpagesはオンザフライで更新されず、したがって、それらは通常、幾分古い値を含む。これらは、VACUUM、ANALYZE、およびCREATE INDEXなどのいくつかのDDLコマンドによって更新されます。テーブル全体をスキャンしないVACUUMまたはANALYZE操作 (これは一般的な場合です) は、スキャンしたテーブルの部分に基づいて関連タプルカウントを増分更新し、その結果、近似値が得られます。いずれの場合も、プランナーは、現在の物理テーブルサイズに一致するようにpg_classで見つけた値をスケーリングし、より近い近似値を取得します。

ほとんどのクエリは、調べる行を制限するWHERE句のため、テーブル内の行の一部のみを取得します。したがって、プランナーは、WHERE句の選択性、つまりWHERE句の各条件に一致する行の割合を推定する必要があります。このタスクに使用される情報は、pg_statisticシステムカタログに保存されます。 pg_statisticのエントリは、ANALYZEコマンドとVACUUM ANALYZEコマンドによって更新され、新しく更新されても常に近似値になります。

統計を手動で調べるときは、pg_statisticを直接見るよりも、そのビューpg_statsを見る方が良いでしょう。pg_statsは、より読みやすいように設計されています。さらに、pg_statsはすべての人が読み取り可能ですが、pg_statisticはスーパーユーザーのみが読み取り可能です。 (これにより、特権のないユーザーが統計から他のユーザーのテーブルの内容について何かを学ぶことができなくなります。 pg_statsビューは、現在のユーザーが読むことができるテーブルに関する行のみを表示するように制限されています。たとえば、次のようにします。

SELECT attname, 継承, n_distinct,
           array_to_string(most_common_vals, E'\n') as most_common_vals
    pg_statsから
    どこtablename = 'road';

     attname | 継承 | n_distinct | most_common_vals
    ---------+-----------+------------+------------------------------------
     名前 | f | -0.363388 | I- 580ランプ +
             | | | I- 880ランプ +
             | | | Sp Railroad +
             | | | I- 580 +
             | | | I- 680ランプ
     name | t | -0.284859 | I- 880ランプ +
             | | | I- 580ランプ +
             | | | I- 680ランプ +
             | | | I- 580 +
             | | | 州ハイウェイ13ランプ
    (2行)

同じ列には2つの行が表示されます。1つは道路テーブルから始まる完全な継承階層に対応し (継承=t) 、もう1つは道路テーブル自体のみを含む (継承=f) 。

ANALYZEによってpg_statisticに格納される情報の量、特に各列のmost_common_valsおよびhistogram_bounds配列の最大エントリ数は、ALTER TABLE set STATISTICSコマンドを使用して列ごとに、またはdefault_statistics_target設定によってグローバルに設定できます。デフォルトの制限は現在100エントリです。制限を上げると、特に不規則なデータ分布を持つ列の場合、pg_statisticでより多くのスペースを消費し、推定値を計算するのにわずかに時間がかかるという代償を払って、より正確なプランナー推定を行うことができます。逆に、単純なデータ分布の列には下限で十分な場合があります。

拡張統計

クエリ句で使用される複数の列が相関しているため、低速クエリで悪い実行計画が実行されるのが一般的です。プランナは通常、複数の条件が互いに独立していると仮定し、列の値が相関している場合には成り立たないという仮定を仮定する。通常の統計は、個々の列ごとの性質のために、クロスカラム相関に関する知識を取り込むことができません。ただし、PolarDB PostgreSQLには、そのような情報を取得できる多変量統計を計算する機能があります。

可能な列の組み合わせの数は非常に多いため、多変量統計を自動的に計算することは実用的ではありません。代わりに、単に統計オブジェクトと呼ばれることが多い拡張統計オブジェクトを作成して、関心のある列のセットにわたって統計を取得するようにサーバーに指示することができます。

統計オブジェクトは、CREATE Statisticsコマンドを使用して作成されます。このようなオブジェクトの作成は、単に統計への関心を表すカタログエントリを作成するだけである。実際のデータ収集は、ANALYZE (手動コマンドまたはバックグラウンド自動分析) によって実行されます。収集された値は、pg_statistic_ext_dataカタログで調べることができます。

ANALYZEは、通常の単一列統計の計算に必要なのと同じテーブル行のサンプルに基づいて拡張統計を計算します。サンプルサイズは、(前のセクションで説明したように) テーブルまたはその列のいずれかの統計ターゲットを増やすことによって大きくなるため、統計ターゲットが大きくなると、通常、統計の計算にかかる時間が長くなります。

次のサブセクションでは、現在サポートされている拡張統計の種類について説明します。

機能依存性

最も単純な種類の拡張統計は、データベースの通常形式の定義で使用される概念である関数の依存関係を追跡します。私たちはそのコラムを言うbは列に機能的に依存しますaの値の知識があればaの値を決定するのに十分です。bの同じ値を持つ2つの行はありません。aしかし、異なる値のb. 完全正規化データベースでは、関数依存関係はプライマリキーとスーパーキーにのみ存在する必要があります。ただし、実際には、多くのデータセットはさまざまな理由で完全に正規化されていません。パフォーマンス上の理由による意図的な非正規化は一般的な例です。完全に正規化されたデータベースであっても、いくつかの列の間に部分的な相関がある場合があり、これは部分関数依存として表すことができる。

関数依存性の存在は、特定のクエリの推定精度に直接影響します。クエリに独立列と従属列の両方の条件が含まれている場合、従属列の条件は結果サイズをさらに縮小しませんが、関数の依存関係の知識がなければ、クエリプランナーは条件が独立していると想定し、結果サイズを過小評価します。

関数依存性についてプランナーに通知するために、ANALYZEはクロスカラム依存性の測定値を収集できます。列のすべてのセット間の依存性の程度を評価することは非常に高価であるため、データ収集は、依存オプションで定義された統計オブジェクトに一緒に現れる列のグループに限定されます。 ANALYZEと後のクエリ計画の両方で不要なオーバーヘッドを回避するために、強い相関がある列グループに対してのみ依存関係統計を作成することをお勧めします。

関数依存関係の統計を収集する例を次に示します。

CREATE STATISTICS stts (依存関係) ON city, zip FROM zipcode;

    zipcodeを分析します。

    SELECT stxname、stxkeys、stxddependencies
      pg_statistic_extからpg_statistic_ext_dataを (oid = stxoid) に結合する
      WHERE stxname = 'stts';
     stxname | stxkeys | stxddependencies
    ---------+---------+------------------------------------------
     stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130}
    (1行)

ここでは、列1 (郵便番号) が列5 (都市) を完全に決定するので、係数は1.0であるが、都市は、時間の42% について郵便番号のみを決定し、複数の郵便番号によって表される多くの都市 (58%) があることを意味することが分かる。

機能的に依存する列を含むクエリの選択性を計算するとき、プランナーは、過小評価を生成しないように、依存係数を使用して条件ごとの選択性推定値を調整します。

機能依存性の制限

現在、関数の依存関係は、列を定数値と比較する単純な等しい条件と、定数値を持つIN句を考慮した場合にのみ適用されます。これらは、2つの列を比較したり、列を式と比較したりする等号条件、範囲句、LIKE、またはその他の種類の条件の推定値を改善するためには使用されません。

関数依存性で推定する場合、プランナーは、関連する列の条件が互換性があり、したがって冗長であると想定します。互換性がない場合、正しい推定値はゼロ行になりますが、その可能性は考慮されません。たとえば、次のようなクエリが与えられます。

SELECT * から郵便コードWHERE city = 'San Francisco' AND zip = '94105';

プランナーは、選択性を変更しないとして市の条項を無視します。これは正しいことです。しかし、それはについて同じ仮定をします

SELECT * から郵便コードWHERE city = 'San Francisco' AND zip = '90210';

このクエリを満たす行は実際にはゼロですが。ただし、機能依存関係の統計は、それを結論付けるのに十分な情報を提供しません。

多くの実際的な状況では、この仮定は通常満たされます。たとえば、アプリケーションには、クエリで使用する互換性のある都市およびZIPコード値の選択のみを許可するGUIがある場合があります。しかし、そうでない場合、関数の依存関係は実行可能なオプションではないかもしれません。

多変量N-異なるカウント

単一列の統計には、各列の異なる値の数が格納されます。複数の列 (たとえば、GROUP BY a、b) を結合するときの個別の値の数の推定値は、プランナに単一列の統計データしかない場合に間違っていることが多く、悪いプランが選択されます。

このような推定を改善するために、ANALYZEは列のグループについてn個の異なる統計を収集できます。前述のように、可能なすべての列グループに対してこれを行うことは実用的ではないため、ndistinctオプションで定義された統計オブジェクトに一緒に表示される列のグループに対してのみデータが収集されます。リストされた列のセットから2つ以上の列の可能な組み合わせごとにデータが収集されます。

前の例を続けると、郵便番号のテーブルのn個の異なるカウントは次のようになります。

CREATE STATISTICS stts2 (ndistinct) ON市、州、郵便番号からの郵便番号;

    zipcodeを分析します。

    SELECT stxkeys AS k, stxdndistinct AS nd
      pg_statistic_extからpg_statistic_ext_dataを (oid = stxoid) に結合する
      WHERE stxname = 'stts2';
    -[レコード1 ]------------------------------------------------------ --
    k | 1 2 5
    nd | {"1,2": 33178, "1,5": 33178, "2,5": 27435, "1,2, 5": 33178}
    (1行)

これは、33178に異なる値を持つ列の3つの組み合わせがあることを示しています。郵便番号と州、郵便番号と市、郵便番号、市、州です (郵便番号だけがこの表で一意である場合、それらがすべて等しいことが予想されます) 。一方、都市と州の組み合わせには、27435の明確な価値しかありません。

グループ化に実際に使用される列の組み合わせに対してのみndistinct統計オブジェクトを作成することをお勧めします。さもなければ、分析サイクルは無駄になる。

多変量MCVリスト

各列に格納される別のタイプの統計は、最も一般的な値リストです。これにより、個々の列の非常に正確な推定が可能になりますが、複数の列の条件を持つクエリの推定を大幅に誤る可能性があります。

このような推定を改善するために、ANALYZEは列の組み合わせでMCVリストを収集できます。関数依存関係やn個の異なる係数と同様に、可能なすべての列グループに対してこれを行うことは実用的ではありません。この場合、MCVリストは (関数依存性およびn個の別個の係数とは異なり) 共通の列値を格納する。したがって、データは、mcvオプションで定義された統計オブジェクトに一緒に表示される列のグループに対してのみ収集されます。

前の例を続けると、郵便番号のテーブルのMCVリストは次のようになります (より単純なタイプの統計とは異なり、MCVコンテンツの検査には関数が必要です) 。

CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcode;

    zipcodeを分析します。

    * pg_statistic_extからpg_statistic_ext_dataを (oid = stxoid) に結合し、
                    pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

     index | 値 | null | 周波数 | base_frequency
    -------+------------------------+-------+-----------+----------------
         0 | {ワシントンDC} | {f,f} | 0.003467 | 2.7e-05
         1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05
         2 | {ヒューストン, TX} | {f,f} | 0.002167 | 0.000133
         3 | {エルパソ, TX} | {f,f} | 0.002 | 0.000113
         4 | {ニューヨーク, NY} | {f,f} | 0.001967 | 0.000114
         5 | {アトランタ, GA} | {f,f} | 0.001633 | 3.3e-05
         6 | {サクラメント, CA} | {f,f} | 0.001433 | 7.8e-05
         7 | {マイアミ, FL} | {f,f} | 0.0014 | 6e-05
         8 | {テキサス州ダラス} | {f,f} | 0.001367 | 8.8e-05
         9 | {シカゴ, IL} | {f,f} | 0.001333 | 5.1e-05
       ...
    (99行)

これは、都市と州の最も一般的な組み合わせがワシントンDCであり、実際の頻度 (サンプル) が約0.35% であることを示しています。 (単純な列ごとの周波数から計算されるような) 組み合わせの基本周波数は0.0027% に過ぎず、2桁の大きさの過小推定値をもたらす。

MCVstatisticsオブジェクトは、実際に条件で一緒に使用される列の組み合わせでのみ作成することをお勧めします。グループの数を誤って推定すると、プランが不良になります。そうでなければ、分析と計画サイクルは無駄になります。