Что такое индекс и когда он помогает

Что такое индекс и когда он помогает

Представьте толстую книгу без оглавления и предметного указателя. Чтобы найти упоминание «транзакция», нужно листать страницу за страницей — от первой до последней. Та же проблема возникает в базе данных, когда вы ищете строку без индекса: PostgreSQL читает таблицу с начала до конца. Это называется full table scan (полное сканирование таблицы). Индекс — это предметный указатель: структура данных, позволяющая найти нужные строки за доли секунды вместо прохода по всей таблице.

Как работает индекс внутри

Самый распространённый тип индекса — B-tree (сбалансированное дерево). Он устроен как структура с быстрым поиском: данные организованы в отсортированном дереве, где поиск любого значения требует не более log₂(N) шагов — то есть для таблицы из миллиона строк потребуется около 20 сравнений вместо 1 000 000.

Для таблицы users с колонкой email B-tree индекс хранит все значения email в отсортированном порядке вместе с указателями на строки таблицы. При запросе WHERE email = 'anna@example.com' PostgreSQL не сканирует таблицу — он спускается по дереву индекса и находит указатель напрямую.

-- Без индекса: PostgreSQL читает каждую строку
SELECT * FROM users WHERE email = 'anna@example.com';
-- Seq Scan: время ~ O(N), миллионы строк = секунды

-- С индексом на email: двоичный поиск по дереву
-- Index Scan: время ~ O(log N), миллионы строк = миллисекунды

Индекс занимает место на диске и требует обновления при каждой INSERT, UPDATE или DELETE. Это цена за скорость чтения.

Когда индексы создаются автоматически

PostgreSQL автоматически создаёт индекс при объявлении:

• PRIMARY KEY — уникальный B-tree индекс по PK-колонке
• UNIQUE — уникальный B-tree индекс по уникальной колонке

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,  -- автоматический индекс на id
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    email       TEXT UNIQUE NOT NULL  -- автоматический индекс на email
);
-- Индекс на customer_id НЕ создаётся автоматически!

Заметьте: FOREIGN KEY не создаёт индекс автоматически. Это один из самых частых пропусков у начинающих. Колонки FK используются в JOIN — без индекса каждый JOIN будет делать полное сканирование дочерней таблицы.

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

Хорошие кандидаты для индексирования:

• Колонки в WHERE: WHERE status = 'active', WHERE created_at > '2024-01-01'
• Колонки в JOIN ON: ON orders.customer_id = customers.id
• Колонки в ORDER BY (если запросы с сортировкой часты)
• Колонки FK — всегда

-- Эти запросы выиграют от индекса:
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM products WHERE category_id = 5 AND price < 1000;
SELECT * FROM events WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

Когда индекс не поможет или помешает:

• Очень маленькие таблицы (до ~1000 строк) — full scan быстрее, PostgreSQL сам выберет
• Колонки с низкой кардинальностью: gender (2-3 значения), status с большой долей одного значения — индекс неэффективен, если выборка охватывает 20%+ строк
• Таблицы с очень частыми INSERT/UPDATE — накладные расходы на поддержание индексов высоки

EXPLAIN: посмотреть план выполнения

Чтобы понять, использует ли PostgreSQL индекс, используйте EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Вывод покажет план: Seq Scan — полное сканирование, Index Scan — использование индекса, Bitmap Index Scan — индекс с последующей сортировкой по физическому расположению.

Для более детальной информации (с реальным временем выполнения):

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE реально выполняет запрос и показывает фактические числа: сколько строк найдено, сколько миллисекунд потребовалось. Это главный инструмент оптимизации запросов.

-- До индекса:
Seq Scan on orders  (cost=0.00..180.00 rows=1000 width=64)
  Filter: (customer_id = 42)

-- После создания индекса:
Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=12 width=64)
  Index Cond: (customer_id = 42)

cost — оценочная стоимость в условных единицах, rows — ожидаемое число строк.

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

PostgreSQL поддерживает несколько типов индексов для разных задач:

Для большинства задач достаточно B-tree (он по умолчанию). GIN нужен для массивов и JSONB-полей. BRIN — для больших таблиц событий с монотонно растущими датами: занимает в 100 раз меньше места, чем B-tree, но менее точен.

Частичные индексы

Иногда нужен индекс только для части строк:

-- Индекс только для активных пользователей
CREATE INDEX idx_users_active_email ON users(email) WHERE is_active = TRUE;

Такой индекс меньше по размеру и быстрее, чем полный индекс — если запросы всегда содержат WHERE is_active = TRUE. PostgreSQL использует частичный индекс только когда условие запроса совпадает с условием индекса.

-- Использует индекс (условие совпадает):
SELECT * FROM users WHERE email = 'anna@example.com' AND is_active = TRUE;

-- Не использует (нет условия на is_active):
SELECT * FROM users WHERE email = 'anna@example.com';

Составной индекс

Индекс по нескольким колонкам помогает при фильтрации по обоим полям:

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Важно: порядок колонок имеет значение. Индекс (customer_id, status) эффективен для:

WHERE customer_id = 42                         -- первая колонка — да
WHERE customer_id = 42 AND status = 'pending'  -- обе колонки — да
WHERE status = 'pending'                        -- только вторая колонка — нет!

Правило: первая колонка в составном индексе — та, по которой фильтрация происходит чаще всего. Если status нужен в запросах отдельно — добавьте отдельный индекс на status.

Стоимость индексов

Индексы не бесплатны:

Место на диске: B-tree индекс по одной большой таблице может занимать 10-30% от размера самой таблицы.

Замедление записи: при каждом INSERT, UPDATE или DELETE PostgreSQL обновляет все индексы таблицы. Таблица с 10 индексами вставляет строки медленнее, чем с 1 индексом.

Bloat (раздувание): индексы PostgreSQL при многократных обновлениях могут «разбухать» — занимать больше места, чем нужно. Периодически их нужно перестраивать командой REINDEX.

Практическое правило: не создавайте индексы превентивно «на всякий случай». Создавайте индексы на основе реальных медленных запросов, измеренных с EXPLAIN ANALYZE.

Ментальная модель: как PostgreSQL выбирает план

Когда вы запускаете SELECT, PostgreSQL не просто «выполняет запрос» — он строит план выполнения через компонент под названием Query Planner. Планировщик анализирует:

1. Какие индексы существуют на затронутых таблицах
2. Статистику распределения данных (сколько строк, как часто встречается каждое значение)
3. Предполагаемый размер результата
4. Оценочную стоимость разных стратегий (Seq Scan vs Index Scan vs Bitmap Scan)

И выбирает самый дешёвый план. Это автоматический выбор — вы не можете «заставить» PostgreSQL использовать индекс (хотя можно отключить Seq Scan для отладки).

Почему планировщик иногда игнорирует индекс:

Если таблица маленькая, Seq Scan быстрее — нет смысла использовать индекс. Если выборка по условию охватывает 30%+ строк — тоже Seq Scan эффективнее. Планировщик знает это благодаря статистике, которую собирает команда ANALYZE (запускается автоматически фоновым процессом autovacuum).

-- Принудительно обновить статистику
ANALYZE orders;

-- Посмотреть статистику по колонке
SELECT n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

n_distinct — оценка числа уникальных значений. correlation — насколько порядок значений совпадает с физическим порядком строк (1.0 = идеально, 0 = случайный). Низкая correlation делает Index Scan дорогим (много случайных чтений с диска) — в таком случае Bitmap Scan или Seq Scan может быть лучше.

Проверь себя

Есть таблица events с миллионом строк. Запрос SELECT * FROM events WHERE user_id = 123 AND event_type = 'click' выполняется 2 секунды. Какой индекс поможет и почему стоит поставить user_id первым?

Краткий итог

• Индекс — структура для быстрого поиска; B-tree самый универсальный
• PRIMARY KEY и UNIQUE создают индексы автоматически; FOREIGN KEY — нет
• EXPLAIN ANALYZE показывает, использует ли запрос индекс, и реальное время
• Хорошие кандидаты: колонки в WHERE, JOIN ON, ORDER BY, FK-колонки
• Индексы ускоряют чтение, но замедляют запись и занимают место
• Создавайте индексы на основе реальных медленных запросов

Что дальше

Вы понимаете, что такое индекс и когда он нужен. Следующий урок — синтаксис: как создать и удалить индексы командами CREATE INDEX и DROP INDEX, и как их просмотреть.