«Запрос тормозит - добавим индекс» - популярный, но неполный ответ. На собеседовании backend-разработчика ждут другого: как вы найдёте причину, прочитаете план, выберете индекс и проверите, что стало лучше. Разберём это на типичной таблице заказов.
Исходная ситуация
Личный кабинет показывает последние оплаченные заказы пользователя. Пока таблица маленькая, всё быстро. Когда строк становятся миллионы, страница начинает отвечать секундами. Начинаем с плана, а не с догадок.
SQL-- Таблица заказов и запрос личного кабинета
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
total numeric(12, 2) NOT NULL
);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
Как читать план
План - дерево узлов, читать его удобно снизу вверх: внутренние узлы поставляют строки внешним. Без подходящего индекса здесь будет Seq Scan по всей таблице с фильтром, затем Sort по created_at и только потом Limit. Обратите внимание на ключевые поля вывода:
- cost - оценка планировщика в условных единицах: стоимость до первой строки и до всех строк.
- rows - ожидаемое число строк; в ANALYZE рядом будет фактическое. Большое расхождение - повод обновить статистику (ANALYZE таблицы).
- actual time - реальное время узла в миллисекундах; loops показывает, сколько раз узел выполнялся.
- Rows Removed by Filter - сколько строк прочитали зря. Чем больше, тем полезнее индекс.
- Buffers - сколько страниц прочитано из кеша (hit) и с диска (read).
Порядок полей в составном индексе
B-tree индекс хранит ключи отсортированными по порядку столбцов. Поэтому правило такое: сначала столбцы, которые сравниваются на равенство, затем столбец диапазона или сортировки. Для нашего запроса user_id и status - равенство, created_at - сортировка.
SQL-- Сначала поля с равенством, затем поле сортировки
CREATE INDEX CONCURRENTLY orders_user_status_created_idx
ON orders (user_id, status, created_at DESC);
CONCURRENTLY строит индекс без долгой блокировки записи - это важно на рабочей базе, хотя построение идёт дольше и не может выполняться внутри транзакции. После создания индекса план меняется: внутри одной пары (user_id, status) строки уже упорядочены по created_at, поэтому отдельная сортировка не нужна, и Limit останавливает чтение после 20 строк.
SQL-- План после индекса (сокращённо, без цифр: они зависят от данных)
-- Limit
-- -> Index Scan using orders_user_status_created_idx on orders
-- Index Cond: ((user_id = 42) AND (status = 'paid'::text))
Левый префикс и частые ошибки
- Индекс (user_id, status, created_at) поможет условиям по user_id и по user_id + status, но не запросу только по status.
- Функция над столбцом ломает использование индекса: WHERE lower(email) = ... требует индекса по выражению lower(email).
- Если покрыть индексом все нужные столбцы (INCLUDE), возможен Index Only Scan без чтения таблицы - но индекс станет тяжелее.
- Частичный индекс WHERE status = 'paid' меньше и быстрее, если запросы почти всегда фильтруют по этому значению.
N+1: медленно не в базе, а в коде
Даже идеальные индексы не спасут, если приложение делает запрос на каждую строку. Классический пример - список постов, к каждому из которых отдельно загружается автор. Двадцать постов - двадцать один запрос и двадцать сетевых задержек.
TypeScript// N+1 и исправление: авторы загружаются одним запросом
type Db = { query: (sql: string, params?: unknown[]) => Promise<{ rows: any[] }> }
export async function postsWithAuthors(db: Db) {
const posts = (await db.query('SELECT id, author_id, title FROM posts ORDER BY id DESC LIMIT 20')).rows
const ids = [...new Set(posts.map((post) => post.author_id))]
const authors = (await db.query('SELECT id, name FROM users WHERE id = ANY($1)', [ids])).rows
const byId = new Map(authors.map((author) => [author.id, author]))
return posts.map((post) => ({ ...post, author: byId.get(post.author_id) ?? null }))
}
Два запроса вместо двадцати одного. ORM обычно умеют то же самое через жадную загрузку связей; главное - заметить проблему. Включите логирование запросов в разработке и считайте их число на одну страницу.
Как проверить, что стало лучше
- Зафиксируйте исходный план и время на данных, похожих на боевые по объёму.
- Добавьте один индекс под конкретный запрос и снова снимите EXPLAIN (ANALYZE, BUFFERS).
- Проверьте, что запись не стала заметно медленнее, а индекс используется (pg_stat_user_indexes).
- Для общей картины включите pg_stat_statements и ищите запросы с наибольшим суммарным временем.
Темы SQL - индексы, транзакции, уровни изоляции и оптимизация - входят в программу менторства Backend. Про контракт ошибок, если база недоступна, - в разборе ошибок Node.js API.
Источники
Документация PostgreSQL: Using EXPLAIN, Multicolumn Indexes, Indexes and ORDER BY, CREATE INDEX, pg_stat_statements.