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

В этой статье я покажу, как удалось сократить время выполнения медленного запроса на 50% с помощью составного индекса — используя команды PostgreSQL EXPLAIN и EXPLAIN ANALYZE для диагностики узких мест в производительности и повышения эффективности.

Почему команда EXPLAIN важна

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

✅ Как PostgreSQL планирует выполнение запроса
✅ Какие индексы и типы сканирования (Seq Scan, Index Scan, Bitmap Scan) будут использоваться
✅ Оптимизирован ли запрос или требуется доработка

С помощью EXPLAIN ANALYZE можно пойти дальше — фактически выполнить запрос и измерить реальное время его выполнения. Однако стоит помнить, что эта команда выполняет запрос, что требует соблюдения ряда предосторожностей.

Оптимизация медленного запроса

Проблема

Я столкнулся с проблемой производительности при выполнении следующего запроса:

1
2
3
4

SELECT *
FROM backlog bl
JOIN backlog_element elem ON elem.backlog_id = bl.id
WHERE elem.backlog_id = 1168201980 AND elem.deleted_at IS NULL;

Шаг 1: Выполнение EXPLAIN

1
2
3
4
5

EXPLAIN ANALYZE
SELECT *
FROM backlog bl
JOIN backlog_element elem ON elem.backlog_id = bl.id
WHERE elem.backlog_id = 1168201980 AND elem.deleted_at IS NULL;

Также можно использовать форматирование: FORMAT { TEXT | XML | JSON | YAML }.

Пример: EXPLAIN (ANALYZE, FORMAT TEXT).

Упрощённый вывод плана выполнения:

Nested Loop  (cost=0.10..32.99 rows=4 width=85) (actual time=0.056..123.456 rows=3 loops=1)
  ->  Index Scan using uidx_elem_backlog_id on backlog bl  (cost=0.05..2.65 rows=1 width=8) (actual time=0.034..0.035 rows=1 loops=1)
        Index Cond: (backlog_id = '1168201980'::bigint)
  ->  Index Scan using idx_backlog_element_backlog_id on backlog_element elem  (cost=0.05..30.32 rows=13 width=93) (actual time=0.019..123.421 rows=3 loops=1)
        Index Cond: (backlog_id = bl.id)
        Filter: (deleted_at IS NULL)
        Rows Removed by Filter: 2
Planning time: 0.309 ms
Execution time: 123.456 ms

📔 Как читать вывод

Стоимость важна (cost)!

В выводе мы видим 2 числа: начальная стоимость до возвращения первой записи и общая стоимость возвращения всех записей.

Для большинства запросов нас интересует общая стоимость.

Детально:

  1. Верхний уровень: Nested Loop

    • Cost: (cost=0.10..32.99 rows=4 width=85)
      • cost=0.10..32.99: Оценочная стоимость операции (единицы измерения условны, обычно отражают количество обращений к дисковым страницам).
      • rows=4: Оценочное количество возвращаемых строк.
      • width=85: Оценочная ширина каждой строки (в байтах).
    • Actual Time: (actual time=0.056..123.456 rows=3 loops=1)
      • actual time=0.056..123.456: Фактическое время начала и завершения операции.
      • rows=3: Фактическое количество возвращаемых строк.
      • loops=1: Количество запусков данной операции.

  2. Первый дочерний узел: Index Scan по таблице backlog

    • Cost: (cost=0.05..2.65 rows=1 width=8)
      • cost=0.05..2.65: Оценочная стоимость.
      • rows=1: Оценочное количество строк.
      • width=8: Оценочная ширина каждой строки.
    • Actual Time: (actual time=0.034..0.035 rows=1 loops=1)
      • actual time=0.034..0.035: Фактическое время выполнения.
      • rows=1: Фактическое количество строк.
      • loops=1: Количество запусков.
    • Index Cond: (backlog_id = '1168201980'::bigint)
      • Условие, применяемое для фильтрации строк.

  3. Второй дочерний узел: Index Scan по таблице backlog_element

    • Cost: (cost=0.05..30.32 rows=13 width=93)
      • cost=0.05..30.32: Оценочная стоимость.
      • rows=13: Оценочное количество строк.
      • width=93: Оценочная ширина каждой строки.
    • Actual Time: (actual time=0.019..123.421 rows=3 loops=1)
      • actual time=0.019..123.421: Фактическое время выполнения.
      • rows=3: Фактическое количество строк.
      • loops=1: Количество запусков.
    • Index Cond: (backlog_id = bl.id)
      • Условие для фильтрации строк.
    • Filter: (deleted_at IS NULL)
      • Дополнительное условие для исключения строк.
    • Rows Removed by Filter: 2
      • Количество строк, исключённых фильтром.

  4. Время планирования и выполнения

    • Planning time: 0.309 ms
      • Время, затраченное на создание плана запроса.
    • Execution time: 123.456 ms
      • Общее время выполнения запроса.

Основные выводы из плана выполнения:

  1. Nested Loop Join: Запрос использует вложенное соединение для объединения строк из таблиц backlog и backlog_element.
  2. Эффективное использование индексов:
    • Таблица backlog сканируется с помощью индекса uidx_elem_backlog_id для поиска конкретного backlog_id.
    • Таблица backlog_element сканируется с помощью индекса idx_backlog_element_backlog_id для поиска строк, соответствующих backlog_id из таблицы backlog.
  3. Дополнительная фильтрация:
    • В таблице backlog_element строки фильтруются для исключения тех, у которых поле deleted_at не равно NULL.
    • Из 5 первоначально выбранных строк 2 были исключены из-за условия фильтра, в итоге было возвращено 3 строки.

Шаг 2: Создание составного индекса

Составной индекс по полям (backlog_id, deleted_at) помогает, поскольку:

  • Он позволяет PostgreSQL быстро находить строки с backlog_id = 1168201980.
  • Благодаря включению поля deleted_at происходит эффективная фильтрация по дате и сортировка результатов.
1

CREATE INDEX idx_backlog_element_deleted ON backlog_element (backlog_id, deleted_at DESC);

Шаг 3: Проверка оптимизации с помощью EXPLAIN ANALYZE

1
2
3
4
5

EXPLAIN ANALYZE
SELECT *
FROM backlog bl
JOIN backlog_element elem ON elem.backlog_id = bl.id
WHERE elem.backlog_id = 1168201980 AND elem.deleted_at IS NULL;

🔍 Обновлённый вывод плана выполнения:

Nested Loop  (cost=0.30..16.35 rows=1 width=113) (actual time=0.042..61.728 rows=1 loops=1)
  ->  Index Scan using uidx_elem_backlog_id on backlog bl  (cost=0.15..8.17 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
        Index Cond: (backlog_id = 1)
  ->  Index Scan using idx_backlog_element_active on backlog_element elem  (cost=0.15..8.17 rows=1 width=121) (actual time=0.012..61.696 rows=1 loops=1)
        Index Cond: ((backlog_id = bl.id) AND (deleted_at IS NULL))  -- Этап фильтрации отсутствует!
Planning time: 0.309 ms
Execution time: 61.728 ms

Ускорение производительности 🚀

✅ Сокращение времени на 50%: Время выполнения запроса сократилось с 124ms → 62ms
✅ Использование Index Scan: PostgreSQL теперь сканирует только релевантное подмножество данных
✅ Эффективная сортировка: Дополнительные операции сортировки не требуются

⚠️ Предостережения при использовании EXPLAIN ANALYZE

Хотя EXPLAIN ANALYZE — мощный инструмент, он имеет некоторые особенности:

  • Побочные эффекты:
    EXPLAIN ANALYZE выполняет запрос, что означает, что если запрос не является только для чтения (например, содержит INSERT, UPDATE или DELETE), он может изменить данные. Всегда запускайте его в безопасной тестовой среде или применяйте к запросам только для чтения.

    💡 Используйте EXPLAIN ANALYZE в рамках транзакции, если запрос изменяет данные:

    1
2
3

    BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

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

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

  • Точность измерения времени:
    Указанное время выполнения включает фазу планирования и может не отражать время повторных запусков запроса. Для более точного тестирования проводите несколько замеров и усредняйте результаты.

Рекомендации по использованию EXPLAIN в реальной работе

1️⃣ Начинайте с EXPLAIN перед выполнением ресурсоёмких запросов: Если запрос работает медленно, сначала проверьте его план выполнения.

2️⃣ Используйте EXPLAIN ANALYZE для получения статистики реального выполнения: Он измеряет фактическое время выполнения, но выполняет запрос. Будьте осторожны при использовании на рабочих базах.

3️⃣ Обращайте внимание на Seq Scan в больших таблицах: Если обнаруживается последовательное сканирование большой таблицы, подумайте об индексировании.

4️⃣ Проверяйте использование индексов PostgreSQL: Иногда индексы существуют, но не используются из-за особенностей структуры запроса.

5️⃣ Разумное использование составных индексов: Индексы по (A, B) наиболее эффективны, когда сначала фильтруется по A, а затем по B.

6️⃣ Избегайте чрезмерного индексирования: Хотя индексы ускоряют чтение, они могут замедлять операции вставки/обновления. Создавайте индексы только при необходимости.

Заключение

Оптимизация запросов в PostgreSQL с использованием EXPLAIN — это мощный инструмент для backend-разработчиков. В данном случае, добавив составной индекс, удалось сократить время выполнения запроса на 50%, что повысило общую эффективность системы.

Читайте другие посты и присоединяйтесь к обсуждению темы в Телеграм: @time2code