Планировщик, стоимость, статистика, соединения

Что отвечать

Четыре стадии. Разбор (parse): текст превращается в дерево, проверяются синтаксис и имена объектов по системному каталогу. Переписывание (rewrite): применяются правила и представления - например, обращение к view разворачивается в подзапрос, накладывается защита строк (RLS). Планирование (plan): стоимостный оптимизатор перебирает способы доступа и порядки соединений и выбирает самый дешёвый план. Исполнение (execute): дерево узлов плана гоняется по модели «тяни строку сверху» (volcano), каждый узел запрашивает строки у дочерних по одной. Разделение на стадии объясняет, почему prepared statement может планироваться один раз, а исполняться много.

Что хотят услышать

Senior должен: - назвать четыре стадии по порядку и что делает каждая - привести пример переписывания: разворот view в подзапрос, RLS - объяснить, что планирование отделено от исполнения, и связать это с кешем плана у prepared statement - понимать, что оптимизатор стоимостной: он сравнивает оценки, а не выполняет варианты

Подводные камни

• ✗ Считать, что view это «сохранённый результат» - на самом деле он разворачивается в текст запроса на стадии переписывания
• ✗ Путать планирование и исполнение - planning time и execution time это разные числа в EXPLAIN
• ✗ Думать, что оптимизатор пробует планы вживую - он оценивает их стоимость по формулам

Follow-up

• ? Что происходит со `SELECT` из view на стадии переписывания?
• ? Почему planning time и execution time разделены в выводе EXPLAIN?
• ? Как стадии связаны с кешем плана prepared statement?

Что отвечать

Cost - не время, а безразмерная оценка работы в условных единицах. Базовые кирпичи задаются параметрами: `seq_page_cost` (чтение страницы подряд, опора 1.0), `random_page_cost` (случайное чтение, по умолчанию 4.0), `cpu_tuple_cost`, `cpu_index_tuple_cost`, `cpu_operator_cost`. Стоимость узла складывается из числа страниц на стоимость страницы плюс число строк на стоимость обработки строки. Например seq scan это примерно `relpages * seq_page_cost + reltuples * cpu_tuple_cost`. Оптимизатор сравнивает суммарные cost вариантов и берёт минимальный. Поэтому на SSD имеет смысл снижать `random_page_cost` - случайное чтение там почти как последовательное.

Что хотят услышать

Senior должен: - подчеркнуть, что cost это условные единицы, а не миллисекунды - назвать основные параметры стоимости и их смысл, особенно `random_page_cost` против `seq_page_cost` - воспроизвести формулу seq scan и понимать, что cost растёт от числа страниц и строк - связать настройку `random_page_cost` с типом хранилища (SSD против HDD)

Подводные камни

• ✗ Читать cost как время в миллисекундах - это условные единицы для сравнения планов
• ✗ Оставлять `random_page_cost=4` на SSD - оптимизатор недооценит индексы
• ✗ Сравнивать cost между разными запросами - смысл только в сравнении планов одного запроса

Follow-up

• ? Почему на SSD `random_page_cost` обычно снижают?
• ? Из чего складывается cost узла Seq Scan?
• ? Можно ли сравнивать cost двух разных запросов между собой?

Что отвечать

У каждого узла плана две оценки: startup cost - работа до того, как узел отдаст первую строку, и total cost - работа до последней строки. У seq scan startup почти нулевой: первую строку он отдаёт сразу. У сортировки или хеш-агрегации startup высокий: пока не прочитаны все входные строки, первой на выходе не будет. Это важно для `LIMIT`: при `LIMIT 10` оптимизатор смотрит не на total, а на стоимость получения первых строк, и план с дешёвым стартом (например index scan по нужному порядку) может выиграть у плана с дешёвым total, но дорогим стартом (seq scan плюс сортировка).

Что хотят услышать

Senior должен: - определить startup как «до первой строки», total как «до последней» - привести узлы с высоким стартом: сортировка, хеш-агрегация, материализация - объяснить связь с LIMIT: запрос с лимитом оценивается по стоимости первых строк - показать, почему индекс по нужному порядку бьёт «seq scan плюс sort» именно при наличии LIMIT

Подводные камни

• ✗ Смотреть только на total cost при наличии `LIMIT` - решает стоимость первых строк
• ✗ Думать, что у всех узлов startup около нуля - у sort и hash он высокий
• ✗ Удивляться, что без LIMIT и с LIMIT планировщик выбирает разные планы

Follow-up

• ? Почему `ORDER BY ... LIMIT 10` иногда выбирает индекс, а без LIMIT - seq scan плюс sort?
• ? У каких узлов высокий startup cost?
• ? Как LIMIT меняет оценку плана?

Что отвечать

Планировщик опирается на статистику, которую собирает `ANALYZE` и хранит `pg_statistic` (читаемо через `pg_stats`). Для столбца это доля NULL, число различных значений (`n_distinct`), список самых частых значений (MCV) с их частотами и гистограмма границ для остальных значений, плюс корреляция физического порядка с логическим. Селективность - доля строк, которые пройдут условие, число от 0 до 1. Для `= 'x'` берётся частота из MCV или `1/n_distinct`; для `>`/`<` - доля по гистограмме. Кардинальность узла это селективность на число строк. Ошибка оценки внизу плана распространяется вверх и портит выбор соединений.

Что хотят услышать

Senior должен: - перечислить, что лежит в статистике: null_frac, n_distinct, MCV, гистограмма, корреляция - объяснить, как из этого считается селективность для равенства и для диапазона - сказать, что кардинальность это селективность на число строк, и что ошибки растут снизу вверх - дать рычаги: `ANALYZE`, `default_statistics_target`, и понимать, что устаревшая статистика рушит планы

Подводные камни

• ✗ Забывать `ANALYZE` после массовой загрузки - планировщик считает по пустой или старой статистике
• ✗ Путать селективность с числом строк - это доля от 0 до 1, кардинальность получается умножением
• ✗ Думать, что у планировщика есть статистика по результату соединения - её нет, отсюда рост ошибки вверх по плану

Follow-up

• ? Как из `pg_stats` оценить селективность условия `col > 100`?
• ? Почему ошибка кардинальности усиливается на верхних узлах плана?
• ? Когда поможет увеличение `default_statistics_target`?

Что отвечать

Nested loop: для каждой строки внешнего набора ищем совпадения во внутреннем; выгоден, когда внешних строк мало, а внутренний доступ дёшев (обычно индекс). Hash join: по меньшей таблице строится хеш-таблица в памяти, по большей идёт проба; хорош для крупных наборов без полезного порядка, но требует `work_mem` под хеш и работает только по равенству. Merge join: оба входа сортируются (или приходят уже отсортированными по индексу) и сливаются как два упорядоченных списка; выгоден на больших наборах, когда сортировка дешева или порядок уже есть. Выбор делает стоимость: размеры, наличие индексов, доступная память.

Что хотят услышать

Senior должен: - описать механику всех трёх и типичную нишу каждого - связать hash join с `work_mem`: не хватило памяти - хеш выплеснется на диск в несколько проходов - объяснить, что nested loop с индексом - это про малую внешнюю сторону, а merge join - про уже отсортированные большие входы - понимать, что выбор не жёсткий: оптимизатор сравнивает cost с учётом статистики и памяти

Подводные камни

• ✗ Считать nested loop всегда плохим - на малом внешнем наборе с индексом он лучший
• ✗ Забыть про `work_mem` для hash join - при нехватке хеш идёт на диск и резко дорожает
• ✗ Думать, что merge join бесплатен при наличии ORDER BY - сортировка входов тоже стоит

Follow-up

• ? Что произойдёт с hash join, если хеш-таблица не влезает в `work_mem`?
• ? Когда nested loop оказывается лучшим выбором?
• ? Почему merge join выгоден, если входы уже отсортированы?

Что отвечать

Индексный доступ дешевле только при низкой селективности: когда условие выбирает малую долю строк. Каждая строка, найденная по индексу, обычно требует случайного чтения страницы кучи - а случайное чтение дорогое. Когда условие проходит, скажем, треть таблицы, дешевле прочитать её всю подряд (seq scan), чем делать сотни тысяч случайных обращений по индексу. Есть точка перелома по доле строк, после которой seq scan выигрывает по стоимости. Поэтому на «широких» условиях планировщик сознательно игнорирует индекс, и это правильно. Сдвигают эту точку `random_page_cost`, корреляция данных и наличие index-only scan.

Что хотят услышать

Senior должен: - объяснить причину: индексный доступ платит за случайные чтения кучи, и при высокой доле строк это дороже сплошного чтения - назвать точку перелома по селективности и факторы, которые её двигают (random_page_cost, корреляция, visibility map) - понимать, что выбор seq scan на широком условии это норма, а не ошибка - связать с диагностикой: завышенный `random_page_cost` на SSD заставляет зря избегать индексов

Подводные камни

• ✗ Считать, что наличие индекса обязывает планировщик его использовать - при высокой селективности seq scan дешевле
• ✗ Форсировать индекс через `enable_seqscan=off` в продакшене вместо разбора стоимости
• ✗ Игнорировать корреляцию: при плохой корреляции индексный скан делает ещё больше случайных чтений

Follow-up

• ? От чего зависит доля строк, на которой seq scan обгоняет индекс?
• ? Как `random_page_cost` сдвигает точку перелома?
• ? Почему хорошая корреляция данных удешевляет индексный скан?

Что отвечать

Число вариантов порядка соединений растёт факториально с числом таблиц. До небольшого порога планировщик честно перебирает их динамическим программированием и находит оптимум. Когда таблиц много (больше `geqo_threshold`, по умолчанию 12), полный перебор становится слишком дорогим, и включается генетический оптимизатор GEQO: он ищет хороший, но не гарантированно лучший порядок за разумное время. На число рассматриваемых вариантов влияют `join_collapse_limit` и `from_collapse_limit`: они задают, насколько глубоко разворачивать подзапросы и явные JOIN в общий пул для перебора. Явный порядок JOIN при низком лимите фиксируется как есть.

Что хотят услышать

Senior должен: - объяснить комбинаторный взрыв и переход от точного перебора к GEQO по `geqo_threshold` - понимать роль `join_collapse_limit`/`from_collapse_limit`: они решают, что попадёт в общий пул для перебора порядка - знать побочный эффект: при низком `join_collapse_limit` порядок явных JOIN фиксируется и может стать рычагом ручного управления - видеть компромисс GEQO: меньше времени планирования ценой возможной неоптимальности

Подводные камни

• ✗ Думать, что планировщик всегда находит оптимальный порядок - при GEQO это эвристика
• ✗ Не знать про `join_collapse_limit` и удивляться, почему перестановка JOIN меняет план
• ✗ Бездумно поднимать `geqo_threshold` на запросах с десятками таблиц - время планирования взлетит

Follow-up

• ? Что меняет понижение `join_collapse_limit` до 1?
• ? При каком числе таблиц включается GEQO по умолчанию?
• ? Чем GEQO расплачивается за скорость планирования?

Что отвечать

Обычная статистика собирается по каждому столбцу отдельно и предполагает их независимость. Когда столбцы коррелируют, эта посылка ломается. Классика: `city` и `region` - запрос `WHERE city='Москва' AND region='Московская'` планировщик оценит как произведение двух селективностей и сильно недооценит число строк, потому что на деле эти условия почти дублируют друг друга. Расширенная статистика (`CREATE STATISTICS`) учит планировщик зависимостям: тип `dependencies` ловит функциональные зависимости, `ndistinct` - число различных комбинаций, `mcv` - частые сочетания значений. После неё оценка кардинальности на коррелированных столбцах становится близкой к реальной.

Что хотят услышать

Senior должен: - объяснить корень проблемы: независимость столбцов в обычной статистике и недооценка на коррелированных предикатах - привести понятный пример коррелированной пары и показать, как ошибка ведёт к плохому плану - назвать виды расширенной статистики: dependencies, ndistinct, mcv - и что каждый исправляет - понимать, что статистику надо создать явно и пересобрать `ANALYZE`, сама она не появится

Подводные камни

• ✗ Считать, что планировщик сам учитывает связь столбцов - по умолчанию он считает их независимыми
• ✗ Создать `CREATE STATISTICS` и забыть `ANALYZE` - оценки не обновятся
• ✗ Лепить расширенную статистику на все пары столбцов - она стоит сбора, нужна только там, где есть корреляция и боль

Follow-up

• ? Почему `WHERE city=... AND region=...` недооценивается без расширенной статистики?
• ? Чем тип `dependencies` отличается от `mcv` в `CREATE STATISTICS`?
• ? Что нужно сделать после создания расширенной статистики, чтобы она заработала?

Что отвечать

EXPLAIN показывает план с оценками, EXPLAIN ANALYZE ещё и выполняет запрос, добавляя фактические числа. Главный приём диагностики - сравнивать `rows` оценочные и `actual rows` на каждом узле. Сильное расхождение (на порядок и больше) означает, что планировщик ошибся в кардинальности, и план построен на ложных числах. Дальше смотрят `loops` (узел в nested loop выполняется много раз), `Rows Removed by Filter` (читаем много, выбрасываем много - просится индекс), и `Buffers` (сколько страниц реально прочитано). Узкое место ищут снизу вверх: первая крупная ошибка оценки или самый дорогой по факту узел.

Что хотят услышать

Senior должен: - назвать приём «estimated против actual rows» как первый шаг разбора - читать `loops`, `Rows Removed by Filter`, `Buffers` и понимать, что каждый сигнализирует - искать первопричину снизу вверх, а не хвататься за верхний дорогой узел - различать EXPLAIN (только оценки) и EXPLAIN ANALYZE (реальное выполнение со всеми последствиями, включая запись при DML)

Подводные камни

• ✗ Запускать `EXPLAIN ANALYZE` на INSERT/UPDATE/DELETE без транзакции с откатом - он реально выполнит изменение
• ✗ Смотреть только на total time верхнего узла и не сравнивать оценки с фактом
• ✗ Игнорировать `loops`: дорогой узел внутри nested loop умножается на число итераций

Follow-up

• ? Что значит расхождение между `rows` и `actual rows` в десять раз?
• ? Как безопасно сделать `EXPLAIN ANALYZE` для `UPDATE`?
• ? О чём говорит большое `Rows Removed by Filter`?