Как оптимизатор PostgreSQL выбирает план

— Макс, ты уже говорил, что в некоторых случаях Seq Scan быстрее чем Index Scan! — напомнила Лена на следующий день. — Но везде пишут, что нужно избавляться от Seq Scan. Так как понять, когда Seq Scan хорошо, а когда плохо?

— А давай разберёмся, как Postgres вообще принимает такие решения. — отодвинул кружку с кофе Макс. — В этом нет никакой магии, обычная математика.

1. Как работает оптимизатор

Postgres оценивает планы через стоимость и статистику:

• Стоимость — условная величина, а не миллисекунды. По умолчанию последовательное чтение одной страницы задаёт базовую единицу seq_page_cost = 1; случайное чтение, обработка строк и операторов имеют собственные коэффициенты. Их соотношение описывает модель конкретного сервера лишь приблизительно.

• Статистика: для таблиц это прежде всего количество строк и информация о том, как распределены данные по столбцам:

-- Посмотрим статистику для таблицы orders:
SELECT relpages, reltuples FROM pg_class WHERE relname = 'orders';

→ relpages = 100 (блоков), reltuples = 10000 (строк)

2. Почему Seq Scan иногда быстрее

Пример запроса:

SELECT * FROM orders WHERE status = 'completed';  -- Допустим, 80% от всех строк

Условный Seq Scan:

• Читает все 100 блоков (cost = 100).

• Фильтрует строки в памяти (быстро).

Условный Index Scan:

1. Читает индекс (допустим, 20 блоков, cost = 20).

2. Для найденных ссылок обращается к страницам таблицы:

   • При слабой корреляции это может означать множество разрозненных обращений к одним и тем же или разным страницам.

   • Планировщик оценивает число страниц, кэширование и стоимость случайного доступа; это не равно числу строк, умноженному на seq_page_cost.

Вывод: при возврате 80% таблицы последовательное чтение часто дешевле индексной навигации. Точные числа нужно брать из реального EXPLAIN, а не из этой упрощённой иллюстрации.

Конечно в процессе такого выполнения почти все блоки таблицы окажутся в кэше в памяти, и операция не будет реально эквивалентна чтению 8000 блоков. Но это уже отдельная тема.

— Действительно все просто и логично, — отметила Лена. — Выбирается вариант с наименьшими затратами.

— Да. Оптимизатор учитывает еще множество разных деталей. Например, разницу между последовательным и случайным чтением, корреляцию индекса и т.д.

— А что это такое “корреляция индекса”? — заинтересовалась Лена.

— Это такая статистика, которая показывает, насколько физический порядок строк в таблице соответствует реальному порядку в данных. Это важно, когда есть индекс и выборка идет по диапазону (BETWEEN). Может иметь значения от -1 до 1:

• 1.0 — сильное совпадение физического и возрастающего логического порядка.

• 0.0 — нет корреляции.

• -1.0 — столь же сильный, но обратный порядок.

-- Посмотреть correlation для столбца:
SELECT tablename, attname, correlation FROM pg_stats
WHERE tablename = 'orders' AND attname = 'created_at';

Эта информация используется для оценки стоимости Index Scan:

• Если модуль correlation близок к 1.0, диапазонный Index Scan может читать страницы более последовательно, в прямом или обратном направлении;

• Если correlation ближе к 0.0, то Index Scan потребует много случайных чтений, что повышает cost.

Конечно, на современных SSD разница между последовательным и случайным чтением не такая большая, как на обычных дисках, но она все же есть.

— На физический порядок можно повлиять командой CLUSTER, — добавил Макс. — Она однократно переписывает таблицу по выбранному индексу, требует дополнительное место и эксклюзивную блокировку, а последующие изменения снова размывают порядок. Улучшив корреляцию одного ключа, можно ухудшить другой. Поэтому сначала нужен измеримый выигрыш и отдельный план обслуживания.

— Поняла! — Лена сделала заметку в блокноте. — Значит, главное не слепо гнаться за индексами, а понимать логику оптимизатора.

— Хорошо сказано! — Макс одобрительно кивнул. — Для закрепления предлагаю тебе поизучать статистику для известных тебе таблиц. В pg_stats еще много информации. Готовь вопросы - обсудим.