Как анализировать медленную фильтрацию в базе данных с помощью EXPLAIN ANALYZE

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

Умеете ли вы читать план запроса, а не вешать индекс наугад. Интервьюер хочет услышать, на что вы смотрите в выводе и как отличаете отсутствие индекса от устаревшей статистики. Следом часто спрашивают, как получить план из Django или SQLAlchemy.

Как отвечать

EXPLAIN показывает план с оценками планировщика, EXPLAIN ANALYZE реально выполняет запрос и добавляет фактические цифры. Для медленного фильтра в PostgreSQL удобен EXPLAIN (ANALYZE, BUFFERS). Смотрите на:

• тип узла: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan. Последовательное чтение не приговор: на маленькой таблице или при слабой селективности оно быстрее;
• Rows Removed by Filter: если отброшено в сотни раз больше строк, чем осталось, фильтр идёт мимо индекса;
• оценка против факта: rows= планировщика против actual ... rows=. Расхождение на порядки обычно означает устаревшую статистику, помогает ANALYZE по таблице;
• actual time и loops: время дано на одну итерацию, во вложенном цикле его умножают на loops;
• Buffers: сколько страниц взято из кеша (hit), а сколько прочитано с диска (read).

Типичные причины медленного фильтра: индекса нет или он составной, а условие не по первой колонке; над колонкой функция (lower(email)), нужен индекс по выражению; несовпадение типов; LIKE '%текст%', который закрывается только GIN-индексом с pg_trgm.

Из Python план берут так: в Django queryset.explain(analyze=True, buffers=True), в SQLAlchemy выполняют text("EXPLAIN ANALYZE ...") с тем же SQL. Медленные запросы на проде ищут через pg_stat_statements и auto_explain.

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

Запускают EXPLAIN ANALYZE для UPDATE или DELETE на проде и удивляются изменённым данным. Такой запрос оборачивают в BEGIN и ROLLBACK.

Принимают cost за миллисекунды. Это условные единицы планировщика, реальное время только в actual time и Execution Time.

Проверяют план на локальной базе из ста строк. Планировщик там выберет Seq Scan и будет прав, выводы делают на данных, близких к боевым по объёму и распределению.