re:re:actable
Урок 013 минуты чтения

Что такое индекс

На этой странице
Кратко

Индекс — отдельная структура данных, которая позволяет найти строки по значению столбцов, не читая всю таблицу, — как алфавитный указатель в книге. Без индекса запрос 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/maxSELECT max(created_at) FROM orders

Цена индекса

ЗатратыПочему
ЗаписьКаждое изменение строки обновляет все её индексы
МестоИндекс может весить как таблица
HOT-обновленияИндекс на часто меняющемся столбце отключает дешёвые HOT-обновления
Планирование и обслуживаниеБольше вариантов для планировщика, VACUUM обрабатывает каждый индекс

Виды индексов в PostgreSQL

ТипДля чего
B-tree (по умолчанию)=, <, >, BETWEEN, ORDER BY, префиксный LIKE
GINjsonb, массивы, полнотекстовый поиск, 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.

Документация

PostgreSQL: Indexes, Use The Index, Luke.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам