Индексы в PostgreSQL: когда и как ставить, чтобы ускорить запросы

[GuDron]

Что такое индекс?
Индекс в PostgreSQL - это структура данных (обычно B-tree), позволяющая быстро находить строки по значению столбца, не сканируя всю таблицу.

Пример создания простого B-tree-индекса

-- Ускоряем поиск по полю email
CREATE INDEX idx_users_email
  ON users (email);

Best Practices
1. Выбирай правильный тип

• BTREE - по умолчанию, для большинства операций сравнения (=, <, >, BETWEEN).
• GIN/GiST - для полнотекстового поиска (tsvector), работы с массивами и геоданных.
• HASH - для строго равенств (=), но редко нужен.

2. Индексируй часто фильтруемые и сортируемые поля

• WHERE, JOIN, ORDER BY.
• Например, для запросов типа

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;

создаём составной индекс:

CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);

3. Не злоупотребляй

• Каждый индекс занимает место и замедляет INSERT/UPDATE/DELETE.
• Проанализируй pg_stat_user_indexes и pg_stat_user_tables через pg_stat_statements перед добавлением.

4. Используй частичные индексы
Если условие фильтрации редко меняется, можно сузить индекс:

CREATE INDEX idx_active_users_email
ON users (email)
WHERE active = true;

5. Поддерживай актуальность
Периодически делай REINDEX или VACUUM ANALYZE для больших таблиц, чтобы индекс не фрагментировался.
Антипаттерн: “Индекс на всё”
Создание индекса на каждый столбец:

-- Плохо: много маленьких индексов, мало пользы, много затрат
CREATE INDEX idx1 ON table(a);
CREATE INDEX idx2 ON table(b);
CREATE INDEX idx3 ON table(c);
...

Проблемы:

• Большой объём хранилища.
• Замедление DML-операций.
• Планы запросов могут пропускать некоторые индексы.

Вывод
Правильно подобранные и настроенные индексы - ключ к быстрой работе базы. Сосредоточься на реально востребованных столбцах, комбинируй, не забывай про обслуживание.