PostgreSQL: иногда за оптимизацией может последовать деградация или нагрузочное тестирование как инструмент познания СУБД.

Не всегда индексы созданы для скорости.

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

Приготовьтесь пересмотреть свои взгляды на оптимизацию PostgreSQL и научиться доверять сигналам, которые подает вам СУБД.

ℹ️Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

Завершение цикла статей, посвященных анализу влияния индексов на производительность СУБД

Характерные признаки неэффективности индекса

В PostgreSQL нет специфических wait events, которые прямо указывают на ненужность индекса, но следующие признаки в планах выполнения и статистике могут сигнализировать о проблеме:

1. Высокая стоимость обслуживания индекса

• Wait Events, связанные с записью на диск (например, WALWrite, BgWriterHibernate), могут участиться из-за частых обновлений индекса при INSERT/UPDATE/DELETE.
• В планах DML-запросов значительные затраты на Index Updates (строки -> Index Insert, -> Index Delete).

2. Низкая эффективность индекса

• Bitmap Index Scan с последующим Bitmap Heap Scan:
• Если Rows Removed by Index Recheck велико, индекс неточно фильтрует данные.
• Высокое значение Heap Blocks Fetched указывает на много случайных чтений.
• Index Scan с большим Actual Loops и высоким Cost по сравнению с Seq Scan.

3. Избыточность индекса

• Если индекс используется, но в плане появляется Sort или Group, хотя индекс должен обеспечивать порядок (например, для ORDER BY). Это может означать неоптимальность порядка колонок в индексе.
• Наличие нескольких индексов с пересекающимися колонками, где один индекс заменяет другой.

4. Статистика использования

• Запрос к pg_stat_user_indexes показывает низкое значение idx_scan при высоких idx_tup_read и idx_tup_fetch — индекс читает много строк, но редко используется.

5. Размер индекса

• Индекс занимает больше места, чем сама таблица (pg_relation_size), и не дает преимуществ в производительности.

Примеры неэффективных индексов:

• Индексы на колонки с малым количеством уникальных значений (например, boolean).
• Частичные индексы с избыточными условиями.
• Индексы, дублирующие функциональность других индексов.

ℹ️Конкретные признаки в планах выполнения и wait events, указывающие на неэффективность индекса по сравнению с Seq Scan:

1. Высокий процент отфильтрованных строк

-- Если индекс отбирает >5-10% таблицы, он часто проигрывает Seq Scan

Index Scan using idx_name on table (cost=0.43..1254.32 rows=50000 width=8)

Index Cond: (status = 'active')

-- rows=50000 при общем размере таблицы 100000 строк = 50% - слишком много для индекса

2. Большое количество Heap Fetches

Bitmap Heap Scan on orders (cost=184.55..17524.82 rows=8822 width=45)

Recheck Cond: (customer_id = 123)

Heap Blocks: exact=4200 -- Слишком много блоков таблицы прочитано

-> Bitmap Index Scan on idx_orders_customer_id

3. Низкая селективность в Bitmap Index Scan

Bitmap Index Scan on idx_low_selectivity (cost=0.00..1123.45 rows=80000 width=0)

Index Cond: (flag = true) -- Индекс на boolean поле обычно неэффективен

Количественные показатели неэффективности:

1. Сравнение стоимости в плане

-- Плохой случай: индекс дороже последовательного сканирования

Index Scan: (cost=0.43..2500.00 rows=45000)

Seq Scan: (cost=0.00..1500.00 rows=45000) -- Дешевле!

2. Статистика из pg_stat_user_indexes

-- Низкая эффективность индекса

SELECT schemaname, tablename, indexname,

idx_scan, idx_tup_read, idx_tup_fetch,

-- Эффективность: сколько строк возвращается на одно сканирование

CASE WHEN idx_scan > 0

THEN round(idx_tup_read::numeric / idx_scan, 2)

ELSE 0 END as tuples_per_scan

FROM pg_stat_user_indexes

WHERE idx_tup_read::numeric / idx_scan > 10000; -- Слишком много строк на сканирование

ℹ️Типичные сценарии неэффективных индексов:

1. Индексы на низкоселективные колонки

-- Индекс на поле с 2-3 значениями

CREATE INDEX idx_gender ON users(gender); -- 'M', 'F', NULL

2. Неправильный порядок колонок в составном индексе

-- Запрос: WHERE status = 'active' AND created_at > '2023-01-01'

CREATE INDEX idx_created_status ON orders(created_at, status); -- Неоптимально

CREATE INDEX idx_status_created ON orders(status, created_at); -- Оптимально

3. Индексы на часто обновляемые таблицы

-- На таблице с частыми INSERT/UPDATE индекс может замедлять запись

UPDATE sessions SET last_activity = NOW() WHERE user_id = 123;

-- Каждое обновление требует изменения индекса

Диагностика:

1. Сравнение стоимости индекса vs seq scan

EXPLAIN (ANALYZE, BUFFERS)

SELECT * FROM table WHERE indexed_column = 'value';

-- Затем принудительно отключите индекс для сравнения:

SET enable_indexscan = off;

EXPLAIN (ANALYZE, BUFFERS)

SELECT * FROM table WHERE indexed_column = 'value';

RESET enable_indexscan;

2. Анализ распределения данных

-- Селективность индекса

SELECT indexed_column, count(*),

round(100.0 * count(*) / (SELECT count(*) FROM table), 2) as pct

FROM table

GROUP BY indexed_column

ORDER BY count DESC;

Когда индекс становится невыгодным:

• 👍Селективность < 5% - обычно выгоден индекс
• Селективность 5-20% - зависит от размера таблицы и распределения данных
• Селективность > 20% - обычно выгоден Seq Scan
• Маленькие таблицы (< 1000 строк) - индексы обычно не нужны
• Частые массовые обновления - стоимость поддержки индекса может превышать пользу

Эти признаки помогают идентифицировать индексы, которые замедляют, а не ускоряют работу базы данных.

ℹ️ Иллюстрация влияния индексов на производительность СУБД по сравнению с полным сканированием