👩🏻‍🏫 🧚🏻 🚼 不可解なことを説明する。パート5 👊🏼 👩🏻‍🎨 🤡

PG Day'16の準備を続け、PostgreSQLの興味深い機能を紹介します。

このシリーズの以前の投稿では、 EXPLAIN出力の読み方と各行（操作/ノード）の意味について説明しました。

最終投稿では、Postgresが「Operation Y」ではなく「Operation X」を選択した理由を説明します。

PostgreSQLスケジューラが統計に基づいて操作を選択すると聞いたことがあるかもしれません。どんな統計？

可能な限り単純なシナリオを想像してみましょう。

SELECT * FROM table WHERE column = some_value;

テーブル内のすべての行に同じsome_value値がある場合、列にインデックス（既存の可能性がある）を適用しても意味がありません。

一方、列の値が一意（またはほぼ一意）である場合は、インデックスを使用することをお勧めします。

何が起こるか見てみましょう：

 create table test ( all_the_same int4, almost_unique int4 ); CREATE TABLE insert into test ( all_the_same, almost_unique ) select 123, random() * 1000000 from generate_series(1,100000); INSERT 0 100000

そのため、100,000行のテーブルがあり、「all_the_same」列の値は常に同じ（123）であり、名前が示すように、almost_unique列はほぼ一意です。

 select count(*), count(distinct almost_unique) from test; count | count --------+------- 100000 | 95142 (1 row)

次に、それらを等しくするために、2つの単純なインデックスを作成します。

 create index i1 on test (all_the_same); CREATE INDEX create index i2 on test (almost_unique); CREATE INDEX

OK、テスト構成の準備ができました。計画はどうですか？

 explain select * from test where all_the_same = 123; QUERY PLAN ------------------------------------------------------------ Seq Scan on test (cost=0.00..1693.00 rows=100000 width=8) Filter: (all_the_same = 123) (2 rows) explain select * from test where almost_unique = 123; QUERY PLAN --------------------------------------------------------------- Index Scan using i2 on test (cost=0.29..8.31 rows=1 width=8) Index Cond: (almost_unique = 123) (2 rows)

ご覧のとおり、Postgresは賢明な選択をしました。しかし、ここでは、「rows =」の推定値が重要です。クエリが返すことができる行数をどのようにして知るのでしょうか？

答えは、 ANALYZEまたはVACUUM ANALYZEチームにあります。

テーブルに「ANALYZE」を適用すると、Postgresは「ランダムサンプル」を取得し（後で詳しく説明します）、統計情報を取得します。これはどのような統計情報で、どこにあり、それを見ることができますか？もちろん次のことができます。

 select * from pg_statistic where starelid = 'test'::regclass; -[ RECORD 1 ]----------------------------------------------------------------------------- starelid | 16882 staattnum | 1 stainherit | f stanullfrac | 0 stawidth | 4 stadistinct | 1 stakind1 | 1 stakind2 | 3 stakind3 | 0 stakind4 | 0 stakind5 | 0 staop1 | 96 staop2 | 97 staop3 | 0 staop4 | 0 staop5 | 0 stanumbers1 | {1} stanumbers2 | {1} stanumbers3 | [null] stanumbers4 | [null] stanumbers5 | [null] stavalues1 | {123} stavalues2 | [null] stavalues3 | [null] stavalues4 | [null] stavalues5 | [null] -[ RECORD 2 ]----------------------------------------------------------------------------- starelid | 16882 staattnum | 2 stainherit | f stanullfrac | 0 stawidth | 4 stadistinct | -0.92146 stakind1 | 1 stakind2 | 2 stakind3 | 3 stakind4 | 0 stakind5 | 0 staop1 | 96 staop2 | 97 staop3 | 97 staop4 | 0 staop5 | 0 stanumbers1 | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05} stanumbers2 | [null] stanumbers3 | {-0.000468686} stanumbers4 | [null] stanumbers5 | [null] stavalues1 | {21606,27889,120502,289914,417495,951355,283,1812,3774,6028,6229,10372,12234,13291,18309,18443,21758,22565,26634,28392,28413,31208,32890,36563,39277,40574,44527,49954,53344,53863,56492,56715,60856,62993,64294,65275,65355,68353,71194,74718,77205,82096,82783,84764,85301,87498,90990,94043,97304,98779,101181,103700,103889,106288,108562,110796,113154,117850,121578,122643,123874,126299,129236,129332,129512,134430,134980,136987,137368,138175,139001,141519,142934,143432,143707,144501,148633,152481,154327,157067,157799,162437,164072,164337,165942,167611,170319,171047,177383,184134,188702,189005,191786,192718,196330,197851,199457,202652,202689,205983} stavalues2 | {2,10560,20266,31061,40804,50080,59234,69240,79094,89371,99470,109557,119578,130454,140809,152052,162656,173855,183914,194263,204593,214876,224596,233758,243246,253552,264145,273855,283780,294475,303972,314544,324929,335008,346169,356505,367395,376639,387302,397004,407093,416615,426646,436146,445701,455588,466463,475910,485228,495434,505425,515853,525374,534824,545387,554794,563591,573721,584021,593368,602935,613238,623317,633947,643431,653397,664177,673976,684042,694791,703922,714113,724602,735848,745596,754477,764171,772535,781924,791652,801703,812487,822196,831618,841665,850722,861532,872067,881570,891654,901595,910975,921698,931785,940716,950623,960551,970261,979855,989540,999993} stavalues3 | [null] stavalues4 | [null] stavalues5 | [null]

この表（pg_statistic）は、もちろん、ドキュメントで説明されていますが、それでもかなり不可解です。もちろん、ソースで非常に正確な説明を見つけることができますが、これは（通常）最良の解決策ではありません。

幸いなことに、より読みやすいビューで同じデータを含むこのテーブルのビューがあります。

 select * from pg_stats where tablename = 'test'; -[ RECORD 1 ]----------+------------------------------------------------------------------ schemaname | public tablename | test attname | all_the_same inherited | f null_frac | 0 avg_width | 4 n_distinct | 1 most_common_vals | {123} most_common_freqs | {1} histogram_bounds | [null] correlation | 1 most_common_elems | [null] most_common_elem_freqs | [null] elem_count_histogram | [null] -[ RECORD 2 ]----------+------------------------------------------------------------------ schemaname | public tablename | test attname | almost_unique inherited | f null_frac | 0 avg_width | 4 n_distinct | -0.92146 most_common_vals | {21606,27889,120502,289914,417495,951355,283,1812,3774,6028,6229,10372,12234,13291,18309,18443,21758,22565,26634,28392,28413,31208,32890,36563,39277,40574,44527,49954,53344,53863,56492,56715,60856,62993,64294,65275,65355,68353,71194,74718,77205,82096,82783,84764,85301,87498,90990,94043,97304,98779,101181,103700,103889,106288,108562,110796,113154,117850,121578,122643,123874,126299,129236,129332,129512,134430,134980,136987,137368,138175,139001,141519,142934,143432,143707,144501,148633,152481,154327,157067,157799,162437,164072,164337,165942,167611,170319,171047,177383,184134,188702,189005,191786,192718,196330,197851,199457,202652,202689,205983} most_common_freqs | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05} histogram_bounds | {2,10560,20266,31061,40804,50080,59234,69240,79094,89371,99470,109557,119578,130454,140809,152052,162656,173855,183914,194263,204593,214876,224596,233758,243246,253552,264145,273855,283780,294475,303972,314544,324929,335008,346169,356505,367395,376639,387302,397004,407093,416615,426646,436146,445701,455588,466463,475910,485228,495434,505425,515853,525374,534824,545387,554794,563591,573721,584021,593368,602935,613238,623317,633947,643431,653397,664177,673976,684042,694791,703922,714113,724602,735848,745596,754477,764171,772535,781924,791652,801703,812487,822196,831618,841665,850722,861532,872067,881570,891654,901595,910975,921698,931785,940716,950623,960551,970261,979855,989540,999993} correlation | -0.000468686 most_common_elems | [null] most_common_elem_freqs | [null] elem_count_histogram | [null]

素晴らしい。ここからどのような知識を引き出すことができますか？

列schemaname、tablename、およびattnameは明らかです。継承は、このテーブルの値に、この列を継承したテーブルの値が含まれているかどうかを単に報告します。

したがって、テーブルを作成した場合：

 create table z () inherits (test);

そして、このテーブルzにデータを追加すると、テストテーブルの統計に「inherited = true」と表示されます。

残りの列は次のことを示しています。

null_frac-この列のNULL行数。これは分数であるため、値は0〜1になります。
avg_width-この列のデータの平均幅（約。：サイズ）。これは、幅が一定の場合（この例ではint4のような）それほど興味深いものではありませんが、可変幅（text / varchar / numericなど）を持つデータ型の場合は便利です。
n_distinctは非常に興味深い値です。正（1+）の場合、n_distinctが1と等しいtrueのall_the_same列の場合に見られるように、異なる値の単なる概数（分数ではありません！）になります。負の場合、意味は変わります：n_distinctは行の割合は一意です。したがって、almost_uniqueの場合、統計では、行の92.146％が一意の値を持っていることが示唆されます（前に示した95.142％よりわずかに小さい）。私が言及し、後で詳細に説明する「ランダムなサンプル」のために、値は間違っている可能性があります。
most_common_valsは、このテーブルの最も一般的な値の配列です。
most_common_freqs-most_common_valsからの値が見つかる頻度も小数であるため、最大値は1です（ただし、most_common_valsには値が1つしかありません）。ここで、almost_uniqueで、Postgresは値21606、27889、120502、289914、417495、951355が最も頻繁に見つかると「考えている」ことがわかりますが、そうではありません。繰り返しますが、「ランダムパターン」効果は非難することです。
histogram_bounds-データセット全体を同じ行数のグループに分割する（または分割する必要がある-再びすべてが「ランダムパターン」にある）値の配列。つまり、2から10560の間のalmost_unique行の数は、931785から940716の間のalmost_unique行の数と同じ（多かれ少なかれ）です。
相関は非常に興味深い統計であり、ディスク上の行の物理的なソートと値の間に相関があるかどうかを示します。この値は-1から1まで変化でき、-1 / 1に近いほど相関が大きくなります。たとえば、「i2を使用したCLUSTERテスト」を実行すると、テーブルがほぼ固有の順序で並べ替えられ、0.919358の相関関係が得られました。これは、以前の値-0.000468686よりもはるかに優れています。

most_common_elems、most_common_elem_freqs、elem_count_histogramはmost_common_vals、most_common_freqs、histogram_boundsと同じですが、非スカラーデータ型（配列、tsvectorなど）の場合です。

PostgreSQLはこのデータに基づいて、クエリの選択された部分から返される行数を概算し、この情報に基づいて、どちらを使用するのが適切かを決定します。seqスキャン、インデックススキャン、またはビットマップインデックススキャン。マージするとき-ハッシュ結合、結合結合、またはネストループなど、より高速な操作が必要です。

上記のデータを慎重に検討すると、疑問に思うかもしれません。これはかなり広範な出力データのセットであり、配列most_common_vals / most_common_freqs / histogram_boundsには多くの値が含まれています。なぜそんなにたくさんあるの？

理由は簡単です-設定がすべてです。 postgresql.confには、default_statistics_target変数があります。この変数は、これらの配列に格納する値の数をPostgresに伝えます。私の場合（デフォルト）、この数は100です。しかし、簡単に変更できます。 postgresql.confを変更するか、次のように個々の列を変更します。

 alter table test alter column almost_unique set statistics 5;

ALTER（およびANALYZE）を使用すると、pg_statsのデータは大幅に短縮されます。

 select * from pg_stats where tablename = 'test' and not inherited and attname = 'almost_unique'; -[ RECORD 1 ]----------+--------------------------------------------------------- schemaname | public tablename | test attname | almost_unique inherited | f null_frac | 0 avg_width | 4 n_distinct | -0.92112 most_common_vals | {114832,3185,3774,6642,11984} most_common_freqs | {0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05} histogram_bounds | {2,199470,401018,596414,798994,999964} correlation | 1 most_common_elems | [null] most_common_elem_freqs | [null] elem_count_histogram | [null]

統計ターゲットを変更すると、別の効果もあります。

見せてあげましょう。始めるために、ALTER TABLEを使用して行った統計の変更をロールバックします。

 alter table test alter column almost_unique set statistics -1;

次の手順を実行します。

 $ analyze verbose test; INFO: analyzing "public.test" INFO: "test": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows ANALYZE $ alter table test alter column almost_unique set statistics 10; ALTER TABLE $ alter table test alter column all_the_same set statistics 10; ALTER TABLE $ analyze verbose test; INFO: analyzing "public.test" INFO: "test": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 3000 rows in sample, 100000 estimated total rows ANALYZE

2番目の分析では、最初の30,000行ではなく3,000行しかテストされていないことに注意してください。

これは「ランダムサンプル」です。

すべての行の分析は、中規模または大規模のテーブルでは非常に高価です。

したがって、Postgresはよりスマートになっています。

まず、テーブルのページのランダムな部分を読み取ります（各ページは8kBのデータを思い出してください）。正確にいくらですか？ 300 * statistics_target。

これは、default_statistics_target = 100の場合、30,000ページを読み取ることを意味します（私のテーブルにはそれほど多くないので、Postgresはそれらをすべて読み取ります）。

これらのページから、ANALYZEはライブおよびデッドラインに関する情報のみを取得します。次に、ランダムな行パターンに関するデータ（再び300 *統計ターゲット）を受信し、このデータに基づいて列の統計を計算します。

私の場合、テーブルには100,000行ありましたが、default_statistics_target = 100の場合、分析されたのは3分の1だけです。また、統計ターゲットの値を考慮に入れると、分析される行の数はさらに少なくなり、3000になります。

あなたは言うことができます：OK、しかしこの場合、これらの統計は不正確です。スキャンされた行のいずれにも、非常に一般的な値が表示されない場合があります。もちろん、あなたは正しいです。可能です。あまりありませんが。ランダムなデータを受け取ります。他のすべての行に存在する何らかの値を持つ単一行が存在しないテーブルのx％を取得する可能性は無視できます。

また、場合によっては、分析の実行によってクエリが「壊れる」ことも意味します。たとえば、他のページの統計情報を取得すると、一部の値がスキップされることがわかります（またはその逆-most_common_valsであまり一般的な値を取得しないため、Postgresが適切なページ/行を選択してそれらを表示します）。そして、そのような統計に基づいて、Pgは最適ではない計画を生成します。

このような状況に遭遇した場合、それを解決するのは非常に簡単です-統計ターゲットを増やします。これにより、analyzeが一生懸命作業し、より多くの行をスキャンするように強制されるため、これが再び発生する可能性はさらに少なくなります。

ただし、大きな統計ターゲット値を設定すると、特定の欠点があります。最初に、ANALYZEは一生懸命働く必要がありますが、これは搾取の問題であるため、あまり気にしません（通常）。主な問題は、pg_statisticのデータが多いほど、Pgスケジューラーにより多くのデータを考慮する必要があることです。したがって、default_statistics_targetを最大10,000に設定するのがどんなに魅力的であっても、実際には、この値が非常に高いデータベースには遭遇していません。

現在の100は、バージョン8.4からデフォルトでインストールされます。以前のバージョンでは、デフォルト値は10でしたが、ircにはそれを増やすためのヒントがしばしばありました。 100の値で、すべてが多かれ少なかれ構成されています。

私が最後に話さなければならないことは、私はあまり望んでいませんが、Postgresスケジューラーが異なる操作を使用するようにする設定です。

最初に、なぜそれについて話したくないのかを説明します。これは簡単に悪用される可能性があることは確かです。そのため、これらの設定は、問題を解決するためではなく、問題を見つけるために必要です。動作モードでそれらを使用するアプリケーションは、少なくとも、破損した疑いがあります。そして、はい、私は時々これをしなければならないことを知っています。しかし、この「時々」はめったに起こりません。

私はあなたに警告したので、何ができるか見てみましょう。

postgresql.confには、いくつかのオプションがあります。

 enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_indexonlyscan = on enable_material = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on

これらの設定は、選択した操作を無効にするために必要です。

たとえば、enable_seqscanをfalseに切り替えると（SQLセッションでSETコマンドを使用して実行できます。postgresql.confを変更する必要はありません）、スケジューラはシーケンシャルスキャンを回避するために可能な限りすべてを使用します。

また、シーケンシャルスキャンを避けることができない場合があるため（たとえば、テーブルにインデックスがない場合）、これらの設定は実際に操作を無効にするのではなく、使用に莫大なコストがかかります。

例を挙げます。テストパターンに関しては、「all_the_same = 123」で検索すると、費用がかからないため、順次スキャンが使用されることがわかります。

 explain select * from test where all_the_same = 123; QUERY PLAN ------------------------------------------------------------ Seq Scan on test (cost=0.00..1693.00 rows=100000 width=8) Filter: (all_the_same = 123) (2 rows)

ただし、seqスキャンを無効にした場合：

 set enable_seqscan = false; SET explain select * from test where all_the_same = 123; QUERY PLAN ----------------------------------------------------------------------- Index Scan using i1 on test (cost=0.29..3300.29 rows=100000 width=8) Index Cond: (all_the_same = 123) (2 rows)

インデックススキャン〜を使用して同じデータを取得するための推定コストは2倍高いことがわかります（3300.29対1693）。

i1インデックスを削除する場合：

 drop index i1; DROP INDEX set enable_seqscan = false; SET explain select * from test where all_the_same = 123; QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on test (cost=10000000000.00..10000001693.00 rows=100000 width=8) Filter: (all_the_same = 123) (2 rows)

また、シーケンシャルスキャン以外に他のオプションがない場合（このインデックスにはテーブル内のすべての行へのポインターがあるにもかかわらず、Postgresがi2でインデックススキャンを選択しなかったのは興味深いことです）、コストが10,000,000,000に急騰しました-これはenable_ *です= falseおよびありません。

それがすべてだと思います。シリーズ全体を読むと、何が起こっているのか、さらに重要なのはなぜかを理解するのに十分な知識が得られているはずです。

不可解なことを説明する。 パート5

More articles:

不可解なことを説明する。パート5