SQL-индекс не ускорил запрос: начинаем с плана, а не с CREATE INDEX

Симптом знакомый: на таблицу добавили индекс, запрос остался медленным, а в плане по-прежнему виден Seq Scan. Цена ошибки — лишний индекс на каждой вставке и обновлении, но без сокращения времени ответа. Проблема не в том, что PostgreSQL «не заметил» DDL. Планировщик мог посчитать последовательное чтение честно дешевле. До следующего CREATE INDEX надо увидеть, сколько строк условие действительно оставляет и на каком узле теряется время.

Практика ниже строит минимальный маршрут для PostgreSQL 11: воспроизводим перекос значений, запускаем два одинаково написанных запроса с разной селективностью, читаем оценку и факт, затем выбираем действие. Это не рассказ о волшебном покрывающем индексе и не обещание одинакового плана на любом сервере. План зависит от размера строк, cache, стоимости I/O, версии, статистики и настроек. Нам нужен не узнаваемый текст узла, а проверяемая причина выбора.

Индекс — вариант доступа, а не обязательный маршрут

B-tree помогает быстро найти небольшую часть таблицы по подходящему оператору. Но после поиска по вторичному индексу серверу часто надо читать строки таблицы, проверять видимость и возвращать данные. Когда условие оставляет почти все строки, такой обход превращается в много точечных обращений и может стоить дороже одного последовательного прохода. Поэтому отсутствие Index Scan не доказывает неисправность индекса и не является само по себе bug report.

Отделим две проверки. Первая — подходит ли условие к ключу и типу индекса: сравнение = или диапазон по B-tree обычно дают планировщику кандидата. Вторая — выгоден ли кандидат при текущем распределении данных и нужных колонках. Новичок часто останавливается на первой: «столбец проиндексирован». В работе важнее вторая: «какую долю таблицы вернёт конкретное значение и сколько heap-страниц придётся достать».

Таблица не говорит «всегда перепиши запрос». Например, частый статус может быть действительно нужен для выгрузки почти всех заказов. Тогда правильный результат расследования — признать последовательный проход нормальным и обсуждать пакетную обработку, ограничение выборки или отдельную витрину. Техническое решение начинается с цены операции, а не с желания увидеть слово Index.

Фикстура: сначала собираем наблюдение

Ниже SQL-фикстура для отдельной disposable-базы PostgreSQL 11. Она создаёт миллион строк: 990 000 со статусом ready и 10 000 со статусом waiting, строит два B-tree индекса и запускает три EXPLAIN (ANALYZE, BUFFERS). Она намеренно не содержит «ожидаемый план» и миллисекунды: после запуска их должен записать тот сервер, который будет обслуживать запрос. На этой машине автор не запускал PostgreSQL: бинарник psql и подключение к базе недоступны. Код — воспроизводимая инструкция, не замаскированный отчёт о прогоне.

-- Выполнять только в отдельной disposable-базе PostgreSQL 11.
-- Скрипт создаёт собственную схему и не содержит DELETE/DROP.
CREATE SCHEMA p17_sql_index_fixture;
SET search_path TO p17_sql_index_fixture;

CREATE TABLE work_orders (
  id bigint PRIMARY KEY,
  state text NOT NULL,
  created_at timestamp NOT NULL,
  amount integer NOT NULL
);

INSERT INTO work_orders (id, state, created_at, amount)
SELECT n,
       CASE WHEN n % 100 = 0 THEN 'waiting' ELSE 'ready' END,
       timestamp '2019-07-01 00:00:00' + (n % 31) * interval '1 day',
       100 + (n % 500)
FROM generate_series(1, 1000000) AS n;

CREATE INDEX work_orders_state_idx ON work_orders (state);
CREATE INDEX work_orders_created_at_idx ON work_orders (created_at);
ANALYZE work_orders;

-- Сохранить оба вывода вместе с SHOW всех cost-настроек окружения.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM work_orders WHERE state = 'ready';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM work_orders WHERE state = 'waiting';

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM work_orders
WHERE created_at >= timestamp '2019-07-10 00:00:00'
  AND created_at <  timestamp '2019-07-11 00:00:00';

После запуска сохранить не только дерево плана. Рядом с ним нужны версия сервера, размер таблицы, результаты ANALYZE, SHOW random_page_cost, SHOW seq_page_cost, SHOW effective_cache_size и время/характер нагрузки. Не потому, что каждый запрос требует большой анкеты, а потому, что два одинаковых SQL на ноутбуке и на production могут законно получить разные стоимости. Без контекста скриншот одного узла плохо годится для следующего решения.

Селективность: один столбец, два разных вопроса

В фикстуре запрос по ready соответствует почти всей таблице, а waiting — примерно одному проценту. Индекс в обоих случаях существует и условие одинаковой формы. Меняется не синтаксис, а ожидаемая доля результата. Для редкого значения индекс часто уменьшает объём чтения. Для частого он сначала обходит индекс, а потом всё равно возвращается к большинству строк. Поэтому один и тот же ключ может быть хорошим для операционного списка исключений и бессмысленным для экрана «все готовые».

Не превращайте процент в универсальную границу вроде «после пяти процентов индекс плохой». На выбор влияют ширина строк, физическая корреляция, число нужных колонок, cache и LIMIT. Например, маленький LIMIT меняет цену старта: план может предпочесть другой путь, потому что ему не надо дочитывать всё. Вопрос к плану конкретный: сколько строк он ожидает на каждом узле и сколько действительно вернул, а не «какая у нас любимая селективность».

Как читать первый EXPLAIN ANALYZE

Начните с верхнего узла и идите вниз по отступам. Верх отвечает за результат запроса, нижние узлы — за его входы. В каждом месте сравните оценку rows=... с фактом actual ... rows=.... У EXPLAIN ANALYZE фактические rows и time появляются потому, что запрос был выполнен. Его стоимости cost=... — внутренние условные единицы, не миллисекунды. Нельзя вычесть cost одного узла из времени другого и назвать это ускорением.

Дальше смотрим loops. Фактическое время и число строк на узле сообщаются в среднем за один запуск узла; при loops > 1 умножаем, чтобы оценить общий вклад. Для расследования это важнее красивой строки Index Scan: маленький внутренний поиск, повторённый тысячами раз в nested loop, может съесть заметное время. Включённый BUFFERS показывает, откуда пришли страницы — из shared buffers или с чтения, — и помогает не путать CPU-предикат с I/O-ценой.

Если estimate близок к actual, а план выбирает Seq Scan для ready, сначала принимаем гипотезу планировщика всерьёз: он видит массовый результат. Если estimate расходится в десять и более раз, не лечим симптом SET enable_seqscan = off. Такой переключатель может показать альтернативу для исследования, но он не даёт данным стать более селективными и не чинит статистику. В production его нельзя считать постоянным решением без отдельного основания.

Статистика — вход планировщика, а не служебный шум

Планировщик не перебирает таблицу перед каждым SELECT, чтобы узнать точную долю. Он использует приблизительные сведения о количестве строк, частых значениях, гистограммах и distinct-значениях. Их собирает ANALYZE; для ручного чтения документация советует view pg_stats, а не системный каталог напрямую. После большой загрузки, массового изменения статусов или перекоса новых данных убедитесь, что статистика обновилась, и только потом сравнивайте план до и после.

-- Сначала фиксируем контекст, затем обновляем только нужную таблицу.
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'p17_sql_index_fixture'
  AND tablename = 'work_orders'
  AND attname IN ('state', 'created_at');

ANALYZE p17_sql_index_fixture.work_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM p17_sql_index_fixture.work_orders
WHERE state = 'waiting';

Обновление статистики не обещает смену узла. Оно делает следующую оценку честнее. Если после ANALYZE план и факт сблизились, а последовательный проход остался, это полезный результат: индекс не потерялся, он просто дороже для данного условия. Если цифры по-прежнему расходятся, проверяем корреляцию нескольких столбцов, типы, cast, predicate и версию параметризированного запроса. Каждый следующий шаг должен менять одну гипотезу, иначе дифф планов нельзя прочитать.

Маршрут без угадывания

1. Записать точный SQL, значения параметров, цель запроса и наблюдаемый симптом: задержку, рост I/O или ошибочный объём результата. Не начинать с имени предполагаемого индекса.
2. Снять EXPLAIN (ANALYZE, BUFFERS) на безопасном SELECT. Для изменения данных использовать отдельную транзакцию и откат, потому что ANALYZE исполняет statement.
3. Сверху вниз сравнить estimated rows, actual rows, loops и Buffers. Отметить первый узел, где оценка перестала быть похожа на факт.
4. Проверить долю результата: частое значение, широкий диапазон и выгрузка без LIMIT могут честно требовать Seq Scan. Не объявлять такой выбор поломкой.
5. Проверить свежесть и форму статистики через pg_stats, затем выполнить целевой ANALYZE и повторить тот же замер.
6. Сверить выражение в WHERE, predicate частичного индекса и реальные нужные колонки. Только после этого обсуждать другой ключ, expression/partial index или изменение запроса.

Что эта практика не доказывает

• Она не измеряет production и не даёт нормативных миллисекунд: фикстура не запускалась в этой задаче и должна быть выполнена на отдельном контуре.
• Она не утверждает, что waiting обязательно даст Index Scan. Правильный артефакт — сохранённый план конкретного сервера и объяснение его строк/буферов.
• Она не оправдывает ручное отключение scan-стратегий как постоянную настройку. Принудительный план скрывает причину и может ухудшить соседние запросы.
• Она не заменяет проверку влияния индекса на INSERT, UPDATE, размер диска и время построения. Ускорение чтения имеет цену поддержки структуры.

Итог

Новый индекс не обязан ускорять запрос, который честно возвращает почти всю таблицу, опирается на старую статистику или написан в другой форме, чем ключ. Практический ответ начинается с сохранённого EXPLAIN ANALYZE: оценка против факта, loops, buffers и доля результата. После такой проверки можно оставить Seq Scan как правильный план либо изменить именно подтверждённую причину, а не коллекционировать индексы.

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

• PostgreSQL 11: Introduction to Indexes — индекс ускоряет поиск небольшого числа строк, но имеет цену на изменениях; после создания может требоваться актуальная статистика
• PostgreSQL 11: Index Types — B-tree покрывает распространённые сравнения равенства и диапазона; возможность использовать индекс зависит от оператора и формы условия
• 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 ценой времени и места