Как индексы влияют на производительность чтения и записи

Что проверяют

Знаете ли вы, что индекс ускоряет одни операции ценой других, и можете ли объяснить механику, а не повторить «индексы ускоряют поиск». Для Go-разработчика следом почти всегда идёт практика: как увидеть, что запрос из сервиса не попал в индекс, и что делать с медленной вставкой.

Как отвечать

• чтение: B-tree индекс заменяет полный проход по таблице спуском по дереву за логарифмическое число шагов. Помогает на WHERE, JOIN и ORDER BY ... LIMIT: отсортированный индекс избавляет от сортировки всей выборки;
• покрывающий индекс: если все нужные колонки есть в индексе (в PostgreSQL через INCLUDE), база отвечает без обращения к таблице, это Index Only Scan;
• запись: каждый INSERT обновляет все индексы таблицы, а UPDATE в PostgreSQL создаёт новую версию строки и, если не сработала HOT-оптимизация, тоже трогает каждый индекс. Пять индексов означают пять дополнительных структур на каждую вставку, плюс рост WAL и места на диске;
• когда индекс не поможет: низкая селективность, функция над колонкой, LIKE '%x%', несовпадение типа параметра с типом колонки;
• как проверять: EXPLAIN (ANALYZE, BUFFERS) на реальном запросе, pg_stat_user_indexes для поиска индексов, которые никто не читает, pg_stat_statements для самых дорогих запросов.

Со стороны Go добавьте практику массовой записи: пакетная вставка через pgx.Batch или CopyFrom вместо тысячи отдельных Exec. Для разовой загрузки больших объёмов иногда выгоднее снять индекс и построить его после загрузки, а на живой таблице индекс строят через CREATE INDEX CONCURRENTLY, чтобы не блокировать запись.

Где спотыкаются

Считают, что индекс ускоряет всё, включая UPDATE. Поиск строки для обновления он ускоряет, а саму запись делает дороже.

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

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