★PostgreSQLカンファレンス2024 12月6日開催/チケット販売中★
他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

14.2. プランナで使用される統計情報

14.2.1. 単一列統計情報

前節で説明した通り、問い合わせプランナは、より良い問い合わせ計画を選択するために問い合わせによって取り出される行数の推定値を必要としています。 本節では、システムがこの推定に使用する統計情報について簡単に説明します。

統計情報の1つの構成要素は、各テーブルとインデックスの項目の総数と、各テーブルとインデックスが占めるディスクブロック数です。 この情報はpg_classreltuplesrelpages列に保持されます。 以下のような問い合わせによりこれを参照することができます。

SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE '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 rows)

ここで、tenk1とそのインデックスには10000行が存在し、そして、(驚くには値しませんが)インデックスはテーブルよりもかなり小さなものであることがわかります。

効率を上げるため、reltuplesrelpagesは処理の度には更新されず、したがって通常は多少古い値のみ所有しています。 これらはVACUUMANALYZECREATE INDEXなどの一部のDDLコマンドによって更新されます。 テーブル全体をスキャンしないVACUUMANALYZE操作(一般的な状況です)は、スキャンされたテーブルの部分に基づいてreltuples値を漸次更新し、概算値を生成します。 いずれの場合でもプランナは、現在の物理的なテーブルサイズに合わせるためにpg_classから検索した値を調整して、より高精度な近似値を得ます。

ほとんどの問い合わせは、検証される行を制限するWHERE句によって、テーブル内の行の一部のみを取り出します。 したがって、プランナはWHERE句の選択性、つまりWHERE句の各条件にどれだけの行が一致するかを推定する必要があります。 この処理に使用される情報はpg_statisticシステムカタログ内に格納されます。 pg_statistic内の項目は、ANALYZEVACUUM ANALYZEコマンドによって更新され、また1から更新がかかったとしても常に概算値になります。

統計情報を手作業で確認する場合、pg_statisticを直接参照するのではなく、pg_statsビューを参照する方が良いでしょう。 pg_statsはより読みやすくなるように設計されています。 さらに、pg_statsは誰でも読み取ることができますが、pg_statisticはスーパーユーザのみ読み取ることができます。 (これは、非特権ユーザが統計情報から他人のテーブルの内容に関わる事項を読み取ることを防止します。 pg_statsビューは現在のユーザが読み取ることができるテーブルに関する行のみを表示するよう制限されています。) 例えば、以下を行うことができます。

SELECT attname, inherited, n_distinct,
       array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';

 attname | inherited | n_distinct |          most_common_vals
---------+-----------+------------+------------------------------------
 name    | f         |  -0.363388 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp
 name    | t         |  -0.284859 | I- 880                        Ramp+
         |           |            | I- 580                        Ramp+
         |           |            | I- 680                        Ramp+
         |           |            | I- 580                            +
         |           |            | State Hwy 13                  Ramp
(2 rows)

同じ列に対して2行が表示されていることに注意してください。 1つはroadテーブルが始まる継承階層(inherited=t)全体に相当し、もう1つはroadテーブル自身(inherited=f)のみを含むものです。

ANALYZEによりpg_statisticに格納される情報量、具体的には、それぞれの列に対するmost_common_vals内とhistogram_bounds配列のエントリの最大数は、ALTER TABLE SET STATISTICSコマンドによって列ごとに、default_statistics_target設定パラメータを設定することによってグローバルに設定することができます。 現在のデフォルトの上限は100エントリです。 この上限を上げることで、特に、少し変わったデータ分布を持つ列でより正確なプランナの推定が行われますが、pg_statisticにより多くの容量が必要になり、多少推定計算にかかる時間が多くなります。 反対に上限を下げることは、単純なデータ分布の列に対して順当です。

プランナによる統計情報の使用に関する詳細については第70章を参照してください。

14.2.2. 拡張統計情報

問い合わせ句で使われている複数列に相関性があることにより、悪い実行計画を実行する遅いクエリがしばしば観察されます。 プランナは通常複数の条件がお互いに独立であるとみなしますが、列の値に相関性がある場合はそれは成り立ちません。 通常の列ごとの統計情報は、それが個々の列ごとであるという性質上、列をまたがる相関性に関する知識を把握することはできません。 しかしながら、PostgreSQLは、多変量統計情報を計算することができ、それによってそうした情報を把握することができます。

列の組み合わせの数は非常に大きいため、自動的に多変量統計情報を計算するのは現実的ではありません。 代わりに、サーバが興味のある列の集合にまたがる統計情報を得るように指示する目的で、拡張統計情報オブジェクト(しばしば単に統計情報オブジェクトと呼ばれます)を作成することができます。

統計情報オブジェクトはCREATE STATISTICSで作成します。 そうしたオブジェクトを作っても、単に統計情報として興味があることを示すカタログエントリが作られるだけです。 実際のデータ収集は、ANALYZE (手動のコマンドを起動あるいはバックグラウンドでの自動ANALYZE)が行います。 収集したデータは、pg_statistic_ext_dataカタログで参照することができます。

通常の単一列統計情報の計算に使うのと同じテーブル行のサンプルに基づき、ANALYZEは、拡張統計情報を計算します。 (前節で述べたように)テーブルあるいはそのテーブルの対象となる列統計情報の増やすと、サンプルのサイズも増えるので、より大きな統計情報の対象を使うと、通常、より精度の高い拡張統計情報を得られますが、計算に費やす時間も増えます。

次の節では、現在サポートしている拡張統計情報の種類を説明します。

14.2.2.1. 関数従属性

もっとも単純な拡張統計情報は、データベースの正規形の定義で使われる考え方である、関数従属性を追跡します。 aの値に関する知識がbの値を決定するのに十分であるとき、列bは列aに関数的に従属していると言います。 これはすなわち、同じaの値を持ちながら、異なるbの値を持つ二つの行は存在しないということです。 完全に正規化されたデータベースでは、関数従属性は主キーと超キーにのみ存在します。 実際には様々な理由でデータの集合は完全には正規化されません。 性能上の理由により非正規化するというのが典型的な例です。 完全に正規化されたデータベースにおいても、ある列の間に部分的な相関関係が存在することがあり、これは部分的関数従属性として表現されます。

ある問い合わせでは、関数従属性が存在することが見積もりの精度に直接影響を与えます。 問い合わせに独立した列と依存する列の両方に関する条件が含まれていると、依存する列に関する条件はそれ以上結果サイズを小さくしません。 しかし関数従属性に関する知識がなければ、クエリプランナはそれらの条件が独立であると見なし、結果サイズの過少見積もりすることになります。

プランナに関数従属性について知らせるために、ANALYZEは列をまたがる依存性の強さを収集することができます。 すべての列の集合間の依存性度合いを調査するのは、受け入れられないほど高価になります。 そこでデータ収集は、dependenciesオプションで定義された統計情報オブジェクトの中に一緒に現れた列のグループに制限されます。 ANALYZEおよび後々のクエリプランニングにおける不必要なオーバーヘッドを避けるために、強い相関関係のある列のグループのみを対象に、dependencies統計情報を作成することをお勧めします。

関数従属性統計情報の収集例です。

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxname, stxkeys, stxddependencies
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts';
 stxname | stxkeys |             stxddependencies             
---------+---------+------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)

ここでは、列1(zip code)が完全に列5(city)を決定しているので、係数は1.0です。 一方、cityはzip codeを42%しか決定していないので、一つ以上のzip codeで表現されている多くのcity(58%)が存在するということになります。

関数従属する列を伴うクエリの選択率を計算する際、過小評価を行わないように、プランナは依存性係数を使って条件ごとの選択率を調整します。

14.2.2.1.1. 関数従属性の制限事項

今のところ、列と定数を比較する単純な等価条件と、定数のIN句を考慮する際にしか関数従属性は適用されません。 二つの列を比較する、あるいは列を式と比較する等価条件、範囲句、LIKEその他の条件の見積もりを改善するのには使われません。

関数従属性を含めた見積もりでは、プランナは関係する列に対する複数の条件が同時に成り立つ、つまり冗長であるとみなします。 それらの条件が同時に成り立たなければ、正しい見積もりは0行となりますが、その可能性は考慮されません。 たとえば次のクエリを見てください。

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';

プランナは、選択率が変わらないという正しい推定に基づきcity句を無視します。 しかし、これを満たす行が0行であるにもかかわらず、次の問い合わせでも同じ推測をします。

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';

関数従属性統計情報は、これを結論付けるだけの十分な情報を提供しません。

多くの実用的な場合には、この前提は通常満たされます。 たとえば、あるアプリケーションには、クエリの中で両立するcityとZIP codeだけを許すGUIが備わっているかもしれません。 もしそうでなければ、関数従属性は実行可能なオプションではないかもしれません。

14.2.2.2. 多変量N個別値計数

単一列統計情報は、それぞれの列で異なる値の数を保持します。 たとえば、GROUP BY a, bのように、二つ以上の列を組み合わせての異なる値の数の見積もりは、プランナに単一列の統計情報だけしか与えられない場合は、しばしば間違ったものになり、プランナは悪いプランの選択をしてしまいます。

見積もり改善のために、列のグループに対してANALYZEはN個別統計情報を収集することができます。 以前述べたのと同様に、可能なすべての列のグループに対してこれを行なうのは現実的ではありません。 ndistinctオプションで定義された統計オブジェクト中に一緒に現れる列のグループに対してのみデータを 収集します。 列リストの中から、可能な二つ以上の列の組み合わせそれぞれに対してデータが収集されます。

先ほどの例の続きで、ZIP codeのテーブルのN個別値計数は次のようになります。

CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxkeys AS k, stxdndistinct AS nd
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts2';
-[ RECORD 1 ]------------------------------------------------------​--
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)

この例では、33178の異なる値を持つ列の組み合わせが三つあることを示しています。 ZIP codeとstate、ZIP codeとcity、cityとstateです。(これらが等しいという事実は、ZIP codeだけがテーブル中でユニークであることから期待されます。) 一方、cityとstateの組み合わせには、27435だけの異なる値があります。

グループ化で実際に使用する列の組み合わせで、かつグループ数の見積もり間違いによって悪いプランをもたらすものに対してだけ、ndistinct統計情報オブジェクトを作ることをお勧めします。 さもないと、ANALYZEサイクルは単に無駄になります。

14.2.2.3. 多変量MCVリスト

列ごとに格納される別なタイプの統計情報は最頻値リスト(most-common value list)です。 個々の列ごとには非常に正確な推測を可能にしますが、複数列に渡る条件を持つ問い合わせについては重大な誤った推定をもたらすことがあります。

こうした推定を改善するために、列の組み合わせのMCVリストをANALYZEで収集することができます。 関数従属性とN個別値係数同様、考えられるすべての列のグループに対してこれを行うのは実用的ではありません。 MCVリストでは(関数従属性とN個別値係数と違って)列の頻値を格納するのでなおさらです。 ですからmcvオプションで定義された統計情報オブジェクト中に共通して現れる列のグループのデータだけが収集されます。

前述の例を続けましょう。ZIPコードのテーブルのMCVリストは次のようになるでしょう。(単純な形式の統計情報とは違って、MCVの内容を解析する関数が必要になります)

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

ANALYZE zipcodes;

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

 index |         values         | nulls | frequency | base_frequency 
-------+------------------------+-------+-----------+----------------
     0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05
     1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05
     2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133
     3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113
     4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114
     5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05
     6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05
     7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05
     8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05
     9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05
   ...
(99 rows)

これによると市と州のもっとも頻度の高い組み合わせはDCのWashingtonで、(サンプルにおける)実際の頻度は約0.35%でした。 比較の基準となる組み合わせの頻度(単純な列ごとの頻度から計算されたもの)はたった0.0027%で、2桁の過少見積になっています。

そのグループの誤推定値が間違った計画をもたらしてしまうような、条件の中で実際に一緒に使われる列の組み合わせについてのみMCV統計情報オブジェクトを作成することが望ましいです。 さもないと、ANALYZEとプラン処理は単に無駄になってしまいます。