Индексы в Postgres, которые реально ускоряют выборку

Постгрес - база, в которую любят закидывать JSON, массивы, строки-миллионники и потом удивляться, почему медленно. В 9 из 10 случаев проблема - неправильные индексы или их отсутствие. Разберём три семейства, которые покрывают 95% задач.

B-tree: универсальная рабочая лошадка#

Это дефолтный индекс. Хорош для равенства, <, >, BETWEEN, ORDER BY. Плох для паттерна LIKE '%foo%' и полнотекстового поиска.

-- Быстрый поиск по email
CREATE INDEX idx_users_email ON users (email);
 
-- Для ORDER BY created_at DESC - направление имеет значение
CREATE INDEX idx_lots_created_at_desc
  ON lots (created_at DESC);

GIN: когда данные внутри данных#

Для jsonb, массивов, tsvector (полнотекст).

-- Ищем лоты, где в tags есть 'urgent'
CREATE INDEX idx_lots_tags ON lots USING GIN (tags);
 
-- Ищем по ключу внутри jsonb-поля metadata
CREATE INDEX idx_lots_metadata ON lots USING GIN (metadata jsonb_path_ops);

jsonb_path_ops компактнее дефолтного jsonb_ops, но поддерживает только оператор @>. В 90% случаев этого достаточно.

BRIN: гигантские таблицы по времени#

Block Range INdex. Микроскопический по размеру, но работает только если данные физически упорядочены (обычно - по created_at в append-only таблицах).

CREATE INDEX idx_events_created_at_brin
  ON events USING BRIN (created_at);

Когда 100M строк логов - B-tree займёт гигабайты, BRIN - мегабайты. При фильтре WHERE created_at > now() - interval '1 day' Postgres прочтёт только нужный диапазон блоков.

Как проверить, что индекс реально работает#

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM lots
WHERE status = 'active' AND created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

Смотрите на:

• Index Scan vs Seq Scan - последнее значит, что индекс не подошёл.
• Buffers: shared read=N - если много read'ов, база не помещается в RAM.
• actual time=X..Y - реальное время (запустите 3 раза, берите последнее).

Вывод#

Индексы - это trade-off между скоростью чтения и весом/скоростью записи. Добавляйте по метрикам, удаляйте неиспользуемые:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Любой индекс с idx_scan = 0 в проде - кандидат на удаление.