Оптимизация Python и PostgreSQL для профи
Анализ планов EXPLAIN ANALYZE
От теории к практике: EXPLAIN ANALYZE
Когда запрос к базе данных выполняется медленно, первой мыслью часто бывает обвинить сам SQL. Но чтобы понять, почему он медленный, нужно заглянуть «под капот» PostgreSQL. Для этого существует команда EXPLAIN.
EXPLAINпоказывает предполагаемый план выполнения запроса. Он говорит, как PostgreSQL думает выполнить ваш запрос, основываясь на статистике таблиц. Это его гипотеза.
Но гипотезы могут быть ошибочными. Вот почему существует EXPLAIN ANALYZE. Эта команда не просто строит план, а реально выполняет запрос и показывает, что произошло на самом деле: сколько времени занял каждый шаг и сколько строк было обработано. Это отчет о реальных событиях, а не предположение.
Команда EXPLAIN ANALYZE позволяет проверить, насколько точны оценки планировщика.
Чтение плана выполнения
Вывод EXPLAIN ANALYZE представляет собой дерево узлов, где каждый узел — это отдельная операция. Дерево читается снизу вверх и изнутри наружу. Самые глубоко вложенные операции выполняются первыми.
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 123;
Рассмотрим ключевые метрики в каждой строке:
- cost: Первая цифра — это предполагаемая «стоимость» получения первой строки, вторая — всех строк. Это абстрактные единицы, а не миллисекунды. Они полезны для сравнения разных планов одного и того же запроса.
- actual time: Реальное время в миллисекундах. Первая цифра — время до получения первой строки, вторая — до получения всех строк на данном узле.
- rows: Предполагаемое количество строк, которое вернет узел.
- rows (actual): Фактическое количество строк. Большое расхождение между оценкой и фактом — верный признак устаревшей статистики и неоптимального плана.
- loops: Сколько раз выполнялся данный узел.
Основное внимание следует уделять узлам с самым высоким actual time.
Типы операций сканирования
Самые распространенные операции, с которых начинается выполнение запроса, — это сканирование таблиц. От выбора типа сканирования напрямую зависит производительность.
Seq Scan
other
Последовательное сканирование (Sequential Scan). PostgreSQL читает всю таблицу от начала до конца. Это эффективно для маленьких таблиц или когда запрос должен вернуть большую часть строк.
Если вы видите Seq Scan для большого объема данных при поиске нескольких строк, это проблема. Скорее всего, отсутствует нужный индекс.
Index Scan
other
Индексное сканирование. Используется индекс для быстрого поиска конкретных строк, не читая всю таблицу. Идеально для высокоселективных запросов (которые возвращают мало строк).
Это самый желаемый тип сканирования для поиска по уникальному или почти уникальному значению.
Bitmap Index Scan
other
Сканирование по битовой карте. Это двухэтапный процесс. Сначала PostgreSQL использует индекс для создания в памяти «битовой карты» страниц данных, содержащих нужные строки. Затем он последовательно считывает только эти страницы из таблицы. Этот метод эффективен, когда запрос не настолько селективен для Index Scan, но достаточно, чтобы не читать всю таблицу.
Анализ ввода-вывода и поиск «узких мест»
Часто производительность упирается не в процессор, а в чтение данных с диска. Чтобы это увидеть, используйте опцию BUFFERS.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE country = 'RU';
Она добавит в вывод информацию о буферах:
- Shared Hit: Блок данных был найден в общем кеше PostgreSQL. Это быстро.
- Shared Read: Блок данных не был найден в кеше и был прочитан с диска. Это медленно.
Большое количество Shared Read на узле указывает на интенсивную дисковую активность, которая может быть причиной задержек.
Для системного поиска проблемных запросов одного EXPLAIN ANALYZE недостаточно. Нужно знать, какие запросы в целом создают наибольшую нагрузку. Для этого существует расширение pg_stat_statements.
Сначала его нужно активировать:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
А затем добавить pg_stat_statements в shared_preload_libraries в файле postgresql.conf и перезапустить сервер.
После этого PostgreSQL начнет собирать статистику по всем выполняемым запросам. Вы можете посмотреть ее так:
SELECT
total_exec_time,
mean_exec_time,
calls,
rows,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Этот запрос покажет 10 самых «тяжелых» запросов по общему времени выполнения. Это главные кандидаты на оптимизацию. Взяв такой запрос, вы можете проанализировать его с помощью EXPLAIN ANALYZE и понять причину его медлительности.
Иногда текстовый вывод плана бывает сложно читать. К счастью, есть инструменты для его визуализации, которые превращают текст в наглядную графическую схему. Один из самых популярных — explain.depesz.com. Просто скопируйте туда вывод вашей команды, и он построит понятное дерево операций с подсветкой самых затратных узлов.
Теперь вы готовы к более глубокому анализу. Давайте проверим ваши знания.
В чем ключевое различие между командами EXPLAIN и EXPLAIN ANALYZE в PostgreSQL?
При анализе вывода EXPLAIN ANALYZE вы заметили, что для одного из узлов предполагаемое количество строк (rows) равно 10, а фактическое (actual rows) — 500 000. На какую проблему это, скорее всего, указывает?
Понимание планов выполнения — это ключ к оптимизации производительности. Регулярно используя EXPLAIN ANALYZE и pg_stat_statements, вы сможете находить и устранять «узкие места» в работе ваших Python-приложений с PostgreSQL.