Индексы в PostgreSQL — CREATE INDEX, типы, EXPLAIN

Индекс — структура данных, которая ускоряет поиск за счёт дополнительного хранилища. Разбираем синтаксис CREATE INDEX, все типы индексов, как проверить эффективность через EXPLAIN и когда индексы вредят.

Зачем нужны индексы — EXPLAIN до и после

-- Без индекса PostgreSQL читает всю таблицу (Seq Scan):
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Seq Scan on users (cost=0.00..18334.00 rows=1 width=120)
--   Filter: (email = 'alice@example.com')
-- Читает 1 000 000 строк чтобы найти одну

-- Создаём индекс:
CREATE INDEX idx_users_email ON users(email);

-- После индекса — точечное чтение (Index Scan):
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Index Scan using idx_users_email on users (cost=0.43..8.45 rows=1 width=120)
--   Index Cond: (email = 'alice@example.com')
-- В тысячи раз быстрее на больших таблицах

Без индекса PostgreSQL делает Sequential Scan — читает каждую строку таблицы. С индексом — Index Scan: B-дерево позволяет найти нужную запись за O(log n) операций.

Синтаксис CREATE INDEX

-- Простой индекс по одному полю:
CREATE INDEX idx_users_email ON users(email);

-- Уникальный индекс (аналог UNIQUE constraint):
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Составной индекс (порядок полей важен!):
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Подходит для: WHERE user_id = 1 AND status = 'paid'
-- Подходит для: WHERE user_id = 1 (первое поле)
-- НЕ подходит: WHERE status = 'paid' (только второе поле)

-- Частичный индекс (только для части строк):
CREATE INDEX idx_orders_active ON orders(user_id)
WHERE status != 'cancelled';
-- Индексирует только активные заказы — меньше размер, быстрее обновление

-- Функциональный индекс (по выражению):
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- Ускоряет: WHERE lower(email) = lower('Alice@Example.COM')

-- Индекс для сортировки:
CREATE INDEX idx_posts_date_desc ON posts(created_at DESC);
-- Ускоряет: ORDER BY created_at DESC

CREATE INDEX CONCURRENTLY — без блокировки

-- Обычный CREATE INDEX блокирует запись в таблицу на время создания!
CREATE INDEX idx_users_email ON users(email);
-- На таблице 10M строк — блокировка на несколько минут

-- CONCURRENTLY — не блокирует запись, работает дольше:
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Таблица остаётся доступной для INSERT/UPDATE/DELETE
-- Занимает в 2-3 раза больше времени
-- Нельзя создать в транзакции (вне BEGIN...COMMIT)
-- Может завершиться с ошибкой — тогда останется INVALID индекс

-- Проверить статус индексов:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

-- Найти INVALID индексы:
SELECT indexname
FROM pg_indexes
JOIN pg_class ON pg_class.relname = pg_indexes.indexname
WHERE pg_class.relkind = 'i'
  AND pg_index.indisvalid = FALSE;

Типы индексов PostgreSQL

-- Hash индекс (только для равенства):
CREATE INDEX idx_users_token_hash ON sessions USING hash(token);

-- GIN для массивов:
CREATE INDEX idx_products_tags ON products USING gin(tags);
-- Ускоряет: WHERE tags @> ARRAY['electronics']

-- GIN для JSONB:
CREATE INDEX idx_events_data ON events USING gin(metadata);
-- Ускоряет: WHERE metadata @> '{"type": "click"}'

-- GIN для полнотекстового поиска:
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('russian', content));

-- BRIN для временных рядов (очень маленький, для огромных таблиц):
CREATE INDEX idx_logs_date ON server_logs USING brin(created_at);
-- На таблице 100M строк занимает в 1000x меньше места чем B-tree

Управление индексами

-- Просмотреть все индексы таблицы в psql:
\d users
-- Раздел "Indexes:" покажет все индексы

-- Список всех индексов через SQL:
SELECT indexname, indexdef, tablespace
FROM pg_indexes
WHERE tablename = 'users'
ORDER BY indexname;

-- Размер индексов:
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users';

-- Использование индексов (был ли индекс задействован хоть раз):
SELECT
  schemaname, tablename, indexname,
  idx_scan AS times_used,
  idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY idx_scan DESC;

-- Удалить индекс:
DROP INDEX idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email;  -- без блокировки
DROP INDEX IF EXISTS idx_users_email;

-- Перестроить индекс (устранить раздутие):
REINDEX INDEX idx_users_email;
REINDEX TABLE users;                      -- все индексы таблицы
REINDEX INDEX CONCURRENTLY idx_users_email; -- без блокировки (PG 12+)

Когда индексы НЕ нужны

-- 1. Маленькие таблицы (до ~1000 строк):
-- Seq Scan быстрее из-за overhead'а индекса

-- 2. Колонки с низкой селективностью:
-- BAD: индекс на boolean (только 2 значения)
CREATE INDEX idx_users_active ON users(active);  -- неэффективно
-- PostgreSQL часто выберет Seq Scan даже при наличии индекса

-- 3. Частые массовые UPDATE/INSERT:
-- Каждое изменение данных требует обновления индекса
-- На INSERT-heavy таблицах — меньше индексов = быстрее запись

-- 4. Операции которые индекс не покрывает:
-- LIKE '%суффикс' (не начало строки):
WHERE name LIKE '%ivan'  -- индекс НЕ используется
WHERE name LIKE 'ivan%'  -- индекс используется

-- Функции без функционального индекса:
WHERE UPPER(email) = 'A@B.COM'  -- индекс на email НЕ используется
-- Нужен: CREATE INDEX ON users(UPPER(email))

Часто задаваемые вопросы

Как узнать какие индексы используются в PostgreSQL?

Через pg_stat_user_indexes: SELECT indexname, idx_scan FROM pg_stat_user_indexes WHERE tablename = 'users'; — поле idx_scan показывает сколько раз индекс использовался с момента последнего сброса статистики. Если idx_scan = 0 и прошло достаточно времени — индекс скорее всего лишний. Для анализа конкретного запроса: EXPLAIN (ANALYZE, BUFFERS) SELECT ... — в выводе будет указано «Index Scan using idx_name».

Почему PostgreSQL не использует мой индекс?

Несколько причин: 1) Планировщик решил что Seq Scan быстрее — обычно для маленьких таблиц или запросов которые вернут много строк (более 5-10% таблицы). 2) Функция или выражение в WHERE без соответствующего функционального индекса: WHERE lower(email) = 'a@b.com' не использует индекс на email. 3) Неподходящий тип операции — LIKE с ведущим wildcard LIKE '%text'. 4) Устаревшая статистика — выполните ANALYZE users; чтобы обновить. 5) Колонка с низкой кардинальностью — планировщик справедливо предпочитает Seq Scan.

В чём разница между B-tree и GIN индексами?

B-tree — универсальный индекс для скалярных значений: числа, строки, даты. Поддерживает равенство, диапазоны, LIKE с префиксом. GIN (Generalized Inverted Index) — для составных значений: массивы, JSONB, tsvector. Строит обратный индекс от каждого элемента к строкам. GIN занимает больше места и медленнее обновляется, но незаменим для поиска внутри JSONB: WHERE metadata @> '{"status":"active"}' или полнотекстового поиска. Для JSONB-колонки без GIN каждый запрос с @> делает Seq Scan всей таблицы.