💡 Никогда не работал с SQL? Сначала пройди серию SQL с нуля для аналитика — 10 связанных частей от «что такое БД» до Window Functions.
«Запрос работал — потом перестал». Часто причина одна: добавили объём, не добавили индекс. Или добавили — но не тот. PostgreSQL поддерживает 6 типов индексов, и выбор не очевиден: B-tree для всего, BRIN для time-series, GIN для full-text, GiST для гео, Hash для равенства, SP-GiST для редких структур.
Этот гайд — практический выбор индекса аналитика: что брать для каких запросов, как тестировать и где люди ошибаются.
Почему индекс делает запрос быстрее?
Без индекса PostgreSQL читает каждую строку таблицы (Seq Scan). С индексом — за 20 шагов через B-tree находит нужное (Index Scan). На таблице 1М строк — разница в 3000 раз.
!Index Scan vs Seq Scan: почему индекс быстрее
B-tree — индекс по умолчанию
Когда юзаешь CREATE INDEX без типа — получаешь B-tree. Покрывает 95% случаев аналитика.
CREATE INDEX idx_orders_user_id ON orders(user_id);Что умеет B-tree:
- Equality:
WHERE col = 5 - Range:
WHERE col > 100,WHERE col BETWEEN 10 AND 20 - LIKE с фиксированным префиксом:
WHERE name LIKE 'Иван%' - ORDER BY (сортированное чтение)
- IS NULL / IS NOT NULL
Что НЕ умеет:
- LIKE с wildcard в начале:
WHERE name LIKE '%ванов'— full scan - Полнотекстовый поиск с tsvector
- Массивы / JSONB-операторы
Composite B-tree
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);Поддерживает:
WHERE user_id = 5— используетWHERE user_id = 5 AND created_at > '2026-01-01'— использует ОБАWHERE created_at > '2026-01-01'— НЕ использует (нет первой колонки)
Правило: первая колонка composite — самая селективная или обязательная в WHERE.
Шаг 1: проверь нужен ли индекс через EXPLAIN
Прежде чем создавать — посмотри текущий план:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;Если видишь Seq Scan на таблице с 1M+ rows — индекс нужен. Если Index Scan — уже есть и работает.
Подробнее про чтение плана — в нашем посте про EXPLAIN ANALYZE.
BRIN — для time-series и больших таблиц
BRIN (Block Range Index) хранит min/max по блокам диска, а не по строкам. Размер индекса — 1000× меньше B-tree.
CREATE INDEX idx_events_date_brin ON events USING BRIN(event_date);Когда брать BRIN
| Сценарий | Подходит? |
|---|---|
| Append-only events (логи, временные ряды) | ✅ Идеально |
| Таблица отсортирована по полю физически | ✅ Да |
| Sortable значения (дата, ID-секвенс) | ✅ Да |
| Random updates на колонку | ❌ Деградирует |
| Маленькая таблица (<10M rows) | ⚠️ B-tree быстрее |
Реальный кейс: таблица page_views на 500M строк. B-tree по view_ts занимал 8GB. BRIN — 8MB. Скорость почти та же для range-запросов «последние 7 дней».
Шаг 2: оцени размер BRIN до создания
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
pg_size_pretty(pg_relation_size('idx_orders_date')) AS btree_size;
-- BRIN будет ~1000× меньше btree_sizeGIN — массивы, JSONB, full-text
GIN (Generalized Inverted Index) — для multi-value колонок: массивов, JSONB, tsvector.
-- Массив тегов
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- Запрос
SELECT * FROM posts WHERE tags @> ARRAY['python', 'sql'];
-- JSONB
CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);
-- Запрос
SELECT * FROM events WHERE payload @> '{"action": "click"}';
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('russian', body));GIN vs B-tree для JSONB
| Запрос | Индекс | Скорость |
|---|---|---|
WHERE payload->>'user_id' = '5' | B-tree expression | ✅ Быстро |
WHERE payload @> '{"action":"click"}' | GIN | ✅ Быстро |
WHERE payload @> '{"a":1, "b":2}' сложный | GIN | ✅ Быстро |
WHERE payload->>'count' > '100' | B-tree expression | ✅ Быстро |
WHERE payload ? 'has_key' | GIN | ✅ Быстро |
Правило: для JSONB containment (@>) или массивов — GIN. Для single field из JSONB — B-tree expression на этом поле.
Partial индексы — экономим место и ускоряем
Partial index — индекс по подмножеству строк. Создаётся с WHERE:
-- Только активные пользователи (10% от total)
CREATE INDEX idx_users_active_email ON users(email) WHERE is_active = TRUE;Шаг 3: где partial реально помогает
Кейс 1: orders таблица, 99% — status='completed'. Дашборд аналитика смотрит только status='pending' (1%).
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';Index в 100× меньше, запросы — мгновенно.
Кейс 2: soft delete колонка deleted_at IS NULL для всех «живых» записей.
CREATE INDEX idx_users_email_alive ON users(email)
WHERE deleted_at IS NULL;Условие в WHERE должно совпасть с тем что в запросе, иначе планер не возьмёт индекс.
Covering indexes (INCLUDE) — Index-Only Scan
С PG 11+ — INCLUDE добавляет «несортируемые» колонки в индекс.
CREATE INDEX idx_orders_user_covering
ON orders(user_id) INCLUDE (created_at, total_amount);Эффект: SELECT user_id, created_at, total_amount FROM orders WHERE user_id = 5 идёт через Index Only Scan — heap (основная таблица) вообще не читается. 2-5× быстрее для wide-таблиц.
Проверь что используется:
EXPLAIN ANALYZE SELECT user_id, created_at, total_amount FROM orders WHERE user_id = 5;
-- В плане должно быть: «Index Only Scan» (не «Index Scan»)GiST — гео и диапазоны
GiST (Generalized Search Tree) — для сложных типов: точки на карте, диапазоны, full-text.
-- PostGIS
CREATE INDEX idx_stores_location ON stores USING GIST(geom);
SELECT * FROM stores WHERE ST_DWithin(geom, ST_MakePoint(37.6, 55.7)::geography, 5000);
-- Range types
CREATE INDEX idx_bookings_period ON bookings USING GIST(period);
SELECT * FROM bookings WHERE period && tsrange('2026-06-01', '2026-06-30');В обычной аналитике используется редко — но если работаешь с PostGIS или tstzrange — это твой выбор.
Hash индексы — забудь
С PG 10+ hash indexes WAL-логируются и работают, но B-tree быстрее или равен для equality в 99% кейсов. Не нужны.
Шаг 4: проверь что индекс реально используется
После создания индекса — не верь надежде, проверяй:
-- Список индексов таблицы
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- Статистика использования
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;
-- 0 idx_scan = индекс не используется (можно удалить)Шаг 5: типичные ошибки
Ошибка 1: функция в WHERE «убивает» индекс
-- Индекс есть, но не используется
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE LOWER(email) = 'ivan@mail.ru'; -- Seq Scan!
-- Фикс — expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));Ошибка 2: OR на разных колонках
SELECT * FROM orders WHERE user_id = 5 OR product_id = 10;
-- Может выбрать BitmapOr — но часто Seq ScanФикс: разбить на UNION ALL или composite expression index.
Ошибка 3: NOT IN с NULL
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM blocked);
-- Если в blocked есть NULL — вернёт пусто (3-valued logic)Фикс: WHERE user_id NOT IN (SELECT id FROM blocked WHERE id IS NOT NULL) или NOT EXISTS.
Ошибка 4: индекс на маленькой таблице
Таблица 1000 строк → planner всё равно сделает Seq Scan (быстрее чем index lookup). Индекс там не нужен.
Частые вопросы
Когда использовать BRIN вместо B-tree?
BRIN — для больших append-only таблиц где данные физически отсортированы (events, logs, time-series). Если таблица < 50M строк или updates рандомные — B-tree.
GIN или GiST для full-text?
GIN — быстрее на поиск, медленнее на insert. GiST — наоборот. Для read-heavy аналитики — GIN.
Сколько индексов можно повесить на одну таблицу?
Технически — много. Практически — каждый индекс замедляет INSERT/UPDATE/DELETE на ~5-10%. Норма для аналитической таблицы — 3-7 индексов.
Как найти missing indexes?
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND idx_scan < seq_scan / 10
ORDER BY seq_tup_read DESC;
-- Таблицы с большим seq_scan и мало idx_scan — кандидаты на индексСтоит ли индексировать boolean колонку?
Только если очень selectivity — например, is_paid = TRUE для 0.1% записей. Partial index с условием.
Что дальше
Если хочешь практику — попробуй SQL-тренажёр с автопроверкой (5 задач бесплатно) или Python-тренажёр. В обоих — настоящий PostgreSQL 16 в браузере, можно создавать индексы и смотреть как меняется план.
Готов к собеседованиям? AI-интервью тренирует ответы на реальных вопросах из бесплатного пула. В Pro — безлимит мок-собесов с разбором + 491 SQL-задача + 612 тестовых заданий из реальных собесов 2026.
Смежные посты
- EXPLAIN ANALYZE в PostgreSQL — как читать план
- Оконные функции SQL — window functions и индексы
- SQL-antipatterns 10 ошибок — где индекс «не сработает»
- ClickHouse vs PostgreSQL benchmarks — когда не PG
Сравнить Free и Pro → (1999₽/мес, экономит часы рутины)
Источники
- PostgreSQL Docs: «Indexes» (postgresql.org/docs/current/indexes.html)
- Use The Index, Luke! (use-the-index-luke.com) — guide по B-tree
- Habr Otus: «BRIN индексы практика» (habr.com)
- Hubert Lubaczewski: «GIN vs GiST» (depesz.com)