Как использовать план запроса для улучшения производительности?

Использование плана запроса для улучшения производительности

План запроса (Query Plan) — это шаг за шагом описание того, как база данных будет выполнять SQL запрос. Анализ плана позволяет выявить узкие места и оптимизировать запросы.

1. Получение плана запроса

PostgreSQL

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# EXPLAIN показывает план без выполнения
cur.execute("EXPLAIN SELECT * FROM users WHERE age > 30")
for row in cur.fetchall():
    print(row[0])

# EXPLAIN ANALYZE показывает план с фактическими данными
cur.execute("EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30")
for row in cur.fetchall():
    print(row[0])

SQLAlchemy

from sqlalchemy import text
from sqlalchemy.dialects import postgresql

# Визуализация плана
query = text("SELECT * FROM users WHERE age > :age")
print(query.compile(dialect=postgresql.dialect()))

2. Интерпретация плана запроса

Типичный вывод EXPLAIN:

Seq Scan on users  (cost=0.00..35.50 rows=100 width=32)
  Filter: (age > 30)

Ключевые метрики:

• cost=0.00..35.50 — начальная стоимость и полная стоимость (в условных единицах)
• rows=100 — ожидаемое количество строк
• width=32 — средний размер строки в байтах

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

# Плохо: Seq Scan (полное сканирование таблицы)
# Запрос без индекса
SELECT * FROM users WHERE email = test@example.com

# Хорошо: Index Scan (использование индекса)
# После создания индекса
CREATE INDEX idx_users_email ON users(email)
SELECT * FROM users WHERE email = test@example.com

Типы сканирования:

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

4. Оптимизационные техники

Добавление индексов

# Простой индекс
CREATE INDEX idx_user_age ON users(age)

# Составной индекс
CREATE INDEX idx_user_age_city ON users(age, city)

# Частичный индекс (для фильтрации)
CREATE INDEX idx_active_users ON users(id) WHERE status = "active"

ANALYZE таблицы

# Обновить статистику для оптимизатора
ANALYZE users
ANALYZE

Переписание запроса

# Плохо: Использование функции в WHERE (отключает индекс)
SELECT * FROM users WHERE EXTRACT(YEAR FROM created_at) = 2024

# Хорошо: Диапазон дат
SELECT * FROM users WHERE created_at >= "2024-01-01" AND created_at < "2025-01-01"

5. Практический пример оптимизации

from sqlalchemy import create_engine, text

engine = create_engine("postgresql://...")

# Анализируем медленный запрос
with engine.connect() as conn:
    result = conn.execute(text("""
        EXPLAIN ANALYZE
        SELECT u.id, u.name, COUNT(o.id) as orders
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        WHERE u.created_at > "2024-01-01"
        GROUP BY u.id
    """))
    print(result.fetchall())

# Создаём индекс
with engine.begin() as conn:
    conn.execute(text("""
        CREATE INDEX idx_orders_user_id ON orders(user_id)
    """))
    conn.execute(text("ANALYZE orders"))

6. Метрики для отслеживания

При анализе плана обращайте внимание на:

• Избегайте Seq Scan на больших таблицах
• Следите за количеством строк (rows)
• Проверяйте стоимость операции (cost)
• Избегайте вложенных циклов для больших наборов
• Используйте LIMIT для ограничения результатов

Пример улучшения:

# До оптимизации: Seq Scan, cost=0..1000, rows=10000
# После индекса: Index Scan, cost=0..50, rows=100

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