Как Postgres вычисляет оценку количества строк во время планирования запроса
У меня есть простой запрос, например:
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" в настоящее время для нас невозможно).
Ответы
Нет лучшего способа оценить количество строк для общего плана.
Если вы выберете частоты наиболее распространенного значения (MCV), возникнет несколько проблем:
Какой MCV вы выберете?
Для оценки лучше было бы предположить, что оба параметра являются MCV, но это необоснованное предположение. Если бы оба значения были редкими, оценка была бы даже более далекой от нормы, чем текущая оценка.
Лучшее, что мы можем сделать - это выбрать среднее: это что-то среднее. Ваш случай так далек от истины, потому что одно из значений является чрезвычайно редким, что снижает оценку пользовательского плана, но поскольку существует несколько различных значений, а одно из них очень частое, селективность в среднем плохая, а общий план оценка слишком высока.
У вас есть два варианта:
Используйте динамический SQL, чтобы всегда получать индивидуальный план.
Измените запрос на чтение
WHERE filter_1 || '' = ?
так что он не может использовать index.