Глава 45. Единый алгоритм оптимизации (капстоун) · PostgreSQL

45.1 От симптома к механизму

Оптимизация начинается не с запроса, а с вопроса «что именно медленно». Первое ветвление - масштаб проблемы:

Тормозит вся база - смотри систему: горизонт транзакции, bloat, блокировки, нехватка памяти, autovacuum. Это часть про эксплуатацию и наблюдаемость.
Тормозит конкретный запрос - смотри его план: статистика, выбор метода доступа, соединения, индексы. Это часть про планировщик.

Спутать эти ветки - значит чинить не то. Медленный запрос на здоровой базе лечат планом; быстрый запрос на больной базе бесполезно оптимизировать, пока горизонт держит мусор. Алгоритм ниже разводит эти ветки явно.

45.2 Ветка «вся база»: горизонт, мусор, блокировки

Когда деградация общая, идёшь по системным представлениям в определённом порядке:

Кто держит горизонт. pg_stat_activity на долгие транзакции и idle in transaction с большим xact_age. Долгий горизонт не даёт vacuum чистить мусор - и дальше всё медленнее.
Сколько мусора. pg_stat_user_tables на n_dead_tup и last_autovacuum. Раздутая таблица читается дольше, потому что живые строки разбросаны среди мёртвых.
Не растёт ли возраст заморозки. age(relfrozenxid) близко к порогу - впереди агрессивный autovacuum или хуже.
Нет ли блокировок. Цепочка pg_locks плюс pg_stat_activity: кто кого ждёт, кто blocker.

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

45.3 Ветка «один запрос»: статистика, план, индексы

Когда тормозит конкретный запрос, рабочий инструмент - EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). Алгоритм чтения:

Сравни estimated и actual rows. Сильное расхождение - планировщик ошибся в кардинальности, обычно из-за устаревшей или недостаточной статистики. Лечение - ANALYZE, расширенная статистика.
Найди, где уходит время. Самый дорогой узел, неожиданный Seq Scan на большой таблице, Sort с проливом на диск.
Проверь sargability. Предикат вида WHERE col::date = ... или функция над колонкой убивают индекс - условие надо переписать так, чтобы индекс брался.
Проверь, тот ли метод доступа и тип индекса. btree на полнотекстовом LIKE '%x%' бесполезен - нужен GIN; отсутствие составного индекса под ORDER BY ... LIMIT даёт Sort с большим cost.

Ошибка кардинальности распространяется снизу вверх по дереву плана: неверная оценка на нижнем узле тянет неверный выбор соединения на верхнем. Поэтому читают план с листьев.

45.4 Капстоун: пять подсаженных проблем

В капстоуне демо-база специально испорчена пятью независимыми проблемами, по одной на каждую крупную тему курса. Твоя задача - найти и починить каждую, обосновав диагноз данными:

Долгая idle-in-transaction держит горизонт - растут bloat и age(relfrozenxid), vacuum не убирает мёртвые версии.
«Плохой» запрос с кастом ::date и устаревшей статистикой - Seq Scan вместо Index Scan.
Отсутствует составной индекс под ORDER BY ... LIMIT - Bitmap плюс Sort с гигантским cost.
Дублирующий неиспользуемый индекс - раздувает запись, не давая выигрыша на чтении.
Неверный тип индекса - btree вместо GIN на полнотекстовом поиске.

Каждая чинится механизмом из своей части: закрыть транзакцию и дать vacuum (часть про очистку), ANALYZE и переписать предикат (часть про планировщик), создать правильный индекс и удалить лишний (часть про индексы).

45.4.1 Копнуть глубже: как капстоун проверяется

Грейдер капстоуна - это набор SQL-ассертов поверх системных представлений и JSON-планов, по одному на каждую проблему:

горизонт схлопнулся и vacuum убрал мусор: n_dead_tup = 0 на целевой таблице после закрытия транзакции и vacuum;
целевой запрос показывает ожидаемый node type (Index Scan, не Seq Scan) и cost ниже порога - проверяется парсингом EXPLAIN (FORMAT JSON);
pg_stat_user_indexes подтверждает, что новый индекс используется, а у удалённого дубля сканов больше нет;
полнотекстовый запрос на GIN укладывается в заданное время и берёт Bitmap Index Scan, а не Seq Scan.

Это та же методология, которой ты пользовался всю книгу: не «поверь, что починил», а «покажи на представлении и на плане».

45.5 Алгоритм одной картинкой

Сведём всё в блок-схему принятия решений. Это и есть итог книги - карта, по которой идут от жалобы к причине.

Каждый узел этой схемы - отдельная глава курса. Капстоун проводит тебя по обеим веткам сразу. Полный сценарий и автопроверка - в лабе.

Уроки в sandbox

lab-45.1. Капстоун: расследование деградации прода

Перед тобой демо-база с пятью подсаженными проблемами. Пройди единый алгоритм: продиагностируй через системные представления и EXPLAIN, почини каждую проблему, обоснуй диагноз данными. Для каждой сначала предскажи, что увидишь, потом проверь.

Ветка «вся база»: найди, кто держит горизонт - SELECT pid, state, now() - xact_start AS age FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;. Затем посмотри мусор: SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;.
Закрой удерживающую транзакцию (в её сессии COMMIT, либо SELECT pg_terminate_backend(pid)), выполни VACUUM целевой таблицы и подтверди: n_dead_tup упал до 0.
Ветка «один запрос»: возьми запрос с кастом WHERE booked_at::date = ..., посмотри план - Seq Scan. Выполни ANALYZE bookings; и перепиши предикат на sargable диапазон booked_at >= ... AND booked_at < ...; план переходит в Index Scan.
Отсутствующий составной индекс: найди запрос с ORDER BY ... LIMIT, дающий Sort с большим cost; создай составной индекс под него и подтверди Index Scan с малым cost через EXPLAIN (FORMAT JSON).
Лишний индекс: найди дублирующий индекс с нулём сканов в pg_stat_user_indexes (idx_scan = 0) и удали его.
Неверный тип индекса: на полнотекстовом поиске btree даёт Seq Scan; создай GIN-индекс и подтверди Bitmap Index Scan и падение времени.

sandbox с автопроверкой - открыть в песочнице

Резюме

Оптимизация начинается с вопроса «что именно медленно»: первое ветвление - вся база (система) или один запрос (план).
Ветка «вся база»: по порядку проверяешь горизонт (долгие транзакции, idle in transaction), мусор (n_dead_tup), возраст заморозки и блокировки.
Ветка «один запрос»: EXPLAIN ANALYZE, сравнение estimated и actual rows, поиск дорогого узла, проверка sargability и правильности индекса/метода доступа.
Ошибка кардинальности распространяется снизу вверх по дереву плана, поэтому план читают с листьев.
Капстоун - пять независимых подсаженных проблем (горизонт, устаревшая статистика, отсутствующий и лишний индексы, неверный тип индекса), по одной на крупную тему.
Грейдер проверяет каждую починку через SQL-ассерты поверх системных представлений и JSON-планов - та же методология «покажи на представлении», что всю книгу.

Контрольные вопросы

Почему первый шаг оптимизации - различить «вся база» и «один запрос»?
Показать ответ
Потому что эти две ситуации лечат разными средствами, и спутать их - значит чинить не то. Если деградация общая (горизонт держит мусор, блокировки, нехватка памяти), оптимизация отдельного запроса бесполезна - он будет медленным на больной базе всё равно. Если же база здорова, а тормозит конкретный запрос, систему трогать незачем - проблема в его плане. Поэтому алгоритм сразу разводит две ветки: системную диагностику и разбор плана.
В каком порядке проверять систему, когда тормозит вся база?
Показать ответ
Сначала кто держит горизонт - pg_stat_activity на долгие транзакции и idle in transaction с большим xact_age, потому что долгий горизонт запускает цепочку проблем. Затем сколько мусора - n_dead_tup и last_autovacuum в pg_stat_user_tables. Затем возраст заморозки age(relfrozenxid) - не подходит ли к порогу. И блокировки - pg_locks плюс pg_stat_activity, кто кого ждёт. Каждый шаг опирается на механизм из своей части курса.
Почему план EXPLAIN читают с листьев (снизу вверх)?
Показать ответ
Потому что ошибка кардинальности распространяется снизу вверх: неверная оценка строк на нижнем узле заставляет планировщик неверно выбрать соединение и порядок на верхних узлах. Если читать сверху, ты видишь следствие (плохой join), а не причину (плохую оценку на скане). Начав с листьев, находишь первый узел, где estimated сильно разошёлся с actual, - это и есть корень, а всё выше лишь его последствия.
Какие пять проблем подсажены в капстоуне и к каким частям курса они относятся?
Показать ответ
(1) Долгая idle-in-transaction держит горизонт - часть про vacuum/очистку; (2) запрос с кастом ::date и устаревшей статистикой даёт Seq Scan - часть про планировщик и статистику; (3) отсутствует составной индекс под ORDER BY ... LIMIT, отсюда Sort с большим cost - часть про проектирование индексов; (4) дублирующий неиспользуемый индекс раздувает запись - тоже индексы; (5) btree вместо GIN на полнотекстовом поиске - выбор метода доступа. По одной проблеме на крупную тему.
Как капстоун проверяет, что проблема действительно починена, а не «вроде стало быстрее»?
Показать ответ
Набором SQL-ассертов поверх системных представлений и JSON-планов. Горизонт - n_dead_tup = 0 на целевой таблице после vacuum. Запрос - ожидаемый node type (Index Scan) и cost ниже порога из EXPLAIN (FORMAT JSON). Индексы - pg_stat_user_indexes подтверждает использование нового и отсутствие сканов у удалённого. Полнотекст - Bitmap Index Scan на GIN и время в пределах лимита. Это методология «покажи на представлении», а не «поверь на слово».

45.1 От симптома к механизму

Оптимизация начинается не с запроса, а с вопроса «что именно медленно». Первое ветвление - масштаб проблемы:

Тормозит вся база - смотри систему: горизонт транзакции, bloat, блокировки, нехватка памяти, autovacuum. Это часть про эксплуатацию и наблюдаемость.
Тормозит конкретный запрос - смотри его план: статистика, выбор метода доступа, соединения, индексы. Это часть про планировщик.

45.2 Ветка «вся база»: горизонт, мусор, блокировки

Когда деградация общая, идёшь по системным представлениям в определённом порядке:

Кто держит горизонт. pg_stat_activity на долгие транзакции и idle in transaction с большим xact_age. Долгий горизонт не даёт vacuum чистить мусор - и дальше всё медленнее.
Сколько мусора. pg_stat_user_tables на n_dead_tup и last_autovacuum. Раздутая таблица читается дольше, потому что живые строки разбросаны среди мёртвых.
Не растёт ли возраст заморозки. age(relfrozenxid) близко к порогу - впереди агрессивный autovacuum или хуже.
Нет ли блокировок. Цепочка pg_locks плюс pg_stat_activity: кто кого ждёт, кто blocker.

45.3 Ветка «один запрос»: статистика, план, индексы

Когда тормозит конкретный запрос, рабочий инструмент - EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). Алгоритм чтения:

Сравни estimated и actual rows. Сильное расхождение - планировщик ошибся в кардинальности, обычно из-за устаревшей или недостаточной статистики. Лечение - ANALYZE, расширенная статистика.
Найди, где уходит время. Самый дорогой узел, неожиданный Seq Scan на большой таблице, Sort с проливом на диск.
Проверь sargability. Предикат вида WHERE col::date = ... или функция над колонкой убивают индекс - условие надо переписать так, чтобы индекс брался.
Проверь, тот ли метод доступа и тип индекса. btree на полнотекстовом LIKE '%x%' бесполезен - нужен GIN; отсутствие составного индекса под ORDER BY ... LIMIT даёт Sort с большим cost.

45.4 Капстоун: пять подсаженных проблем

Долгая idle-in-transaction держит горизонт - растут bloat и age(relfrozenxid), vacuum не убирает мёртвые версии.
«Плохой» запрос с кастом ::date и устаревшей статистикой - Seq Scan вместо Index Scan.
Отсутствует составной индекс под ORDER BY ... LIMIT - Bitmap плюс Sort с гигантским cost.
Дублирующий неиспользуемый индекс - раздувает запись, не давая выигрыша на чтении.
Неверный тип индекса - btree вместо GIN на полнотекстовом поиске.

45.4.1 Копнуть глубже: как капстоун проверяется

горизонт схлопнулся и vacuum убрал мусор: n_dead_tup = 0 на целевой таблице после закрытия транзакции и vacuum;
целевой запрос показывает ожидаемый node type (Index Scan, не Seq Scan) и cost ниже порога - проверяется парсингом EXPLAIN (FORMAT JSON);
pg_stat_user_indexes подтверждает, что новый индекс используется, а у удалённого дубля сканов больше нет;
полнотекстовый запрос на GIN укладывается в заданное время и берёт Bitmap Index Scan, а не Seq Scan.

45.5 Алгоритм одной картинкой

Сведём всё в блок-схему принятия решений. Это и есть итог книги - карта, по которой идут от жалобы к причине.

Уроки в sandbox

lab-45.1. Капстоун: расследование деградации прода

Ветка «вся база»: найди, кто держит горизонт - SELECT pid, state, now() - xact_start AS age FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY age DESC;. Затем посмотри мусор: SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;.
Закрой удерживающую транзакцию (в её сессии COMMIT, либо SELECT pg_terminate_backend(pid)), выполни VACUUM целевой таблицы и подтверди: n_dead_tup упал до 0.
Ветка «один запрос»: возьми запрос с кастом WHERE booked_at::date = ..., посмотри план - Seq Scan. Выполни ANALYZE bookings; и перепиши предикат на sargable диапазон booked_at >= ... AND booked_at < ...; план переходит в Index Scan.
Отсутствующий составной индекс: найди запрос с ORDER BY ... LIMIT, дающий Sort с большим cost; создай составной индекс под него и подтверди Index Scan с малым cost через EXPLAIN (FORMAT JSON).
Лишний индекс: найди дублирующий индекс с нулём сканов в pg_stat_user_indexes (idx_scan = 0) и удали его.
Неверный тип индекса: на полнотекстовом поиске btree даёт Seq Scan; создай GIN-индекс и подтверди Bitmap Index Scan и падение времени.

sandbox с автопроверкой - открыть в песочнице

Резюме

Оптимизация начинается с вопроса «что именно медленно»: первое ветвление - вся база (система) или один запрос (план).
Ветка «вся база»: по порядку проверяешь горизонт (долгие транзакции, idle in transaction), мусор (n_dead_tup), возраст заморозки и блокировки.
Ветка «один запрос»: EXPLAIN ANALYZE, сравнение estimated и actual rows, поиск дорогого узла, проверка sargability и правильности индекса/метода доступа.
Ошибка кардинальности распространяется снизу вверх по дереву плана, поэтому план читают с листьев.
Капстоун - пять независимых подсаженных проблем (горизонт, устаревшая статистика, отсутствующий и лишний индексы, неверный тип индекса), по одной на крупную тему.
Грейдер проверяет каждую починку через SQL-ассерты поверх системных представлений и JSON-планов - та же методология «покажи на представлении», что всю книгу.

Контрольные вопросы

Почему первый шаг оптимизации - различить «вся база» и «один запрос»?
Показать ответ
Потому что эти две ситуации лечат разными средствами, и спутать их - значит чинить не то. Если деградация общая (горизонт держит мусор, блокировки, нехватка памяти), оптимизация отдельного запроса бесполезна - он будет медленным на больной базе всё равно. Если же база здорова, а тормозит конкретный запрос, систему трогать незачем - проблема в его плане. Поэтому алгоритм сразу разводит две ветки: системную диагностику и разбор плана.
В каком порядке проверять систему, когда тормозит вся база?
Показать ответ
Сначала кто держит горизонт - pg_stat_activity на долгие транзакции и idle in transaction с большим xact_age, потому что долгий горизонт запускает цепочку проблем. Затем сколько мусора - n_dead_tup и last_autovacuum в pg_stat_user_tables. Затем возраст заморозки age(relfrozenxid) - не подходит ли к порогу. И блокировки - pg_locks плюс pg_stat_activity, кто кого ждёт. Каждый шаг опирается на механизм из своей части курса.
Почему план EXPLAIN читают с листьев (снизу вверх)?
Показать ответ
Потому что ошибка кардинальности распространяется снизу вверх: неверная оценка строк на нижнем узле заставляет планировщик неверно выбрать соединение и порядок на верхних узлах. Если читать сверху, ты видишь следствие (плохой join), а не причину (плохую оценку на скане). Начав с листьев, находишь первый узел, где estimated сильно разошёлся с actual, - это и есть корень, а всё выше лишь его последствия.
Какие пять проблем подсажены в капстоуне и к каким частям курса они относятся?
Показать ответ
(1) Долгая idle-in-transaction держит горизонт - часть про vacuum/очистку; (2) запрос с кастом ::date и устаревшей статистикой даёт Seq Scan - часть про планировщик и статистику; (3) отсутствует составной индекс под ORDER BY ... LIMIT, отсюда Sort с большим cost - часть про проектирование индексов; (4) дублирующий неиспользуемый индекс раздувает запись - тоже индексы; (5) btree вместо GIN на полнотекстовом поиске - выбор метода доступа. По одной проблеме на крупную тему.
Как капстоун проверяет, что проблема действительно починена, а не «вроде стало быстрее»?
Показать ответ
Набором SQL-ассертов поверх системных представлений и JSON-планов. Горизонт - n_dead_tup = 0 на целевой таблице после vacuum. Запрос - ожидаемый node type (Index Scan) и cost ниже порога из EXPLAIN (FORMAT JSON). Индексы - pg_stat_user_indexes подтверждает использование нового и отсутствие сканов у удалённого. Полнотекст - Bitmap Index Scan на GIN и время в пределах лимита. Это методология «покажи на представлении», а не «поверь на слово».