Расширенная статистика PostgreSQL: CREATE STATISTICS и ошибка оценки в 1 000 раз

Планировщик ждёт одну строку, а приходит тысяча: условие city = 500 AND zip = 1000 он оценивает как произведение двух независимых долей, хотя зона определяет город. Как читать такой разрыв, описано в статье про EXPLAIN ANALYZE BUFFERS, доли по одному столбцу — в статье про статистику ANALYZE. Здесь — что даёт CREATE STATISTICS.

DBA / 01koddo

Оценка rows до и после CREATE STATISTICS

Опыт 1: две доли перемножаются

Адреса: в каждом городе по две почтовые зоны, zip однозначно определяет city. Рядом таблица отправлений: на каждый из 1 000 адресов зоны 1000 приходится по 1 500 отправлений, то есть одна крупная зона с постоянной нагрузкой.

docker run -d --name dba-extended-statistics -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-extended-statistics psql -U postgres

CREATE TABLE addresses AS
SELECT g AS id, g % 2000 AS zip, (g % 2000) / 2 AS city, md5(g::text) AS street
FROM generate_series(1, 2000000) g;
ALTER TABLE addresses ADD PRIMARY KEY (id);

CREATE TABLE shipments AS
SELECT g AS id, (g % 1000) * 2000 + 1000 AS address_id, g % 50 AS weight
FROM generate_series(1, 1500000) g;
CREATE INDEX ON shipments (address_id);

ANALYZE addresses;
ANALYZE shipments;
SET max_parallel_workers_per_gather = 0;

SELECT attname, n_distinct FROM pg_stats
WHERE tablename = 'addresses' AND attname IN ('city', 'zip') ORDER BY attname;
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE city = 500;
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE zip = 1000;
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE city = 500 AND zip = 1000;

 attname | n_distinct 
---------+------------
 city    |       1000
 zip     |       2000
(2 rows)

 Seq Scan on addresses  (cost=0.00..43752.00 rows=1986 width=45) (actual time=0.046..64.151 rows=2000.00 loops=1)
   Filter: (city = 500)

 Seq Scan on addresses  (cost=0.00..43752.00 rows=993 width=45) (actual time=0.029..61.575 rows=1000.00 loops=1)
   Filter: (zip = 1000)

 Seq Scan on addresses  (cost=0.00..48752.00 rows=1 width=45) (actual time=0.054..68.246 rows=1000.00 loops=1)
   Filter: ((city = 500) AND (zip = 1000))
   Rows Removed by Filter: 1999000

В блоке вывода оставлены строки с оценками и условиями фильтра; остальное убрано. По одному столбцу оценки точные: 1 986 против 2 000 и 993 против 1 000. По двум планировщик умножает доли: 1986 / 2 000 000 × 993 / 2 000 000 × 2 000 000 ≈ 0,99, то есть rows=1. Факт — 1 000, ошибка в 1 000 раз, и равна она числу городов: каждая зона целиком лежит в одном городе, а условие по городу добавляет к оценке лишний множитель 1/1000.

Опыт 2: dependencies и смена плана

Сначала запрос, ради которого всё затевалось: сумма веса отправлений по адресам одной зоны.

EXPLAIN (ANALYZE, BUFFERS OFF)
SELECT sum(s.weight) FROM addresses a JOIN shipments s ON s.address_id = a.id
WHERE a.city = 500 AND a.zip = 1000;

 Aggregate  (cost=52607.04..52607.05 rows=1 width=8) (actual time=1024.688..1024.689 rows=1.00 loops=1)
   ->  Nested Loop  (cost=20.05..52607.04 rows=1 width=4) (actual time=0.389..985.094 rows=1500000.00 loops=1)
         ->  Seq Scan on addresses a  (cost=0.00..48752.00 rows=1 width=4) (actual time=0.062..72.273 rows=1000.00 loops=1)
               Filter: ((city = 500) AND (zip = 1000))
               Rows Removed by Filter: 1999000
         ->  Bitmap Heap Scan on shipments s  (cost=20.05..3840.04 rows=1500 width=8) (actual time=0.198..0.830 rows=1500.00 loops=1000)
               Recheck Cond: (a.id = address_id)
               Heap Blocks: exact=1500000
               ->  Bitmap Index Scan on shipments_address_id_idx  (cost=0.00..19.68 rows=1500 width=0) (actual time=0.093..0.093 rows=1500.00 loops=1000)
                     Index Cond: (address_id = a.id)
                     Index Searches: 1000
 Planning Time: 0.751 ms
 Execution Time: 1024.741 ms

Для одной внешней строки поиск по индексу shipments стоит 3 840, это дешевле одного прохода по таблице (Seq Scan on shipments стоит 23 128). Планировщик выбрал вложенный цикл, но выполнил его 1 000 раз: пять следующих запусков дали 964–1002 мс. Теперь статистика по паре столбцов:

CREATE STATISTICS addr_city_zip (dependencies) ON city, zip FROM addresses;
ANALYZE addresses;
SELECT statistics_name, dependencies FROM pg_stats_ext WHERE statistics_name = 'addr_city_zip';
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE city = 500 AND zip = 1000;

 statistics_name |     dependencies     
-----------------+----------------------
 addr_city_zip   | {"2 => 3": 1.000000}

 Seq Scan on addresses  (cost=0.00..48752.00 rows=998 width=45) (actual time=0.055..61.757 rows=1000.00 loops=1)
   Filter: ((city = 500) AND (zip = 1000))
   Rows Removed by Filter: 1999000

2 => 3 читается по номерам столбцов таблицы: zip (второй) определяет city (третий) со степенью 1,0 — все строки выборки согласованы с зависимостью. Оценка стала 998. Планировщик берёт её из формулы, которая в коде записана комментарием перед clauselist_apply_dependencies:

 * a selectivity of Min(P(a), P(b)) for the dependent rows, since the result
 * should obviously not exceed either column's individual selectivity.  I.e.,
 * we actually combine selectivities using the formula
 *
 *		P(a,b) = f * Min(P(a), P(b)) + (1-f) * P(a) * P(b)

При f = 1 остаётся Min(P(zip), P(city)): доля города не добавляет к оценке множителя, итог определяет доля зоны. Тот же запрос с соединением после статистики:

 Aggregate  (cost=61145.00..61145.01 rows=1 width=8) (actual time=346.590..346.592 rows=1.00 loops=1)
   ->  Nested Loop  (cost=0.44..61143.13 rows=748 width=4) (actual time=0.070..311.653 rows=1500000.00 loops=1)
         ->  Seq Scan on shipments s  (cost=0.00..23128.00 rows=1500000 width=8) (actual time=0.014..55.891 rows=1500000.00 loops=1)
         ->  Memoize  (cost=0.44..0.52 rows=1 width=4) (actual time=0.000..0.000 rows=1.00 loops=1500000)
               Cache Key: s.address_id
               Cache Mode: logical
               Hits: 1499000  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 102kB
               ->  Index Scan using addresses_pkey on addresses a  (cost=0.43..0.51 rows=1 width=4) (actual time=0.010..0.010 rows=1.00 loops=1000)
                     Index Cond: (id = s.address_id)
                     Filter: ((city = 500) AND (zip = 1000))
                     Index Searches: 1000
 Planning Time: 0.591 ms
 Execution Time: 346.629 ms

Планировщик сменил ведущую таблицу: с оценкой 998 адресов Bitmap Heap Scan 998 раз стоит около 3,8 млн, и дешевле прочитать все отправления один раз (23 128) и искать адрес по первичному ключу через Memoize: 1 000 промахов и 1 499 000 попаданий в кэш. Пять следующих запусков дали 335–345 мс против 964–1002 мс, ускорение почти втрое. ANALYZE берёт случайную выборку, поэтому у тебя оценка может отличаться от 998.

Опыт 3: где dependencies врёт, а mcv и ndistinct помогают

Зависимость — усреднённое свойство столбцов, а не знание о конкретных парах. Город 7 и зона 1000 несовместимы, строк нет вовсе:

EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE city = 7 AND zip = 1000;
DROP STATISTICS addr_city_zip;
CREATE STATISTICS addr_city_zip_mcv (mcv) ON city, zip FROM addresses;
ALTER STATISTICS addr_city_zip_mcv SET STATISTICS 2000;
ANALYZE addresses;
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE city = 7 AND zip = 1000;
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT * FROM addresses WHERE city = 500 AND zip = 1000;

 Seq Scan on addresses  (cost=0.00..48752.00 rows=998 width=45) (actual time=69.156..69.157 rows=0.00 loops=1)
   Filter: ((city = 7) AND (zip = 1000))

 Seq Scan on addresses  (cost=0.00..48752.00 rows=1 width=45) (actual time=65.344..65.345 rows=0.00 loops=1)
   Filter: ((city = 7) AND (zip = 1000))

 Seq Scan on addresses  (cost=0.00..48752.00 rows=930 width=45) (actual time=0.049..66.109 rows=1000.00 loops=1)
   Filter: ((city = 500) AND (zip = 1000))

С dependencies оценка для несуществующей пары — 998 при факте 0: зависимость считает, что зона всегда определяет город, и не проверяет конкретное значение. Список частых пар (mcv) хранит реальные комбинации, поэтому те же условия дают 1 при факте 0 и 930 при факте 1 000. Лимит списка поставлен на 2 000 по числу пар в таблице. Какие условия понимает каждый вид, написано в README каталога статистики:

    (a) functional dependencies - equality clauses (AND), possibly IS NULL

    (b) MCV lists - equality and inequality clauses (AND, OR, NOT), IS [NOT] NULL

Третий вид, ndistinct, не меняет оценку условий, а исправляет число групп в GROUP BY по паре столбцов:

EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT city, zip, count(*) FROM addresses GROUP BY city, zip;
DROP STATISTICS addr_city_zip_mcv;
CREATE STATISTICS addr_city_zip_nd (ndistinct) ON city, zip FROM addresses;
ANALYZE addresses;
SELECT statistics_name, n_distinct FROM pg_stats_ext WHERE statistics_name = 'addr_city_zip_nd';
EXPLAIN (ANALYZE, BUFFERS OFF, SUMMARY OFF) SELECT city, zip, count(*) FROM addresses GROUP BY city, zip;

 HashAggregate  (cost=156252.00..173877.00 rows=200000 width=16) (actual time=225.009..225.274 rows=2000.00 loops=1)
   Group Key: city, zip
   Planned Partitions: 4  Batches: 1  Memory Usage: 2201kB

 statistics_name  |   n_distinct   
------------------+----------------
 addr_city_zip_nd | {"2, 3": 2000}

 HashAggregate  (cost=53752.00..53772.00 rows=2000 width=16) (actual time=246.563..246.680 rows=2000.00 loops=1)
   Group Key: city, zip
   Batches: 1  Memory Usage: 217kB

Без статистики планировщик ждал 200 000 групп при факте 2 000 и планировал четыре раздела хеш-таблицы (Planned Partitions: 4); после неё оценка точная, стоимость узла упала с 173 877 до 53 772, а разделов нет. Время одного запуска не изменилось (225 и 247 мс, на одном запуске это шум): на 2 000 групп память не была узким местом, оценка исправила план, а не скорость. Само n_distinct для пары тоже считается по выборке оценкой Duj1, как и по одному столбцу; здесь 2 000 совпало с фактом:

	numer = (double) numrows * (double) d;

	denom = (double) (numrows - f1) +
		(double) f1 * (double) numrows / totalrows;

	ndistinct = numer / denom;

Что из этого следует

• Расхождение «оценка 1, факт 1 000» при условии по двум столбцам, каждое из которых оценено верно, — признак зависимых столбцов (город и индекс, страна и валюта, категория и подкатегория). Оценки по каждому столбцу верны, поэтому ошибку не исправит более точная статистика по одному столбцу.
• dependencies исправляет равенства, но врёт на несовместимых парах (998 при факте 0); mcv ловит и их (1 при факте 0), потому что хранит конкретные пары; ndistinct нужен только для GROUP BY и DISTINCT по набору столбцов.
• Исправленная оценка меняет план, а не только число: соединение сменило Bitmap Heap Scan ×1000 на Seq Scan с Memoize и ускорилось с 964–1002 до 335–345 мс.
• Статистика для пары не создаётся сама: CREATE STATISTICS и повторный ANALYZE нужны вручную, а содержимое видно в pg_stats_ext.

Чек-лист

1. В плане найди узел, где оценка rows отличается от факта на порядок, и посмотри, сколько условий на нём стоит. Два и больше связанных столбца — кандидат на CREATE STATISTICS.
2. Проверь каждое условие отдельно: если оценки по одному столбцу верны, а по двум неверны, причина — перемножение долей.
3. Для равенств по зависимым столбцам начни с dependencies, для редких комбинаций и неравенств — с mcv, для GROUP BY — с ndistinct; вид проверяй EXPLAIN (ANALYZE) до и после.
4. После CREATE STATISTICS выполни ANALYZE таблицы и сравни rows в EXPLAIN с фактом на самом запросе приложения.
5. Убери стенд командой docker rm -f -v dba-extended-statistics: без -v том с данными остаётся на диске.