Какие типы индексов существуют и когда какой использовать?

Индекс — структура данных, ускоряющая поиск строк в таблице за счёт дополнительного расхода на запись и хранение. В PostgreSQL поддерживается несколько типов индексов, каждый из которых оптимален для определённых сценариев.

Основные типы индексов

B-tree (сбалансированное дерево)

Тип по умолчанию. Подходит для большинства случаев: сравнения, сортировка, поиск по префиксу.

CREATE INDEX idx_emp_name ON employees(name);
-- Эквивалентно: CREATE INDEX idx_emp_name ON employees USING btree(name);

Hash

Оптимален только для =. Не поддерживает сортировку и диапазоны. В PostgreSQL 10+ crash-safe.

CREATE INDEX idx_emp_email ON employees USING hash(email);

GIN (Generalized Inverted Index)

Инвертированный индекс для значений, содержащих несколько элементов.

-- Полнотекстовый поиск
CREATE INDEX idx_articles_fts ON articles USING gin(to_tsvector('russian', content));

-- JSONB
CREATE INDEX idx_data_jsonb ON events USING gin(metadata jsonb_path_ops);

-- Массивы
CREATE INDEX idx_tags ON posts USING gin(tags);

GiST (Generalized Search Tree)

Обобщённое дерево поиска для геометрических данных, диапазонов, полнотекстового поиска.

-- Геоданные (PostGIS)
CREATE INDEX idx_locations ON places USING gist(coordinates);

-- Поиск ближайших соседей (KNN)
SELECT name, coordinates <-> point(55.75, 37.62) AS distance
FROM places
ORDER BY coordinates <-> point(55.75, 37.62)
LIMIT 5;

BRIN (Block Range Index)

Хранит сводную информацию о диапазонах значений в физических блоках. Очень компактный, но эффективен только для данных с физической корреляцией (данные отсортированы на диске).

CREATE INDEX idx_events_date ON events USING brin(created_at);

Специальные виды индексов

Составные (композитные)

CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

Порядок столбцов важен. Индекс (department, salary) эффективен для WHERE department = 'IT', но не для WHERE salary > 50000 без фильтрации по department. Правило: столбцы с = ставить первыми, затем — с диапазонными условиями.

Частичные (partial)

CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

Индексируют только строки, удовлетворяющие условию — меньше размер, быстрее обновление.

Покрывающие (covering) — INCLUDE

CREATE INDEX idx_emp_dept_covering ON employees(department) INCLUDE (name, salary);

Добавляют столбцы в индекс для обеспечения Index Only Scan без обращения к таблице.

Когда НЕ нужно создавать индекс

• Таблица маленькая (менее нескольких тысяч строк) — Seq Scan будет быстрее
• Столбец имеет очень низкую селективность (например, gender с 2 значениями)
• Таблица очень часто обновляется — каждый INSERT/UPDATE/DELETE обновляет все индексы
• Столбец редко используется в WHERE, JOIN, ORDER BY

Частые ошибки

• Создание индекса на каждый столбец — замедляет запись без пропорционального ускорения чтения
• Неправильный порядок столбцов в составном индексе — индекс не используется
• Функция в WHERE без функционального индекса: WHERE LOWER(name) = 'ivan' не использует обычный индекс на name
• B-tree для полнотекстового поиска или JSONB — нужен GIN

На собеседовании: назовите 4-5 типов индексов и для каждого приведите пример использования. Обязательно упомяните составные, частичные и покрывающие индексы. Частая ошибка — знать только B-tree и не уметь подобрать тип индекса под задачу.