Запросы замедлились за недели, хотя EXPLAIN показывает использование индекса — найдите причину
Горячая таблица orders принимает тяжёлый трафик UPDATE/DELETE. Поиск, который был мгновенным, медленно деградировал за недели, хотя индекс всё ещё выбирается. Прочитайте этот план и определите, почему он медленный.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
Index Scan using orders_user_id_idx on orders
(cost=0.43..2841 rows=12 width=240)
(actual time=0.05..38.7 rows=12 loops=1)
Index Cond: (user_id = 42)
Buffers: shared hit=9 read=4120
Planning Time: 0.20 ms
Execution Time: 38.92 msОпределите причину и скажите, как бы вы это исправили.
Индекс всё ещё выбран, но возвращает 12 строк, трогая 4120 буферов — признак bloat: мёртвые строки MVCC от тяжёлого трафика UPDATE/DELETE копились быстрее, чем autovacuum успевал их вернуть, поэтому heap и индекс полны мёртвых страниц, через которые скан вынужден продираться. Исправление: сделать autovacuum агрессивнее на этой таблице, выполнить VACUUM и REINDEX (или pg_repack), чтобы перестроить раздутый индекс.
- ✗Читать «индекс используется» как доказательство, что план в порядке, игнорируя разрыв строк и буферов
- ✗Винить устаревшую статистику или отсутствие составного индекса вместо bloat
- ✗Считать, что тяжёлые чтения буферов всегда означают малый кэш
- →Какие столбцы
pg_stat_user_tablesподтверждают избыток мёртвых строк в таблице? - →Почему
REINDEX CONCURRENTLYважен на таблице, которой нельзя простоя?
Что в плане
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
Index Scan using orders_user_id_idx on orders
(cost=0.43..2841 rows=12 width=240)
(actual time=0.05..38.7 rows=12 loops=1)
Index Cond: (user_id = 42)
Buffers: shared hit=9 read=4120
Planning Time: 0.20 ms
Execution Time: 38.92 msДиагноз
План выбран правильный — это Index Scan, индекс используется. Но обратите внимание на разрыв: запрос возвращает 12 строк, а трогает 4120 буферов (read=4120). Чтобы вернуть десяток строк, скан читает тысячи страниц — это bloat.
Таблица под тяжёлым UPDATE/DELETE. MVCC оставляет мёртвые версии строк после каждого изменения; они копились быстрее, чем autovacuum успевал их вернуть. В итоге heap и сам индекс заполнены мёртвыми страницами, через которые скан вынужден продираться, хотя живых строк всего 12.
✅ Исправление:
- сделать
autovacuumагрессивнее на этой таблице (autovacuum_vacuum_scale_factorпониже,autovacuum_vacuum_cost_limitповыше); - выполнить
VACUUM(вернуть мёртвые строки для повторного использования); REINDEX CONCURRENTLYилиpg_repack, чтобы перестроить раздутый индекс без простоя.
Подтвердить можно по pg_stat_user_tables.n_dead_tup и расширению pgstattuple.
