Статистика в PostgreSQL/SQL Server, ANALYZE, auto-vacuum?

#Зачем оптимизатору статистика

SELECT * FROM orders WHERE status = 'refunded' AND created_at > now() - interval '1 day';

Выбор между Index Scan и Seq Scan, Nested Loop и Hash Join зависит от того, сколько строк вернёт условие: 10 или 10 миллионов. Оптимизатор не выполняет запрос заранее — он оценивает по статистике.

#Что хранится

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Для status = 'refunded' (не входит в MCV) оптимизатор оценит долю как «остаток / число редких значений».

#Расширенная статистика

Оптимизатор по умолчанию считает условия независимыми: city = 'Tashkent' AND country = 'UZ' оценивается как произведение вероятностей — сильная недооценка, ведь город определяет страну.

CREATE STATISTICS st_addr_city_country (dependencies, ndistinct, mcv) ON city, country FROM addresses;
ANALYZE addresses;

В SQL Server многостолбцовая статистика создаётся для составных индексов или вручную CREATE STATISTICS ... ON (city, country).

#Как статистика устаревает

• Массовая загрузка или удаление данных.
• Новые значения за пределами гистограммы: оптимизатор думает, что строк с created_at > вчера почти нет, потому что статистика собрана неделю назад («ascending key problem»).
• Изменение распределения (новый крупный клиент).

-- PostgreSQL
ANALYZE orders;                                   -- собрать статистику (выборка строк, быстро)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;   -- детальнее (больше MCV/корзин)
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables;

-- SQL Server
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
SELECT name, STATS_DATE(object_id, stats_id) AS last_updated FROM sys.stats WHERE object_id = OBJECT_ID('dbo.Orders');

#VACUUM и MVCC в PostgreSQL

Что делает обычный VACUUM (и autovacuum):

1. Находит мёртвые кортежи, невидимые ни одной активной транзакции, и освобождает место внутри страниц для новых вставок (файл обычно не уменьшается).
2. Удаляет ссылки на них из индексов.
3. Обновляет visibility map — страницы, где все строки видимы всем; без этого Index Only Scan вынужден ходить в таблицу.
4. Замораживает старые идентификаторы транзакций (freeze) — защита от wraparound: счётчик транзакций 32-битный, и без заморозки через ~2 млрд транзакций старые данные «исчезли» бы. При угрозе PostgreSQL принудительно запускает агрессивный vacuum, а в крайнем случае перестаёт принимать запись.

#Autovacuum и его настройка

Autovacuum запускается для таблицы, когда число мёртвых строк превышает autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × число строк (по умолчанию 50 + 20%). Для таблицы в 100 млн строк это 20 млн мёртвых строк — слишком поздно.

-- Для больших часто обновляемых таблиц — агрессивнее
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_limit = 2000
);

-- Мониторинг раздувания и работы autovacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

#Что мешает VACUUM

• Долгие транзакции (в том числе «idle in transaction» — приложение открыло транзакцию и забыло): VACUUM не может удалить версии, которые могут понадобиться этой транзакции → bloat растёт по всей базе. Лечится idle_in_transaction_session_timeout и дисциплиной в коде.
• Забытые слоты репликации и долгие запросы на репликах с hot_standby_feedback.
• Слишком «ленивые» настройки autovacuum для больших таблиц.

#SQL Server

• Статистика: AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS (порог в новых версиях динамический, ~√(1000 × строк)), AUTO_UPDATE_STATISTICS_ASYNC — не блокировать запрос на время обновления.
• Версии строк для RCSI/SNAPSHOT хранятся в tempdb (version store) и очищаются фоново; долгие транзакции раздувают tempdb — аналог проблемы долгих транзакций в PostgreSQL.
• Обслуживание индексов: ALTER INDEX ... REORGANIZE (онлайн, лёгкая дефрагментация) / REBUILD (полная перестройка, с ONLINE = ON в Enterprise). Популярный инструмент — скрипты Ola Hallengren.
• Query Store — история планов и возможность «закрепить» хороший план при регрессии.

#Вопросы на засыпку

#Итог

Оптимизатор принимает решения по статистике, поэтому свежая и детальная статистика — условие хороших планов (ANALYZE, расширенная статистика для коррелирующих столбцов). В PostgreSQL MVCC требует VACUUM: он освобождает место мёртвых версий, поддерживает visibility map и защищает от wraparound. Настраивайте autovacuum для больших таблиц и избегайте долгих транзакций.