Антипаттерны индексов в PostgreSQL: когда индексы вредят

Индексы ускоряют чтение — это знают все. Но у этого ускорения есть цена: каждый индекс замедляет запись, занимает место на диске и в памяти, усложняет планировщику выбор плана. Больше индексов — не значит быстрее. Давайте разберём, когда индексы скорее мешают, чем помогают.

Индексы замедляют запись

При каждом INSERT, UPDATE или DELETE PostgreSQL обновляет все индексы таблицы. Таблица с 10 индексами при вставке данных выполняет в 10 раз больше работы по поддержке индексных структур по сравнению с таблицей без индексов.

-- Простая демонстрация: сравните время вставки
-- Таблица без индексов
CREATE TABLE orders_no_idx (
    id bigint, user_id bigint, status text,
    total numeric, created_at timestamptz
);

-- Таблица с "типичными" индексами
CREATE TABLE orders_with_idx (
    id bigint, user_id bigint, status text,
    total numeric, created_at timestamptz
);
CREATE INDEX ON orders_with_idx(user_id);
CREATE INDEX ON orders_with_idx(status);
CREATE INDEX ON orders_with_idx(created_at);
CREATE INDEX ON orders_with_idx(total);

-- INSERT в orders_with_idx будет заметно медленнее
-- при высокой интенсивности записи

Дублирующиеся индексы

Один из самых распространённых антипаттернов — создание нескольких индексов, которые перекрываются. Составной индекс (a, b) уже покрывает запросы по колонке a — отдельный индекс на a дублирует его.

-- Антипаттерн: дублирование
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- idx_orders_user_id теперь избыточен!
-- Запрос WHERE user_id = 5 может использовать оба индекса,
-- но составной справится не хуже простого

-- Найти потенциально дублирующиеся индексы
SELECT
    t.relname                               AS table_name,
    ix1.indexrelid::regclass                AS index1,
    ix2.indexrelid::regclass                AS index2,
    array_agg(a1.attname ORDER BY x1.ordinality) AS cols1,
    array_agg(a2.attname ORDER BY x2.ordinality) AS cols2
FROM pg_index ix1
JOIN pg_index ix2 ON ix1.indrelid = ix2.indrelid
    AND ix1.indexrelid < ix2.indexrelid
JOIN pg_class t ON t.oid = ix1.indrelid
CROSS JOIN LATERAL unnest(ix1.indkey) WITH ORDINALITY AS x1(attnum, ordinality)
CROSS JOIN LATERAL unnest(ix2.indkey) WITH ORDINALITY AS x2(attnum, ordinality)
JOIN pg_attribute a1 ON a1.attrelid = ix1.indrelid AND a1.attnum = x1.attnum
JOIN pg_attribute a2 ON a2.attrelid = ix2.indrelid AND a2.attnum = x2.attnum
GROUP BY t.relname, ix1.indexrelid, ix2.indexrelid
HAVING array_agg(a1.attname ORDER BY x1.ordinality)[1:1]
     = array_agg(a2.attname ORDER BY x2.ordinality)[1:1];

Индексы на колонках с низкой селективностью

Индекс работает хорошо, когда он отфильтровывает большую часть строк. Для колонки status с тремя значениями (active, inactive, banned) индекс почти бесполезен — планировщик предпочтёт sequential scan, потому что при каждом значении нужно читать треть таблицы.

-- Антипаттерн: индекс на boolean или колонку с малым числом значений
CREATE INDEX idx_users_is_active ON users(is_active);
-- Если 95% пользователей is_active = true,
-- планировщик не будет использовать этот индекс для WHERE is_active = true

-- Правильный подход: partial index
-- Индексируем только редкое значение
CREATE INDEX idx_users_inactive ON users(id)
WHERE is_active = false;
-- Теперь запрос "SELECT * FROM users WHERE is_active = false"
-- использует компактный индекс с маленькими данными

-- Ещё пример: индекс на статус заказа
-- Если 'completed' = 90% строк, 'pending' = 5%, 'failed' = 5%
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Очень эффективен для мониторинга незакрытых заказов

Неиспользуемые индексы

PostgreSQL ведёт статистику использования индексов. Индексы с нулевым числом сканирований — прямые кандидаты на удаление:

SELECT
    schemaname,
    relname                                    AS table_name,
    indexrelname                               AS index_name,
    idx_scan                                   AS scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisprimary      -- не первичный ключ
  AND NOT indisunique       -- не уникальный ограничитель
ORDER BY pg_relation_size(indexrelid) DESC;

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

Index bloat

Индексы тоже подвержены bloat — со временем в них накапливаются «мёртвые» записи от удалённых строк. VACUUM очищает таблицы, но для индексов нужен REINDEX.

-- Приблизительная оценка bloat индексов (без расширений)
SELECT
    schemaname,
    relname         AS table_name,
    indexrelname    AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- Перестройка индекса без блокировки (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

-- Перестройка всех индексов таблицы
REINDEX TABLE CONCURRENTLY orders;

Когда индекс не используется планировщиком

Иногда индекс есть, но планировщик его игнорирует. Причины:

-- 1. Функция на индексируемой колонке — индекс не применяется
-- Плохо:
SELECT * FROM users WHERE lower(email) = 'user@example.com';
-- Хорошо: создать функциональный индекс
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- 2. Неявное приведение типов
-- Колонка user_id bigint, но передаём text — индекс не используется
SELECT * FROM orders WHERE user_id = '123';  -- '123' это text!
-- Исправление:
SELECT * FROM orders WHERE user_id = 123;    -- integer literal

-- 3. Устаревшая статистика — планировщик неправильно оценивает selectivity
ANALYZE orders;

Практические правила

Итог

Регулярно проводите аудит индексов: ищите неиспользуемые через pg_stat_user_indexes, проверяйте дубли, удаляйте индексы с низкой селективностью. Меньше индексов — быстрее запись, меньше bloat, проще планировщику.

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