Индекс есть, Seq Scan остался: полевая диагностика PostgreSQL

Симптом: в таблице уже есть индекс на created_at, но запрос за один день идёт через Seq Scan; после создания ещё одного индекса ситуация не изменилась. Цена такого ремонта — раздутая схема, медленнее запись и отсутствие объяснения в следующем инциденте. В полевом разборе не будем угадывать «правильный индекс». Сначала отделим три причины: условие выбирает слишком много строк, статистика неверно оценивает долю или сам предикат не совпадает с формой ключа.

Нужен один доказуемый маршрут: точный SQL → EXPLAIN (ANALYZE, BUFFERS) → первое расхождение estimate/fact → проверка статистики и формы WHERE → минимальная правка → тот же план повторно. Это не требует специального ORM и остаётся полезным, когда query пришёл из PHP, фоновой задачи или admin-экспорта. В 2019 году достаточно видеть серверный SQL и параметры; не нужно придумывать платформенные метрики, чтобы не делать индекс вслепую.

До индекса фиксируем четыре факта

Первая ловушка — знать только имя индекса. Для расследования нужны: точный индексный ключ и predicate, точное условие запроса с типами параметров, фактическая доля результата и свежесть статистики. \d в psql удобен, но SQL-проверка переносимее: pg_indexes показывает определение, pg_stats — доступную статистику. Это не делает внутренние каталоги читабельным API для приложения; это инструменты диагностики для инженера, который должен сопоставить запись с планом.

-- Инвентаризация перед изменением DDL.
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'p17_sql_index_fixture'
  AND tablename = 'work_orders';

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'p17_sql_index_fixture'
  AND tablename = 'work_orders';

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM p17_sql_index_fixture.work_orders
WHERE created_at::date = DATE '2019-07-10';

Последний запрос нарочно неудобен для обычного индекса на created_at: в условии написано выражение created_at::date, а не исходная колонка. Не делайте вывод по одной строке плана заранее. Сначала сохраните plan и rows. Затем спросите, какой контракт у бизнес-условия: нам нужна календарная дата в timestamp without time zone, или точный диапазон в определённой временной зоне? От ответа зависит корректная перепись, а не только скорость.

Эта карта не заменяет участие владельца данных. Например, «частый state» может быть следствием нового сценария, а не статистической ошибки. В таком случае сначала проверить продуктовую нагрузку: правда ли экрану нужна вся выборка или UI перестал пагинировать. Индекс решает путь доступа к уже запрошенным строкам; он не превращает экспорт миллиона записей в список из десяти.

Форма предиката: range чаще честнее, чем cast

Если created_at имеет тип timestamp without time zone и задача — один календарный день, диапазон сохраняет исходную колонку слева от сравнения. Это обычно проще сопоставить с B-tree индексом на created_at, чем выражение created_at::date. При этом диапазон не должен быть механической заменой: для timestamptz границы дня выбирают в явной бизнес-временной зоне. Сначала фиксируем семантику времени, потом меняем SQL.

-- Для timestamp without time zone: полуоткрытый диапазон одного дня.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM p17_sql_index_fixture.work_orders
WHERE created_at >= timestamp '2019-07-10 00:00:00'
  AND created_at <  timestamp '2019-07-11 00:00:00';

-- Альтернатива только для действительно устойчивого выражения в запросах:
CREATE INDEX work_orders_created_date_idx
ON p17_sql_index_fixture.work_orders ((created_at::date));

Expression index — не бесплатная оптимизация. PostgreSQL хранит вычисленное выражение и поддерживает его при вставках и изменениях. Он уместен, когда выражение является стабильным контрактом многих запросов и переписать предикат нельзя или нельзя без потери смысла. Если нужен только день из timestamp, диапазон часто проще читать, покрывает один интервал и не требует дублировать вычисленное значение. Решение фиксируем рядом с реальным SQL и его планом, а не только в миграции.

В этой задаче нет реального запуска PostgreSQL: psql не найден и тестовая база не предоставлена. Примеры выше — SQL-фикстуры, которые следует выполнять на disposable-контуре PostgreSQL 11 после создания схемы из практической статьи. В них нет заявленных rows, времени и названия узла: результат должен появиться из конкретного сервера вместе с его настройками. Это ограничение важнее красивого, но вымышленного Index Scan.

Частичный индекс: планировщик должен доказать условие

Частичный индекс хранит только строки, удовлетворяющие своему predicate. Это полезно, когда интересующая нагрузка постоянно работает с небольшой, заранее понятной частью данных. Но PostgreSQL использует такой индекс только когда при планировании может распознать, что WHERE запроса математически включает predicate индекса. Система не пытается доказывать произвольную эквивалентность всех возможных SQL-выражений. Поэтому похожий человеку текст не всегда является подходящим текстом для планировщика.

CREATE INDEX work_orders_waiting_created_idx
ON p17_sql_index_fixture.work_orders (created_at)
WHERE state = 'waiting';

-- Условие явно включает predicate: кандидат для partial index.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM p17_sql_index_fixture.work_orders
WHERE state = 'waiting'
  AND created_at >= timestamp '2019-07-10 00:00:00'
  AND created_at <  timestamp '2019-07-11 00:00:00';

-- Не считайте заранее эквивалентным prepared SQL с неизвестным значением.
PREPARE orders_by_state(text) AS
SELECT id FROM p17_sql_index_fixture.work_orders
WHERE state = $1;

В последнем примере нет утверждения «partial index никогда не используется с PREPARE». Документация говорит точнее: сопоставление происходит при планировании, а параметризованная оговорка не может в общем случае доказать predicate, который должен быть истинным для всех возможных значений. Поэтому до DDL нужно снять настоящий план применения с реальными параметрами и режимом планирования вашего клиента. Если общее условие не доказуемо, partial index — неправильная ставка для этого маршрута, даже если для одного значения он выглядит заманчиво.

Когда проблема в статистике, а не в ключе

Если выражение и key совпадают, но estimate всё равно сильно расходится с actual, возвращаемся к данным. ANALYZE хранит приблизительные частые значения, гистограммы и distinct-оценки; после изменений они могут устареть, а статистика одного столбца не знает взаимосвязь с другим. Сначала запускаем целевой ANALYZE в согласованное время, затем повторяем один и тот же план. Не смешиваем этот шаг с созданием нового индекса, иначе исчезает возможность понять, что действительно изменило выбор.

ANALYZE p17_sql_index_fixture.work_orders;

-- Только если есть доказанное расхождение на двух связанных равенствах:
CREATE STATISTICS work_orders_state_amount_stats (dependencies)
ON state, amount
FROM p17_sql_index_fixture.work_orders;

ANALYZE p17_sql_index_fixture.work_orders;

Расширенная статистика также имеет границы. В PostgreSQL 11 functional dependencies применяются к простым equality-условиям с константами; они не обещают исправить все range, LIKE, выражения или сравнения столбцов между собой. Это достаточная причина не добавлять объект «на всякий случай». В отчёте расследования показывают исходный и повторный plan, точное условие, estimate/actual и действие с индексом. Если эффект не подтверждён, объект и гипотеза не получают статуса решения.

Полевой маршрут на один запрос

1. Взять SQL из реального места вызова вместе с типами и значениями параметров. Указать, сколько строк требуется пользователю и есть ли LIMIT, сортировка или пагинация.
2. Снять EXPLAIN (ANALYZE, BUFFERS) на безопасном контуре. Сохранить plan, версию PostgreSQL и важные cost-настройки; не переносить в отчёт вымышленные цифры.
3. Найти первый узел, где estimate rows расходится с actual rows. Если расхождения нет, сначала признать выбранный Seq Scan или Bitmap путь обоснованным.
4. Сверить Index Cond, Filter, pg_indexes.indexdef и точную форму WHERE. Проверить cast, функцию, operator, составной ключ и predicate partial index.
5. Проверить pg_stats, выполнить целевой ANALYZE и повторить тот же plan. При доказанной корреляции рассмотреть ровно одну подходящую statistics object.
6. Сделать минимальную правку: семантически верный range, expression index или иной ключ для подтверждённой нагрузки. Сравнить не только имя узла, но rows, loops, buffers, время и цену записи.

Что нельзя обещать по одному плану

• Нельзя обещать вечный Index Scan: данные, параметры, cache и настройки меняются, а планировщик вправе выбрать другой путь.
• Нельзя считать EXPLAIN ANALYZE безопасной сухой проверкой: он исполняет statement и может заметно нагрузить сервер.
• Нельзя подменять семантику времени быстрым cast/range. Для timestamptz календарные границы принадлежат выбранной бизнес-временной зоне.
• Нельзя добавлять partial/expressional index без оценки стоимости обновлений, размера и устойчивости реального query shape.

Итог

Когда индекс есть, а Seq Scan остался, это не повод переписывать схему наугад. Проверяем селективность, точность estimate, свежесть статистики и форму предиката. Условие created_at::date, частый state и недоказуемый predicate partial index — три разные причины с разными исправлениями. Сохранённый план и одна проверенная правка дают повторяемый диагноз; серия DDL без этого только прячет проблему под новыми именами.

Проверяемые источники

• PostgreSQL 11: Using EXPLAIN — стоимости, дерево плана, реальные строки и время в EXPLAIN ANALYZE; значения actual time и rows даны в среднем на один loop
• PostgreSQL 11: EXPLAIN — ANALYZE действительно выполняет statement, BUFFERS выводит статистику буферов; для изменяющих запросов документация рекомендует транзакцию с rollback
• PostgreSQL 11: Statistics Used by the Planner — селективность оценивается по приблизительной статистике; pg_stats удобнее прямого чтения pg_statistic, а корреляцию столбцов не ловят обычные одноколоночные статистики
• PostgreSQL 11: ANALYZE — ANALYZE собирает приблизительную выборку, обновляет статистику для планировщика и позволяет повышать target ценой времени и места
• PostgreSQL 11: Indexes on Expressions — выражение в условии требует индекса на том же выражении; такие индексы дороже поддерживать при INSERT и non-HOT UPDATE
• PostgreSQL 11: Partial Indexes — условие запроса должно доказуемо включать predicate индекса; распознавание ограничено и происходит при планировании, а не после подстановки строк на исполнении
• PostgreSQL 11: Index Types — B-tree покрывает распространённые сравнения равенства и диапазона; возможность использовать индекс зависит от оператора и формы условия