Как Postgres вычисляет оценку количества строк во время планирования запроса

Aug 20 2020

У меня есть простой запрос, например:

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

Таблица довольно большая (около 100 миллионов строк), и у нее есть индекс, подобный следующему (фактический индекс имеет еще один столбец с правой стороны, но это не должно иметь никакого значения):

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

Итак, теперь давайте опишем проблему: когда я запускаю свой запрос в подготовленном операторе, первые 5 выполнений используют индивидуальный план. Тогда общий план рассматривается как менее затратный и используется для напоминания о времени жизни подготовленного оператора.

Вот EXPLAIN ANALYZE, когда Postgres использует индивидуальный план:

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 игнорируются. И Postgresql просто смотрит на n_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, но это необоснованное предположение. Если бы оба значения были редкими, оценка была бы даже более далекой от нормы, чем текущая оценка.

Лучшее, что мы можем сделать - это выбрать среднее: это что-то среднее. Ваш случай так далек от истины, потому что одно из значений является чрезвычайно редким, что снижает оценку пользовательского плана, но поскольку существует несколько различных значений, а одно из них очень частое, селективность в среднем плохая, а общий план оценка слишком высока.

У вас есть два варианта:

  • Используйте динамический SQL, чтобы всегда получать индивидуальный план.

  • Измените запрос на чтение

    WHERE filter_1 || '' = ?
    

    так что он не может использовать index.