Модель стоимости планировщика PostgreSQL: откуда берутся cost= и random_page_cost

В плане стоит cost=0.00..31764.00, и это число можно получить на калькуляторе: страницы таблицы, строки и три параметра из pg_settings. Как читать готовый план, написано в статье про EXPLAIN ANALYZE BUFFERS; здесь разберём, откуда берутся сами числа, почему при одинаковом числе строк индекс стоит 496 или 32 627 и когда random_page_cost = 1.1 помогает, а когда замедляет запрос в четыре раза.

DBA / 01koddo

Seq Scan против индекса при двух random_page_cost

Опыт 1: стоимость Seq Scan пересчитана вручную

Таблица на миллион строк: id по порядку, k перемешан (перестановка чисел от 0 до 999 999), grp принимает десять значений, pad растягивает строку до 117 байт.

docker run -d --name dba-planner-cost-model -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-planner-cost-model psql -U postgres

CREATE TABLE t AS
SELECT g AS id, (g::bigint * 7919) % 1000000 AS k, g % 10 AS grp, repeat('x', 100) AS pad
FROM generate_series(1, 1000000) g;
CREATE INDEX t_id ON t (id);
CREATE INDEX t_k ON t (k);
VACUUM ANALYZE t;
SET max_parallel_workers_per_gather = 0;

SELECT relpages, reltuples FROM pg_class WHERE relname = 't';
SELECT name, setting FROM pg_settings
WHERE name IN ('seq_page_cost', 'cpu_tuple_cost', 'cpu_operator_cost', 'random_page_cost', 'effective_cache_size');

 relpages | reltuples
----------+-----------
    19264 |     1e+06

         name         | setting
----------------------+---------
 cpu_operator_cost    | 0.0025
 cpu_tuple_cost       | 0.01
 effective_cache_size | 524288
 random_page_cost     | 4
 seq_page_cost        | 1

EXPLAIN и ручная формула из кода cost_seqscan: страницы × seq_page_cost плюс строки × (cpu_tuple_cost плюс цена одного оператора в WHERE).

EXPLAIN SELECT * FROM t WHERE grp = 3;
SELECT relpages * 1.0 + reltuples * (0.01 + 0.0025) AS hand FROM pg_class WHERE relname = 't';

 Seq Scan on t  (cost=0.00..31764.00 rows=99233 width=117)
   Filter: (grp = 3)

   hand
----------
 31764.00

Совпало до сотых: 19 264 + 1 000 000 × 0,0125 = 19 264 + 12 500. Число строк в ответе (rows=99233) в формулу не входит: сканируются все строки, а каждый оператор в WHERE добавляет 0,0025 на каждую из них. Код делает то же самое (costsize.c, REL_18_6):

	disk_run_cost = spc_seq_page_cost * baserel->pages;

	/* CPU costs */
	get_restriction_qual_cost(root, baserel, param_info, &qpqual_cost);

	startup_cost += qpqual_cost.startup;
	cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
	cpu_run_cost = cpu_per_tuple * baserel->tuples;

Страницы планировщик берёт не из pg_class, а из размера файла в момент планирования: RelationGetNumberOfBlocks (tableam.c, строка 667). Из каталога идёт только плотность reltuples / relpages (строки 711–713), и число строк получается как плотность × фактические страницы. Проверим: добавим 100 000 строк и не будем запускать ANALYZE.

INSERT INTO t SELECT g + 1000000, g % 1000000, g % 10, repeat('x', 100) FROM generate_series(1, 100000) g;
SELECT pg_relation_size('t') / 8192 AS pages_now, relpages, reltuples FROM pg_class WHERE relname = 't';
EXPLAIN SELECT * FROM t WHERE grp = 3;

 pages_now | relpages | reltuples
-----------+----------+-----------
     21154 |    19264 |     1e+06

 Seq Scan on t  (cost=0.00..34880.38 rows=108969 width=117)

Каталог не обновился, а стоимость выросла с 31 764 до 34 880,38: 21 154 страницы и 1 098 110 строк (плотность 51,91 строки на страницу × 21 154) — те же 21 154 + 13 726,38. Размер страницы и устройство блока разобраны в статье про страницу 8 КБ.

Опыт 2: Index Scan и корреляция

Возьмём 10 000 строк (1% таблицы) двумя способами: по id, который лежит в куче по порядку, и по k, чьи значения разбросаны по всей таблице. Реально вернётся по 10 000 строк. Bitmap и Seq Scan выключены, чтобы увидеть именно cost_index.

SET enable_seqscan = off;
SET enable_bitmapscan = off;
EXPLAIN SELECT * FROM t WHERE id BETWEEN 1 AND 10000;
EXPLAIN SELECT * FROM t WHERE k BETWEEN 0 AND 9999;
SELECT attname, correlation FROM pg_stats WHERE tablename = 't' AND attname IN ('id', 'k');

 Index Scan using t_id on t  (cost=0.42..495.94 rows=9776 width=117)
 Index Scan using t_k on t  (cost=0.42..32627.04 rows=10219 width=117)

 attname | correlation
---------+-------------
 id      |           1
 k       | 0.007404017

Оценки rows (9 776 и 10 219) чуть расходятся из-за гистограмм, но порядок один, а стоимость различается в 66 раз: 495,94 и 32 627,04. Разница целиком в чтении кучи. cost_index считает две границы и смешивает их по квадрату корреляции (costsize.c, строки 790–797):

	/*
	 * Now interpolate based on estimated index order correlation to get total
	 * disk I/O cost for main table accesses.
	 */
	csquared = indexCorrelation * indexCorrelation;

	run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);

max_IO_cost — каждая выбранная строка лежит на своей странице, и страница читается как случайная: число страниц × random_page_cost. min_IO_cost — строки идут подряд: одно случайное чтение и дальше последовательные, random_page_cost + (страниц − 1) × seq_page_cost. Проверим на цифрах опыта:

SELECT ceil(2*19264.0*10219/(2*19264+10219)) AS ml_pages,
       ceil(2*19264.0*10219/(2*19264+10219)) * 4 AS max_io,
       4 + (ceil(9776/1e6*19264) - 1) AS min_io;

 ml_pages | max_io | min_io
----------+--------+--------
     8077 |  32308 |    192

Для id корреляция 1, csquared = 1, и табличная часть стоит 192 из 495,94. Для k корреляция 0,0074, csquared меньше 0,0001: берётся почти чистый max_IO_cost, 32 308 из 32 627,04. Число страниц (8 077 вместо 10 000) даёт формула Макерта и Лохмана из index_pages_fetched: она учитывает, что часть строк попадёт на уже прочитанную страницу.

Здесь же работает effective_cache_size. Формула берёт b, долю этого параметра на таблицу: это оценка того, сколько страниц вместе кэшируют shared_buffers и кэш ОС. Память параметр не выделяет и кэшем не управляет, он только меняет cost. Тот же запрос по k:

SET effective_cache_size = '4GB';  -- EXPLAIN: cost=0.42..32627.04
SET effective_cache_size = '8MB';  -- EXPLAIN: cost=0.42..39378.67

При 8 МБ формула считает, что повторно прочитанные страницы успеют вытесниться, и индексный план дорожает на 21%. Без enable_* планировщик для k по умолчанию выбирает Bitmap Heap Scan за 16 992,45: он читает кучу в физическом порядке и уходит от дорогой случайности.

Опыт 3: random_page_cost = 4 против 1,1

По документации случайное чтение с диска обычно дороже последовательного больше чем вчетверо, но по умолчанию стоит 4, потому что большинство обращений через индекс считаются попаданиями в кэш; когда данные целиком в кэше, значение можно снижать. Здесь так и есть, поэтому берём 1,1. Прогоним границы выборки по k при обоих значениях и посмотрим, какой узел выбирает планировщик.

for rpc in 4 1.1; do for w in 500 500000; do
  docker exec -i dba-planner-cost-model psql -U postgres -Atq -c \
   "SET max_parallel_workers_per_gather=0; SET random_page_cost=$rpc;
    EXPLAIN SELECT * FROM t WHERE k BETWEEN 0 AND $((w-1))" | sed -n 1p
done; done

Bitmap Heap Scan on t  (cost=13.65..1792.74 rows=510 width=117)
Seq Scan on t  (cost=0.00..34264.00 rows=498080 width=117)
Index Scan using t_k on t  (cost=0.42..567.20 rows=510 width=117)
Index Scan using t_k on t  (cost=0.42..32656.59 rows=498080 width=117)

Строки идут по порядку: 4 и 500 строк, 4 и 500 000, 1,1 и 500, 1,1 и 500 000. При 1,1 оба раза выбран Index Scan. Теперь время обоих планов (EXPLAIN (ANALYZE, BUFFERS), четвёртый прогон подряд, кэш тёплый: данные в shared_buffers или в кэше ОС, диск NVMe в измерении почти не участвует).

w=500, random_page_cost=4:
Bitmap Heap Scan on t  (actual time=0.171..1.033 rows=500.00 loops=1)
  Buffers: shared hit=507
Execution Time: 1.145 ms
w=500, random_page_cost=1.1:
Index Scan using t_k on t  (actual time=0.037..0.731 rows=500.00 loops=1)
  Buffers: shared hit=507
Execution Time: 0.783 ms
w=500000, random_page_cost=4:
Seq Scan on t  (actual time=0.135..48.678 rows=500000.00 loops=1)
  Buffers: shared hit=3399 read=15865
Execution Time: 58.801 ms
w=500000, random_page_cost=1.1:
Index Scan using t_k on t  (actual time=0.025..295.439 rows=500000.00 loops=1)
  Buffers: shared hit=486920 read=14452 written=30
Execution Time: 306.053 ms

Для 500 строк (0,05% таблицы) выигрыш индекса почти нулевой: восемь повторов дали 0,73–1,65 мс у Bitmap Heap Scan и 0,62–0,90 мс у Index Scan. Оба плана трогают одни и те же 507 буферов, разница в пределах шума. Для 500 000 строк (50%) цена ошибки другая: пять повторов Seq Scan — 77–109 мс, Index Scan — 303–374 мс, в три-четыре раза хуже. Индекс с корреляцией 0,007 заходит в кучу почти по разу на строку: 486 920 обращений к буферам против 19 264 страниц таблицы.

Планировщик не ошибся в арифметике, он ошибся в предпосылке: при 1,1 случайная страница почти не дороже последовательной, а при корреляции 0,007 индекс возвращается в кучу за каждой строкой. Точки перелома Seq Scan из бинарного поиска по ширине BETWEEN, это значения доли строк по оценке планировщика: при random_page_cost = 4 — 40,5% (между 404 687 и 405 468 строк), при 1,1 — 55,6% (между 555 468 и 556 250). Точки схемы получены теми же EXPLAIN при разной ширине BETWEEN.

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

• Первое подозрение при «планировщик выбрал странный план» — не статистика, а три числа: фактический размер файла, reltuples и стоимость страницы. Ручной пересчёт Seq Scan занимает минуту и отделяет «ошибся в оценке строк» от «неверные константы».
• random_page_cost = 1.1 при данных в кэше даёт индексу выигрыш в узких выборках или ничего (опыт 3: 507 буферов в обоих планах), а в широких выборках по плохо коррелированному столбцу вредит: перелом на схеме уехал с 40,5% на 55,6%, а при 50% Seq Scan уже в три-четыре раза быстрее.
• effective_cache_size ставь ближе к реальному объёму shared_buffers плюс кэш ОС на этот сервер; он не требует рестарта и не занимает память, но в опыте 2 падение с 4 ГБ до 8 МБ подняло стоимость индексного плана на 21%.
• Проверку делай по EXPLAIN (ANALYZE, BUFFERS) на копии данных: число Buffers покажет, сколько раз план возвращался в кучу.

Чек-лист

1. Посчитай Seq Scan вручную: relpages × seq_page_cost + reltuples × (cpu_tuple_cost + цена условий); расхождение значит, что файл вырос или сжался после ANALYZE либо в условии есть ещё операторы.
2. Для спорного индексного плана выпиши correlation из pg_stats: около нуля означает почти полную цену случайных чтений кучи.
3. Меняя random_page_cost, прогони узкую и широкую выборки с EXPLAIN (ANALYZE, BUFFERS) и сравни время, а не cost.
4. Не считай effective_cache_size настройкой памяти: он описывает ожидаемый кэш, и меняет только оценки.
5. Меняй константы в сессии (SET) до того, как записывать в ALTER SYSTEM или ALTER TABLESPACE.