前節で説明した通り、問い合わせプランナは、より良い問い合わせ計画を選択するために問い合わせによって取り出される行数の推定値を必要としています。 本節では、システムがこの推定に使用する統計情報について簡単に説明します。
統計情報の1つの構成要素は、各テーブルとインデックスの項目の総数と、各テーブルとインデックスが占めるディスクブロック数です。
この情報はpg_class
のreltuples
とrelpages
列に保持されます。
以下のような問い合わせによりこれを参照することができます。
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行が存在し、そして、(驚くには値しませんが)インデックスはテーブルよりもかなり小さなものであることがわかります。
効率を上げるため、reltuples
とrelpages
は処理の度には更新されず、したがって通常は多少古い値のみ所有しています。
これらはVACUUM
、ANALYZE
、CREATE INDEX
などの一部のDDLコマンドによって更新されます。
テーブル全体をスキャンしないVACUUM
、ANALYZE
操作(一般的な状況です)は、スキャンされたテーブルの部分に基づいてreltuples
値を漸次更新し、概算値を生成します。
いずれの場合でもプランナは、現在の物理的なテーブルサイズに合わせるためにpg_class
から検索した値を調整して、より高精度な近似値を得ます。
ほとんどの問い合わせは、検証される行を制限するWHERE
句によって、テーブル内の行の一部のみを取り出します。
したがって、プランナはWHERE
句の選択性、つまりWHERE
句の各条件にどれだけの行が一致するかを推定する必要があります。
この処理に使用される情報はpg_statistic
システムカタログ内に格納されます。
pg_statistic
内の項目は、ANALYZE
とVACUUM 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
により多くの容量が必要になり、多少推定計算にかかる時間が多くなります。
反対に上限を下げることは、単純なデータ分布の列に対して順当です。
プランナによる統計情報の使用に関する詳細については第72章を参照してください。
問い合わせ句で使われている複数列に相関性があることにより、悪い実行計画を実行する遅いクエリがしばしば観察されます。 プランナは通常複数の条件がお互いに独立であるとみなしますが、列の値に相関性がある場合はそれは成り立ちません。 通常の列ごとの統計情報は、それが個々の列ごとであるという性質上、列をまたがる相関性に関する知識を把握することはできません。 しかしながら、PostgreSQLは、多変量統計情報を計算することができ、それによってそうした情報を把握することができます。
列の組み合わせの数は非常に大きいため、自動的に多変量統計情報を計算するのは現実的ではありません。 代わりに、サーバが興味のある列の集合にまたがる統計情報を得るように指示する目的で、拡張統計情報オブジェクト(しばしば単に統計情報オブジェクトと呼ばれます)を作成することができます。
統計情報オブジェクトはCREATE STATISTICS
で作成します。
そうしたオブジェクトを作っても、単に統計情報として興味があることを示すカタログエントリが作られるだけです。
実際のデータ収集は、ANALYZE
(手動のコマンドを起動あるいはバックグラウンドでの自動ANALYZE)が行います。
収集したデータは、pg_statistic_ext_data
カタログで参照することができます。
通常の単一列統計情報の計算に使うのと同じテーブル行のサンプルに基づき、ANALYZE
は、拡張統計情報を計算します。
(前節で述べたように)テーブルあるいはそのテーブルの対象となる列統計情報の増やすと、サンプルのサイズも増えるので、より大きな統計情報の対象を使うと、通常、より精度の高い拡張統計情報を得られますが、計算に費やす時間も増えます。
次の節では、現在サポートしている拡張統計情報の種類を説明します。
もっとも単純な拡張統計情報は、データベースの正規形の定義で使われる考え方である、関数従属性を追跡します。
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%)が存在するということになります。
関数従属する列を伴うクエリの選択率を計算する際、過小評価を行わないように、プランナは依存性係数を使って条件ごとの選択率を調整します。
今のところ、列と定数を比較する単純な等価条件と、定数の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が備わっているかもしれません。 もしそうでなければ、関数従属性は実行可能なオプションではないかもしれません。
単一列統計情報は、それぞれの列で異なる値の数を保持します。
たとえば、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
サイクルは単に無駄になります。
列ごとに格納される別なタイプの統計値は最頻値リスト(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
とプラン処理は単に無駄になってしまいます。