Postgresはクエリプランニング中に行数の見積もりをどのように計算しますか

Aug 20 2020

次のような簡単なクエリがあります。

SELECT 
    * 
FROM 
    example 
WHERE 
    filter_1 = ? 
    AND filter_2 = ? 
LIMIT 
    10

テーブルは非常に大きく（約1億行）、次のようなインデックスがあります（実際のインデックスには、右側にもう1つの列がありますが、違いはありません）。

CREATE INDEX example_idx
ON public.example 
USING btree (filter_1, filter_2, (...));

それでは、問題について説明しましょう。プリペアドステートメントでクエリを実行すると、最初の5回の実行でカスタムプランが使用されます。次に、一般的な計画はより安価であると見なされ、準備されたステートメントの存続期間を思い出させるために使用されます。

Postgresがカスタムプランを使用する場合のEXPLAINANALYZEは次のとおりです。

Limit  (cost=0.57..39.35 rows=10 width=78) (actual time=0.024..0.034 rows=8 loops=1)
  ->  Index Scan using example_idx on example c0  (cost=0.57..12345.29 rows=3183 width=78) (actual time=0.024..0.032 rows=8 loops=1)
        Index Cond: (((filter_1)::text = 'rare_value_1'::text) AND (filter_2 = 'frequent_value_2'::custom_enum))
Planning Time: 0.098 ms
Execution Time: 0.045 ms

Postgresが一般的なプランを使用する場合の説明は次のとおりです。

Limit  (cost=0.00..11.31 rows=10 width=78)
  ->  Seq Scan on example_idx c0  (cost=0.00..3469262.28 rows=3067235 width=78)
        Filter: (((filter_1)::text = $1) AND (filter_2 = $2))

ここでは、ジェネリックプランのコストが低いことがはっきりとわかります。

私の問題は、インデックススキャンとシーケンススキャンの行数の見積もりがどのように計算されるかです。

ドキュメントでは、どのように計算するかを説明しています3183。これは、カスタムプランの推定行数です。

rare_value_1とfrequent_value_2は両方ともMCVリストにあります。そしてそれらの頻度はそれぞれ0.00002667と0.99783です。また、推定テーブル行数は119622152です。

0.00002667 * 0.99783 * 119622152 = 3183

残りの質問は、それが一般的な計画のためにどのように行われるかということです。

なんらかの理由で、MCV周波数が無視されていることがわかりました。そして、Postgresqln_distinctは列filter_1とfilter_2（それぞれ13と3）の値を調べるだけです。

estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
                    = 119622152 / (13 * 3)
                    = 3067235

私の質問はなぜですか？Postgresqlは、MCV頻度の形式でより良い統計にアクセスできるため、なぜこのような原始的な方法を使用して行数を推定するのですか？

Postgresqlバージョン：11（したがって、現時点では「force_custom_plan」オプションを使用することはできません）。

回答

2 LaurenzAlbe Aug 20 2020 at 19:34

一般的なプランの行数を見積もるのにこれ以上の方法はありません。

最も一般的な値（MCV）の頻度で行くことを選択した場合、いくつかの問題があります。

どのMCVを選択しますか？
両方のパラメーターがMCVであると仮定すると、推定はより適切になりますが、それは根拠のない仮定です。両方がまれな値である場合、見積もりは現在の見積もりよりもさらに遠くになります。

平均を選択することが私たちにできる最善のことです。それは中立的なものです。値の1つが非常にまれであり、カスタムプランの見積もりが低くなるため、ケースはマークから大きく外れていますが、明確な値が少なく、1つが非常に頻繁であるため、平均して選択性が低く、一般的なプラン見積もりが高すぎます。