Как анализировать план выполнения запросов в PostgreSQL с помощью EXPLAIN и EXPLAIN ANALYZE?

Анализ плана выполнения запросов в PostgreSQL

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

Основные различия

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

EXPLAIN SELECT * FROM users WHERE age > 30;

Выведет стоимость операций и примерное количество строк.

EXPLAIN ANALYZE реально выполняет запрос и показывает фактические цифры:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Вывод включает реальное время выполнения, число обработанных строк и другие метрики.

Основные типы операций

• Seq Scan — полное сканирование таблицы (медленно на больших таблицах)
• Index Scan — использование индекса (быстро)
• Bitmap Index Scan — специализированное сканирование через индекс
• Hash Join / Nested Loop Join / Merge Join — способы соединения таблиц
• Sort — сортировка (может быть бутылочным горлышком)

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

Seq Scan on users (cost=0.00..35.50 rows=1000 width=32)
  Filter: (age > 30)
  Rows Removed by Filter: 5000

Значения:

• cost=0.00..35.50 — примерная стоимость (от 0 до 35.50 единиц)
• rows=1000 — предполагаемое количество строк (EXPLAIN) или реальное (ANALYZE)
• width=32 — средняя ширина строки в байтах
• Rows Removed by Filter: 5000 — сколько строк отфильтровано

Практический пример: оптимизация с индексом

-- Медленный запрос (полное сканирование)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = completed;

-- Seq Scan on orders (cost=0.00..10000.00 rows=50000 width=64)
-- Rows Removed by Filter: 950000

-- Добавляем индекс
CREATE INDEX idx_orders_status ON orders(status);

-- Быстрый запрос (индекс)
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = completed;

-- Index Scan using idx_orders_status on orders (cost=0.29..8000.00 rows=50000 width=64)

Опции EXPLAIN

-- Всё включить
EXPLAIN (ANALYZE true, BUFFERS true, FORMAT json)
SELECT * FROM users WHERE id = 1;

-- BUFFERS показывает использование памяти
-- FORMAT json удобен для программного анализа

Что смотреть при оптимизации

1. Отскачила ли стоимость после индекса? Если нет — индекс не помогает
2. Seq Scan vs Index Scan — индекс полезен только на больших таблицах
3. Rows Removed vs rows — если фильтруется много строк, нужен лучший индекс
4. Sort операции — дорогие, часто удаляются ORDER BY или добавлением индекса
5. Nested Loops — медленны при соединении больших таблиц, добавьте индекс на ключ связи

Когда EXPLAIN недоверять

• Планировщик может ошибаться при неточных статистиках
• Запустите ANALYZE на таблице: ANALYZE users;
• На маленьких таблицах индекс неэффективен (сама таблица в памяти)
• Параллельное выполнение может отличаться в production

В контексте Python приложения

from sqlalchemy import text
from sqlalchemy.orm import Session

def get_query_plan(session: Session, query_str: str):
    result = session.execute(text(f"EXPLAIN ANALYZE {query_str}"))
    return result.fetchall()

# Использование
results = get_query_plan(session, "SELECT * FROM users WHERE age > 30")
for row in results:
    print(row)

Этот подход полезен для мониторинга медленных запросов в production.