Что такое индекс
На этой странице
Индекс — отдельная структура данных, которая позволяет найти строки по значению столбцов, не читая всю таблицу, — как алфавитный указатель в книге. Без индекса запрос WHERE email = $1 выполняет последовательное сканирование (Seq Scan) — O(n); с B-tree индексом — поиск за O(log n). Индекс ускоряет WHERE, JOIN, ORDER BY … LIMIT, обеспечивает UNIQUE/PRIMARY KEY, но замедляет запись (каждый INSERT/UPDATE/DELETE обновляет все индексы), занимает место и требует обслуживания. Поэтому индексы создают под конкретные частые запросы, проверяют их использование через EXPLAIN и удаляют неиспользуемые. В PostgreSQL PK и UNIQUE получают индекс автоматически, а внешние ключи — нет.
Вопрос
Что такое индекс, как он ускоряет запросы и какова его цена?
Без индекса и с индексом
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'anna@x.com';
-- Seq Scan on users (actual time=0.02..84.3 rows=1) Rows Removed by Filter: 999999
CREATE INDEX users_email_idx ON users (email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'anna@x.com';
-- Index Scan using users_email_idx on users (actual time=0.03..0.04 rows=1)Что ускоряет индекс
| Операция | Пример |
|---|---|
| Поиск по равенству и диапазону | WHERE email = $1, WHERE created_at >= $1 |
| Соединения | JOIN orders o ON o.user_id = u.id |
| Сортировка с лимитом | ORDER BY created_at DESC LIMIT 20 |
| Уникальность | UNIQUE (email) |
Агрегаты min/max | SELECT max(created_at) FROM orders |
Цена индекса
| Затраты | Почему |
|---|---|
| Запись | Каждое изменение строки обновляет все её индексы |
| Место | Индекс может весить как таблица |
| HOT-обновления | Индекс на часто меняющемся столбце отключает дешёвые HOT-обновления |
| Планирование и обслуживание | Больше вариантов для планировщика, VACUUM обрабатывает каждый индекс |
Виды индексов в PostgreSQL
| Тип | Для чего |
|---|---|
| B-tree (по умолчанию) | =, <, >, BETWEEN, ORDER BY, префиксный LIKE |
| GIN | jsonb, массивы, полнотекстовый поиск, trigram |
| GiST | Геоданные, диапазоны, EXCLUDE |
| BRIN | Огромные таблицы с естественным порядком (логи по времени) |
| Hash | Только равенство |
Подробнее — типы индексов.
Создание без блокировки
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);Обычный CREATE INDEX блокирует запись в таблицу на всё время построения. CONCURRENTLY дольше, не работает внутри транзакции и при сбое оставляет индекс INVALID — его нужно удалить и создать заново.
Неиспользуемые индексы
SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;Перед удалением проверьте, что индекс не обеспечивает уникальность и не используется на репликах.
Частые ошибки
- Индекс на каждый столбец «на всякий случай».
- Не индексировать FK-столбцы.
CREATE INDEXбезCONCURRENTLYна нагруженной таблице.- Не проверить через
EXPLAIN, что индекс действительно используется.
Что важно помнить
- Индекс ускоряет чтение ценой записи и места.
- Создавайте индексы под реальные запросы.
- В PostgreSQL FK не индексируются автоматически.
Мини-шпаргалка
CREATE INDEX [CONCURRENTLY] name ON t (cols).EXPLAIN ANALYZE— проверка.pg_stat_user_indexes.idx_scan— использование.DROP INDEX CONCURRENTLY.