Индексы PostgreSQL: B-tree, GIN и когда они помогают

Коротко: Индекс — дополнительное дерево поиска, ускоряющее запросы, но стоящее записи и хранилища; вы создаёте его сознательно для частых фильтров, сортировок и соединений.

Подробнее: Без индекса PostgreSQL читает для запроса всю таблицу (seq scan). Индекс быстро находит строки, как предметный указатель в книге.

Подробности

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

• B-tree (по умолчанию): равенство, диапазоны (<, >, BETWEEN), сортировка (ORDER BY), префикс LIKE 'abc%'.
• Hash: только равенство.
• GIN: многозначные данные (массивы, JSONB, полнотекстовый tsvector).
• GiST/SP-GiST: геоданные, диапазоны, сходство.
• BRIN: очень большие, упорядоченные по времени таблицы, крошечный.
• pgvector (HNSW/IVFFlat): векторный поиск.

CREATE INDEX idx_user_email ON users (email);
CREATE UNIQUE INDEX uq_user_email ON users (lower(email));
CREATE INDEX idx_order_customer_date ON orders (customer_id, created DESC);
CREATE INDEX idx_active ON users (id) WHERE active;       -- частичный
CREATE INDEX idx_tags ON articles USING gin (tags);

Когда индекс окупается?

• Столбцы в WHERE, JOIN (внешние ключи!), ORDER BY.
• Избирательные условия (мало совпадений).
• Составные индексы: порядок важен; (customer_id, created) помогает при WHERE customer_id = … и … AND created …, но не при фильтре только по created (правило левого префикса).
• Без индекса на крошечных таблицах, на столбцах с малым числом значений (да/нет) в одиночку или если в таблицу постоянно интенсивно пишут.
• Стоимость: каждый индекс замедляет INSERT/UPDATE/DELETE, занимает хранилище и обслуживание.

Проверка через EXPLAIN

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42 ORDER BY created DESC LIMIT 10;

Seq Scan на большой таблице = подозрение; Index Scan/Index Only Scan/Bitmap Heap Scan = индекс использован. Планировщик выбирает по статистике (ANALYZE); иногда при многих совпадениях seq scan быстрее. pg_stat_user_indexes показывает никогда не использовавшиеся индексы (удалите их). CREATE INDEX CONCURRENTLY строит без долгой блокировки.

Типичные ошибки

Функции над столбцами (WHERE lower(email) = …) мешают обычному индексу (→ функциональный индекс). Преобразования типов, ведущий % в LIKE, OR по разным столбцам. Отсутствие индекса на внешнем ключе замедляет соединения и удаления. Слишком много индексов. В бессерверных базах данных считается и время вычислений: меньше seq scan = меньше часов CPU.

См. также: PostgreSQL, Планировщик запросов и EXPLAIN, Оптимизация запросов с EXPLAIN, Проблема N+1, Векторные базы данных