Скриншот вверху взят из файла
analyze.c в исходном коде PostgreSQL. Число 300 — это жёстко заданное значение в коде ANALYZE. Его происхождение связано с научной работой "Random Sampling for Histogram Construction: How Much Is Enough?", опубликованной в 1998 году, когда объёмы данных были значительно меньше, а оборудование — намного медленнее.В статье рассматривался вопрос:
сколько строк нужно выбрать в выборку, чтобы построить статистику, достаточно точную для оптимизации запросов к неиндексированным данным?
Ответ оказался примерно таким: около 300 выборок на каждый бакет (bin) гистограммы равной высоты (equi-height histogram).
Почему именно столько?
Статья показывает, что необходимый объём выборки растёт линейно с количеством бакетов, но лишь логарифмически с размером таблицы в большинстве практических случаев. Поэтому после нескольких сотен выборок на бакет отдача от увеличения выборки становится всё меньше.
Например, значение
statistics_target по умолчанию равно 100.Это означает, что PostgreSQL стремится собрать выборку размером:
300 × 100 = 30 000 значений
чтобы:
- построить гистограмму равной высоты из 100 бакетов;
- сохранить 100 наиболее часто встречающихся значений (Most Common Values, MCV).
Зачем столько усилий ради неиндексированных данных?
Потому что в 1998 году индексы были значительно дороже, чем сегодня:
- занимали ценное дисковое пространство;
- потребляли ограниченные IOPS при записи и построении;
- были дорогими в сопровождении;
- полные сканирования таблиц выполнялись медленно и блокировали работу.
В то время производительность дисков измерялась в RPM (оборотах в минуту). Говорить об IOPS было сложнее, поскольку случайный доступ к данным требовал ожидания поворота диска до нужного сектора, а физическое расположение данных заранее было неизвестно.
Тесты из статьи выполнялись на системе со следующими характеристиками:
- процессор Pentium 200 МГц;
- 64 МБ оперативной памяти;
- SCSI-диск 7200 RPM.
Пользователи PostgreSQL продолжают получать выгоду от этой работы даже сегодня.
Да, индексы по-прежнему не бесплатны, и их может быть слишком много, но их стоимость уже далеко не такая, как в конце 90-х. Аналогично и работа с неиндексированными данными стала намного менее затратной.
Компромисс между точностью и производительностью
Авторы статьи также отмечают, что задача является:
«доказуемо сложной, поскольку существует предел достижимой точности оценки в худшем случае».
Поэтому:
«мы разработали простой метод оценки, который, по нашему мнению, является оптимальным».
Число 300 представляет собой компромисс между точностью и скоростью работы:
- меньшее значение дало бы менее точную статистику и могло привести к ошибочным решениям планировщика запросов;
- большее значение улучшило бы точность, но замедлило бы работу
ANALYZE.А в те времена
ANALYZE и без того работал значительно медленнее.Что контролирует
statistics_target?Параметр
statistics_target определяет количество значений, сохраняемых для:- Most Common Values (MCV);
- Equi-height Histogram.
Например:
statistics_target = 100 → 30 000 выборок, 100 MCV, 100 бакетов
statistics_target = 500 → 150 000 выборок, 500 MCV, 500 бакетов
statistics_target = 1000 → 300 000 выборок, 1000 MCV, 1000 бакетов
По умолчанию этот параметр задаётся на уровне базы данных, но его можно переопределить для отдельного столбца:
-- Настройка для конкретного столбца
ALTER TABLE requests
ALTER COLUMN status_code
SET STATISTICS 500;
ANALYZE requests;
Для крупных баз данных обычно находится хотя бы один столбец, для которого имеет смысл увеличить значение статистики локально. Не стоит повышать глобальное значение по умолчанию только из-за одного столбца, которому требуется более детальная статистика.
Сегодня влияние настроек статистики меньше, чем раньше, но для отдельных столбцов они всё ещё могут улучшить планы запросов.
👉 @SQLPortal