Базы данных
Планы выполнения: тест с выбором ответа
Суть Синтез в формате multiple-choice по всему юниту execution plans — чтение EXPLAIN ANALYZE, выбор scan и join, каскад ошибок оценки строк, extended statistics и generic-plan trap.
Высота — путь к senior
НольJuniorMiddleSenior
Ты на senior-высоте — в орбитеШесть вопросов, проходящих через весь юнит. Каждый — план, который ты реально читал бы в инциденте: не определение для пересказа, а диагноз, который нужно поставить по выводу, и фикс, который нужно выбрать под нагрузкой.
Цель
Подтверди, что умеешь читать план EXPLAIN ANALYZE, проследить плохую оценку строк вверх до join, который она сломала, и выбрать фикс, бьющий по причине, а не по симптому — синтез, к которому вели семь уроков.
Викторина
Completed
Запрос быстр на staging и медленный в production. Та же схема, те же индексы. EXPLAIN ANALYZE прод-прогона показывает: Nested Loop (actual time=4180..4192 rows=50 loops=1) -> Index Scan on orders (cost=0..3800000 rows=50) (actual time=12..3950 rows=520000 loops=1) -> Index Scan on customers_pkey (actual time=0.002..0.002 rows=1 loops=520000) В чём первопричина?
Heads-up Внутренний — это Index Scan по customers_pkey, индекс есть. Проблема в loops=520000 из-за недооценки внешней стороны, а не в отсутствующем индексе.
Heads-up Форсирование алгоритма (enable_nestloop=off) маскирует симптом. При корректной оценке в 520 000 строк планировщик сам выбрал бы Hash Join. Чини оценку, а не алгоритм.
Heads-up Корректный план на большем объёме масштабируется плавно. Раздувание цикла в 10 400 раз из-за разрыва оценки 50 против 520 000 — это ошибка планировщика, а не линейный рост.
Викторина
Completed
Таблица на 1M строк имеет индекс по status. Запрос WHERE status = 'active' попадает в 400 000 строк (40% таблицы). Планировщик выбирает Seq Scan вместо индекса. Это баг, и почему?
Heads-up Планировщик выбирает по стоимости, а не по наличию индекса. Index Scan платит random_page_cost за каждую совпавшую строку; при 40% это перевешивает один seq scan.
Heads-up Index Only Scan требует всех проецируемых колонок в индексе И all-visible Visibility Map. Ни то, ни другое здесь не подразумевается, и он всё равно затронул бы 40% данных.
Heads-up Seq Scan выигрывает на низкой селективности, а не на размере. Для предиката, попадающего в 100 строк из 1M, индекс кардинально дешевле. Кроссовер задаёт селективность, а не число строк.
Викторина
Completed
Предикат фильтрует по WHERE country='US' AND region='CA' AND status='shipped'. Планировщик прогнозирует 500 строк; узел реально выдаёт 50 000. Одноколоночная statistics свежая. Что чинит оценку?
Heads-up Больший по-колоночный target даёт более тонкие одноколоночные гистограммы и MCV-списки, но планировщик всё равно перемножает P(country) x P(region). Предположение о независимости остаётся — заменяет его только extended statistics.
Heads-up Индекс меняет пути выполнения, а не модель многоколоночной оценки строк. Оценка останется неверной, пока extended statistics не научат планировщик корреляции.
Heads-up Та же проблема на глобальном уровне: тоньше по-колоночная статистика, но межколоночная корреляция всё ещё не смоделирована. CREATE STATISTICS — единственный инструмент, фиксирующий функциональные зависимости.
Викторина
Completed
Параметризованный запрос быстр месяцами, затем P99 прыгает до 4с, а среднее остаётся ~3мс. pg_stat_statements показывает большой stddev_exec_time. EXPLAIN (GENERIC_PLAN) по подготовленной форме показывает: Index Scan using idx_orders_created on orders Filter: ((workspace_id = $1) AND (status = $2)) Что произошло и каков немедленный фикс?
Heads-up Составной индекс всё ещё есть — generic plan просто не может его использовать, не зная значения параметров на этапе планирования. Чинит перепланирование на каждое выполнение, а не переиндексация.
Heads-up Рост данных даёт постепенную деградацию, а не бимодальную задержку (низкое среднее, высокий stddev), появляющуюся после фиксированного числа выполнений. Сигнатура прямо указывает на переключение на generic plan.
Heads-up Пересоздание перезапускает 5-кратный цикл custom plan с нуля — оно не даёт устойчивой регрессии generic plan. Скачок stddev — это generic-plan trap.
Викторина
Completed
B2B-дашборд деградирует каждое утро понедельника после ночного воскресного batch-импорта 20M строк, затем сам восстанавливается ко вторнику. В чём причина и устойчивый фикс?
Heads-up Самовосстановление ко вторнику — главный признак: autovacuum в итоге запускает ANALYZE и оценки исправляются сами. Разница в трафике не лечилась бы автоматически за ночь.
Heads-up Конкуренция за блокировки проявляется как заблокированные запросы в pg_stat_activity и снимается при commit транзакции — а не сутки спустя. Паттерн соответствует устаревшей statistics, а не блокировкам.
Heads-up Полезность индекса задаёт селективность, а не размер, и перестройки не объяснили бы самовосстановление во вторник. Первопричина — свежесть statistics, чинится через ANALYZE в batch hook.
Викторина
Completed
EXPLAIN (ANALYZE, BUFFERS) на Hash Join показывает Hash Batches: 64 и Sort Method: external merge Disk: 450MB на сортировке под ним. Среднее время высокое, но reads — в основном shared hit. В чём узкое место и точечный фикс?
Heads-up Buffers — в основном shared hit, значит heap-страницы закэшированы — это не проблема холодного IO. Время съедает spill на диск из-за переполнения work_mem.
Heads-up Индекс с подходящей сортировкой мог бы включить Merge Join, но немедленный измеренный симптом — spill work_mem на обоих узлах. Размер work_mem чинит spill напрямую.
Heads-up Hash Batches — это число дисковых партиционированных проходов, когда хэш-таблица не влезает в work_mem, а не счётчик цикла. Фикс — больше work_mem, а не переписывание.
Итог
Сквозная линия юнита — одна дисциплина чтения: на каждом узле плана сравнивай rows-estimated с actual rows, найди, где открывается разрыв, и проследи его вверх — плохая оценка каскадирует в неверный выбор scan, неверный алгоритм join и неверный метод сортировки над ней. Выбор scan следует за селективностью, выбор join — за оценкой внешней стороны, а устойчивые фиксы бьют по причине: свежая statistics через ANALYZE, extended statistics для коррелированных колонок, force_custom_plan для перекошенных prepared statements и cost-константы под SSD. Форсирование алгоритма или перестройка индекса лечат симптом; починка оценки исправляет весь план.