Индекс — это отдельная структура данных, которая позволяет находить строки без полного сканирования таблицы. Правильно расставленные индексы ускоряют запросы в десятки и сотни раз.
Как работает индекс
Без индекса PostgreSQL читает каждую строку таблицы (Seq Scan). С индексом — сначала ищет в компактной структуре, получает указатели на строки, читает только их (Index Scan).
-- Создать индекс
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Посмотреть план
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
Типы индексов
B-tree (по умолчанию)
Работает для операций: =, <, >, <=, >=, BETWEEN, IN, LIKE 'prefix%', ORDER BY, IS NULL.
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_date ON orders (created_at);
Используй B-tree в 90% случаев. Подходит для большинства запросов с фильтрацией и сортировкой.
Особый случай — уникальный индекс: он не только ускоряет поиск, но и гарантирует отсутствие дубликатов. Технически это тот же B-tree, и он создаётся автоматически для PRIMARY KEY и UNIQUE-ограничений. Но можно создать и явно:
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
Hash
Только для точного равенства (=). Не поддерживает диапазоны и сортировку. В PostgreSQL 10+ стал надёжным (раньше не переживал краш).
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);
Незначительно быстрее B-tree для = при очень больших таблицах. В большинстве случаев B-tree не хуже.
GIN (Generalized Inverted Index)
Для поиска по массивам, JSONB, полнотекстового поиска. Один элемент → много строк.
-- Поиск в массиве
CREATE INDEX idx_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql', 'postgresql'];
-- Полнотекстовый поиск
CREATE INDEX idx_search ON articles USING GIN (to_tsvector('russian', body));
SELECT * FROM articles WHERE to_tsvector('russian', body) @@ to_tsquery('sql');
-- to_tsvector преобразует текст в специальный формат для поиска по словам,
-- GIN-индекс позволяет быстро находить документы без перебора всех строк
-- JSONB
CREATE INDEX idx_meta ON events USING GIN (metadata);
SELECT * FROM events WHERE metadata @> '{"type": "click"}';
BRIN (Block Range Index)
Очень маленький индекс для огромных таблиц с естественной сортировкой данных (например, временные ряды, лог-таблицы где строки вставляются по возрастанию времени).
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);
Занимает минимум места, но эффективен только если физический порядок данных совпадает с порядком запросов.
Составной индекс
Индекс по нескольким столбцам. Порядок важен!
CREATE INDEX idx_orders_user_status ON orders (customer_id, status);
-- Использует индекс
SELECT * FROM orders WHERE customer_id = 5;
SELECT * FROM orders WHERE customer_id = 5 AND status = 'paid';
-- НЕ использует индекс (нет первого столбца)
SELECT * FROM orders WHERE status = 'paid';
Правило: первый столбец должен быть тем, по которому фильтруешь чаще всего. Если оба столбца в фильтре — порядок в запросе не важен, важен порядок в индексе.
Covering index (покрывающий индекс): если индекс содержит все столбцы, нужные запросу, PostgreSQL выполнит Index Only Scan — вообще не обращаясь к таблице. Это максимально быстрый вариант:
-- Индекс (customer_id, status) покрывает этот запрос полностью
SELECT customer_id, status FROM orders WHERE customer_id = 5;
-- → Index Only Scan (таблица не читается)
Partial индекс
Индекс только по части строк. Меньше, быстрее.
-- Только активные заказы
CREATE INDEX idx_active_orders ON orders (created_at)
WHERE status = 'active';
-- Только не NULL
CREATE INDEX idx_orders_phone ON users (phone) WHERE phone IS NOT NULL;
Запрос использует partial индекс только если его условие совпадает с WHERE запроса.
Expression индекс
Индекс по выражению или функции.
-- Без этого индекса LOWER() ломает обычный индекс
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- использует индекс
-- По году
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM created_at));
Когда индекс НЕ используется
-- ❌ Функция над столбцом (без expression индекса)
WHERE LOWER(name) = 'иван'
WHERE DATE(created_at) = '2026-01-01'
-- ❌ Неявное приведение типов
WHERE id = '42' -- id integer, '42' text
-- ❌ LIKE с левым wildcardcard
WHERE name LIKE '%иван%' -- но LIKE 'ив%' — использует
-- ❌ OR с неиндексированным столбцом
WHERE indexed_col = 1 OR non_indexed_col = 2
-- ❌ Маленькая таблица — seq scan дешевле
Как проверить: EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
Смотри на:
Seq Scan→ полное сканирование (нет индекса или не используется)Index Scan→ индекс используется, строки читаются по однойIndex Only Scan→ индекс покрывает запрос целиком, таблица не читаетсяBitmap Heap Scan→ собирает много ссылок через индекс, затем читает страницы таблицы сортированно — эффективно при выборке большого числа строкcost=X..Y→ ожидаемая стоимостьactual time=X..Y→ реальное времяrows=N→ количество строк
Индексы и производительность записи
Каждый индекс замедляет INSERT/UPDATE/DELETE — при изменении данных нужно обновить и индекс. На таблицах с высокой нагрузкой записи не создавай лишних индексов.
-- Посмотреть все индексы таблицы
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- Найти неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'
ORDER BY tablename;
-- Важно: статистика накопительная с момента старта сервера или последнего сброса.
-- Индекс с idx_scan = 0 — кандидат на удаление, но убедитесь что сервер работал достаточно долго.
Практические советы
- Индексируй столбцы из WHERE, JOIN ON, ORDER BY — особенно те, где высокая селективность (много уникальных значений)
- Не индексируй всё подряд — индексы занимают место и замедляют запись
- Составной индекс vs два отдельных — составной эффективнее если оба столбца всегда вместе в фильтре
- После добавления индекса проверь EXPLAIN — убедись что он используется
- VACUUM и ANALYZE — регулярно обновляй статистику для правильных планов