Освоение EXPLAIN ANALYZE: руководство по оптимизации планов запросов PostgreSQL

EXPLAIN ANALYZE — это то, к чему я прибегаю, когда запрос PostgreSQL кажется медленным, а обычные догадки уже не помогают. Возможно, запрос выглядит безобидным в коде приложения. Возможно, у таблицы есть индекс, и все предполагают, что база данных его использует. Возможно, запрос быстр на стенде, но медленен в продакшене. План — это то, где эти предположения либо подтверждаются, либо рушатся.

Полезная привычка — читать план как историю работы, выполненной PostgreSQL: какие строки он ожидал затронуть, какие строки он фактически затронул, где он выполнял соединение, где сортировал, оставался ли он в памяти и приходилось ли ему читать с диска. Вам не нужно запоминать каждый узел плана, прежде чем это станет полезным. Вам нужно замедлиться и сравнить оценки с реальностью.

Понимание EXPLAIN vs. EXPLAIN ANALYZE

Разница между EXPLAIN и EXPLAIN ANALYZE важна, потому что одно — это прогноз, а другое — измерение.

EXPLAIN

Когда вы запускаете запрос с префиксом EXPLAIN, PostgreSQL генерирует предполагаемый план выполнения, фактически не выполняя запрос. Это полезно для:

• Предварительного просмотра плана: Вы можете увидеть, что PostgreSQL считает самым дешевым способом выполнения вашего запроса.
• Оценки затрат: Он предоставляет оценки затрат для каждого узла в плане, давая вам относительное представление об использовании ресурсов.

Пример:

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE идет на шаг дальше. Он не только показывает вам запланированное выполнение, но и выполняет запрос, а затем сообщает фактические статистические данные выполнения. Это означает, что вы получаете:

• Фактическое время выполнения: Сколько времени действительно занял каждый шаг.
• Фактическое количество строк: Сколько строк было фактически обработано на каждом узле.
• Подтверждение оценок: Вы можете сравнить предполагаемое количество строк с фактическим, чтобы увидеть, делает ли планировщик PostgreSQL точные прогнозы.

Это делает EXPLAIN ANALYZE лучшим инструментом для реальной настройки, но у него есть острая грань: он выполняет запрос. SELECT все еще может быть дорогим, потому что он может сканировать много данных, захватывать блокировки или конкурировать за кэш. UPDATE, DELETE или INSERT будут фактически изменять данные, если вы не обернете их в транзакцию и не откатите:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

Этот шаблон полезен в окне обслуживания или на копии стенда. Это не бесплатный пропуск для выполнения опасных операторов на загруженной производственной базе данных.

Пример:

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

Расшифровка вывода EXPLAIN ANALYZE

Вывод EXPLAIN ANALYZE может показаться сложным на первый взгляд, но понимание его ключевых компонентов является основополагающим.

Основные компоненты:

• Тип узла: Определяет выполняемую операцию (например, Seq Scan, Index Scan, Hash Join, Nested Loop, Sort, Aggregate).
• Стоимость: Представлена как (startup_cost .. total_cost).
  • startup_cost: Стоимость получения первой строки.
  • total_cost: Стоимость получения всех строк.
  • Примечание: Стоимость — это произвольные единицы, используемые для сравнения, а не время или память напрямую.
• Строки: Предполагаемое количество строк, которое планировщик ожидает вернуть от этого узла.
• Ширина: Предполагаемая средняя ширина (в байтах) строк, возвращаемых этим узлом.
• Фактическое время: Представлено как (startup_time .. total_time). Это фактическое время в миллисекундах для выполнения этого узла.
  • startup_time: Фактическое время возврата первой строки.
  • total_time: Фактическое время возврата всех строк.
• Фактические строки: Фактическое количество строк, возвращенных этим узлом.
• Циклы: Количество раз, которое этот узел был выполнен. Для узлов верхнего уровня это обычно 1. Для вложенных операций может быть больше.

Пример интерпретации вывода:

Рассмотрим упрощенный пример Seq Scan (последовательного сканирования) большой таблицы:

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

Интерпретация:

• Seq Scan on users: База данных читает каждую строку в таблице users.
• cost=0.00..15000.00: Планировщик оценил общую стоимость примерно в 15000 единиц.
• rows=1000000: Планировщик оценил, что в таблице 1 миллион строк.
• actual time=0.020..150.500: На самом деле сканирование и фильтрация заняли 150,5 миллисекунды.
• rows=950000: На самом деле было возвращено 950 000 строк (после фильтрации).
• loops=1: Это сканирование было выполнено один раз.
• Filter: (registration_date > '2023-01-01'): Это условие, примененное для фильтрации строк.
• Rows Removed by Filter: 50000: 50 000 строк были отброшены фильтром.

Выявление узкого места: Не смотрите только на самое большое actual time. Также ищите узел, который выполняется много раз. Внутреннее сканирование вложенного цикла, которое занимает 0,2 мс, может выглядеть безобидно, пока loops=50000. В этом случае реальная стоимость примерно равна времени на цикл, умноженному на количество циклов.

Читайте изнутри наружу

Планы PostgreSQL — это деревья. Верхний узел возвращает конечный результат клиенту, но работа обычно начинается глубже в плане. Когда запрос соединяет orders, customers и order_items, верхняя строка может быть Aggregate, но реальная проблема может быть в сканировании или соединении ниже.

Обычно я читаю план в таком порядке:

1. Начните с самых глубоких узлов сканирования и спросите: прочитал ли PostgreSQL намного больше строк, чем возвращает запрос?
2. Сравните предполагаемые rows с фактическими rows.
3. Проверьте, имеют ли дорогие узлы высокие loops.
4. Ищите узлы Sort, Hash или Materialize, которые сбрасываются на диск.
5. Используйте BUFFERS, чтобы решить, является ли запрос в основном работой CPU/кэша или дисковым вводом-выводом.

Вот распространенный пример:

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Если вы видите последовательное сканирование миллионов строк orders, затем сортировку, а затем ограничение, база данных делает слишком много работы, прежде чем вернуть 20 запрошенных строк. Практичным индексом может быть:

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

После этого хороший план может использовать индекс, чтобы перейти непосредственно к самым новым заказам для этого клиента и остановиться после 20 строк. Точный план зависит от размера таблицы, статистики, версии PostgreSQL и распределения данных, но принцип стабилен: сопоставьте индекс с шаблоном фильтрации и сортировки, который вы фактически используете.

Распространенные узлы плана запроса и стратегии оптимизации

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

1. Последовательное сканирование (Seq Scan)

• Что это такое: Читает каждую строку в таблице. Это часто неэффективно для больших таблиц, особенно при фильтрации по определенным условиям.
• Когда это нормально: Для небольших таблиц или когда нужно получить большой процент строк таблицы. Последовательное сканирование не является автоматически плохим.
• Оптимизация: Создайте индекс на селективных столбцах фильтра, но проверьте это с помощью плана. Если предикат возвращает большую часть таблицы, PostgreSQL может правильно продолжать использовать последовательное сканирование.

2. Индексное сканирование (Index Scan)

• Что это такое: Использует индекс для поиска строк, соответствующих условию WHERE. PostgreSQL проходит по индексу, а затем извлекает соответствующие строки из таблицы.
• Оптимизация: Убедитесь, что индекс соответствует форме запроса. Для составного индекса порядок столбцов имеет значение. Индекс на (tenant_id, created_at) помогает запросу, который фильтрует по tenant_id и сортирует по created_at; он может не сильно помочь запросу, который фильтрует только по created_at.

3. Сканирование только индекса (Index Only Scan)

• Что это такое: Оптимизированное Index Scan, где все данные, необходимые для запроса, доступны непосредственно в индексе. PostgreSQL не нужно обращаться к куче таблицы.
• Когда это эффективно: Когда все выбранные столбцы доступны из индекса, а карта видимости позволяет PostgreSQL избежать многих проверок кучи.
• Оптимизация: Рассмотрите покрывающий индекс с INCLUDE для путей с интенсивным чтением, но не добавляйте каждый столбец "на всякий случай". Более крупные индексы требуют больше затрат на поддержку при записи.

4. Операции соединения (Nested Loop, Hash Join, Merge Join)

• Nested Loop: Для каждой строки внешнего отношения PostgreSQL сканирует внутреннее отношение. Эффективен для небольших внешних отношений или когда к внутреннему отношению можно быстро получить доступ через индекс.
• Hash Join: Строит хеш-таблицу из одного отношения (сторона построения) и проверяет ее строками из другого отношения (сторона проверки). Эффективен для больших таблиц, где индексы не полезны для условия соединения.
• Merge Join: Требует, чтобы оба отношения были отсортированы по ключам соединения. Объединяет отсортированные списки. Эффективен для больших, уже отсортированных входных данных.
• Оптимизация:
  • Убедитесь, что существуют индексы на столбцах соединения.
  • Проверьте, не привели ли плохие оценки строк к неправильному выбору соединения. PostgreSQL не поддерживает нативные подсказки оптимизатора в том же стиле, что и некоторые базы данных, поэтому обычными исправлениями являются лучшая статистика, лучшие индексы или переписывание запроса.
  • Проверьте EXPLAIN ANALYZE на наличие больших значений loops или высокого actual time на узлах соединения.

5. Сортировка (Sort)

• Что это такое: Упорядочивает строки. Может быть вычислительно затратным, особенно на больших наборах данных.
• Оптимизация:
  • Добавьте индекс, порядок столбцов которого соответствует шаблону ORDER BY, когда запрос достаточно селективен.
  • Уменьшите количество сортируемых строк, добавив более ограничивающие условия WHERE.
  • Убедитесь, что work_mem настроен достаточно, чтобы сортировка выполнялась в памяти, а не на диске.

6. Агрегации (Aggregate)

• Что это такое: Выполняет операции, такие как COUNT(), SUM(), AVG(), GROUP BY.
• Оптимизация:
  • Убедитесь, что условия WHERE эффективны, уменьшая количество строк перед агрегацией.
  • Рассмотрите использование материализованных представлений для предварительно агрегированных данных, если агрегация является частой и медленной операцией.
  • Индексируйте столбцы, используемые в условиях GROUP BY.

Использование EXPLAIN ANALYZE с опциями

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

VERBOSE

• Что делает: Отображает дополнительную информацию о плане запроса, такую как имена таблиц с указанием схемы и имена выходных столбцов.

EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

• Что делает: Включает предполагаемые затраты в вывод. Это поведение по умолчанию, но вы можете явно его отключить.

EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

• Что делает: Сообщает информацию об использовании буферов (общих, временных и локальных). Это помогает выявить узкие места ввода-вывода.
  • shared hit: Блоки, найденные в общем буферном кэше PostgreSQL.
  • shared read: Блоки, прочитанные с диска в общие буферы.
  • temp read/written: Блоки, прочитанные/записанные во временные файлы (часто для сортировок или хешей, превышающих work_mem).

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

• Что делает: Включает фактическое время запуска и общее время для каждого узла. Это поведение по умолчанию для ANALYZE.

EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

Комбинирование опций

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

Практические советы и лучшие практики

• Начинайте с EXPLAIN ANALYZE: Всегда используйте EXPLAIN ANALYZE для анализа производительности в реальных условиях. Одного EXPLAIN недостаточно.
• Сосредоточьтесь на actual time: Уделяйте первоочередное внимание оптимизации узлов с самым высоким actual time.
• Сравнивайте rows (оцененные и фактические): Большие расхождения указывают на то, что планировщик запросов PostgreSQL может делать неточные предположения. Это часто можно исправить, обновив статистику таблицы с помощью ANALYZE <table_name>; или создав соответствующие индексы.
• Используйте BUFFERS: Анализируйте использование буферов, чтобы понять, связан ли ваш запрос с вводом-выводом.
• Тестируйте с реалистичными данными: Запускайте EXPLAIN ANALYZE на базе данных, которая имеет репрезентативный объем данных и аналогичное распределение данных, как в вашей производственной среде.
• Оптимизируйте поэтапно: Не пытайтесь оптимизировать все сразу. Сначала устраните самое большое узкое место.
• Учитывайте work_mem: Если вы видите значительное чтение с диска для сортировки или хеширования (temp read/written в BUFFERS), увеличение work_mem (на сессию или глобально) может помочь, но помните об использовании памяти.
• Индексируйте с умом: Создавайте только те индексы, которые действительно используются и полезны. Слишком много индексов может замедлить запись и потреблять дисковое пространство.
• Проверяйте версию PostgreSQL: Более новые версии часто имеют улучшенные планировщики запросов и новые функции, которые могут повлиять на производительность.

Практический проход настройки

Возьмем этот запрос:

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Если план показывает последовательное сканирование, индекс только на email может не помочь, потому что запрос применяет lower(email). PostgreSQL не всегда может использовать обычный индекс, когда выражение в запросе отличается от индексированного значения. Лучшим вариантом может быть индекс по выражению:

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

Затем запустите снова:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

Вы ищете меньшее количество просканированных строк, меньшее количество прочитанных буферов и меньшее время выполнения. Если план все еще не использует индекс, проверьте, не является ли таблица крошечной, не устарела ли статистика или не написан ли запрос не так, как вы думаете, что его отправляет приложение.

Другой распространенный случай — соединение, которое выглядит нормально в SQL, но "взрывается" в плане:

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

Полезные индексы могут включать orders(created_at), order_items(order_id) и первичный ключ на products(id). Но если последние семь дней включают большую часть таблицы orders, orders(created_at) может не быть основным исправлением. План говорит вам, является ли реальной проблемой фильтр даты, разветвление соединения или отсутствующий индекс на дочерней таблице.

Хорошая настройка запросов PostgreSQL — это не "добавьте индекс, пока план не изменится". Это цикл: измерьте реальный план, сделайте одно обоснованное изменение, измерьте снова и оставьте изменение только в том случае, если оно улучшает рабочую нагрузку, которая вам действительно важна.