Как посмотреть план и время выполнения запроса в SQL?

Как посмотреть план и время выполнения SQL запроса

Оптимизация SQL запросов начинается с анализа их плана выполнения. Рассмотрю инструменты и методы для разных БД.

Способ 1: EXPLAIN в PostgreSQL

Основной инструмент для анализа:

-- Простой EXPLAIN
EXPLAIN
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

-- Вывод показывает план исполнения, но не реальное время

Вывод:

Sort  (cost=45.32..45.33 rows=100 width=40)
  Sort Key: (count(p.id)) DESC
  ->  HashAggregate  (cost=40.23..42.23 rows=100 width=40)
        Group Key: u.id, u.name
        ->  Hash Left Join  (cost=10.00..35.20 rows=1000 width=32)
              Hash Cond: (p.user_id = u.id)
              ->  Seq Scan on posts p  (cost=0.00..20.00 rows=1000 width=8)
              ->  Hash  (cost=5.00..5.00 rows=100 width=24)
                    ->  Seq Scan on users u  (cost=0.00..5.00 rows=100 width=24)

Способ 2: EXPLAIN ANALYZE (реальное время)

Показывает фактическое время выполнения:

-- Показывает как план так и реальное время
EXPLAIN ANALYZE VERBOSE
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

Вывод:

HashAggregate  (cost=40.23..42.23 rows=100 width=40)
  (actual time=12.543..12.655 rows=95 width=40)  <-- Реальное время
  Group Key: u.id, u.name
  ->  Hash Left Join  (cost=10.00..35.20 rows=1000 width=32)
        (actual time=0.523..8.234 rows=950 width=32)
        Hash Cond: (p.user_id = u.id)

Способ 3: EXPLAIN FORMAT JSON

Для программной обработки:

EXPLAIN (FORMAT JSON, ANALYZE, VERBOSE)
SELECT * FROM users WHERE id = 1;

Вывод (JSON):

[
  {
    "Plan": {
      "Node Type": "Index Scan",
      "Index Name": "users_pkey",
      "Actual Rows": 1,
      "Actual Time": 0.023,
      "Total Cost": 0.28
    },
    "Execution Time": 0.045,
    "Planning Time": 0.102
  }
]

Способ 4: Python с psycopg2

import psycopg2
import json

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

# Получить план выполнения
query = "SELECT * FROM users WHERE age > %s"
cur.execute(f"EXPLAIN (FORMAT JSON, ANALYZE) {query}", (18,))

plan = json.loads(cur.fetchone()[0])
print(json.dumps(plan, indent=2))

# Извлечь полезную информацию
for item in plan:
    exec_time = item.get('Execution Time')
    planning_time = item.get('Planning Time')
    rows = item['Plan'].get('Actual Rows')
    
    print(f"Время выполнения: {exec_time:.3f} ms")
    print(f"Время планирования: {planning_time:.3f} ms")
    print(f"Реальные строки: {rows}")

Способ 5: SQLAlchemy с EXPLAIN

from sqlalchemy import text, select
from sqlalchemy.orm import Session

def analyze_query(session: Session, query_str: str):
    """Анализирует план выполнения запроса."""
    
    # Для PostgreSQL
    explain_query = f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {query_str}"
    
    result = session.execute(text(explain_query))
    plan = json.loads(result.scalar())
    
    return plan

# Использование
from app.models import User

query = (
    select(User)
    .where(User.age > 18)
    .order_by(User.created_at.desc())
)

query_str = str(query.compile(compile_kwargs={"literal_binds": True}))
plan = analyze_query(session, query_str)

print(f"Время: {plan[0]['Execution Time']:.2f} ms")
print(f"Строк: {plan[0]['Plan']['Actual Rows']}")

Способ 6: MySQL EXPLAIN

-- MySQL синтаксис
EXPLAIN
SELECT u.name, COUNT(p.id)
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- Вывод таблицей
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100  |   100.00 | NULL                |
|  1 | SIMPLE      | p     | NULL       | ref  | user_id       | user_id | 8 | db.u.id | 10 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------+

Ключевые колонки:

• type: ALL (полный скан), INDEX, RANGE, REF — от худшего к лучшему
• rows: предполагаемое число строк
• Extra: дополнительная информация (Using filesort, Using temporary)

Способ 7: Время выполнения в CLI

# PostgreSQL
psql -h localhost -U postgres -d mydb -c "\timing" -c "SELECT * FROM users"

# MySQL
mysql -u root -p -e "SELECT * FROM users" && echo "Time taken: "

# SQLite
sqlite3 database.db
sqlite> .timer ON
sqlite> SELECT * FROM users;

Способ 8: pg_stat_statements (PostgreSQL)

Для мониторинга реальных запросов в production:

-- Включить расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Самые медленные запросы
SELECT 
    query,
    calls,
    mean_exec_time,
    max_exec_time,
    stddev_exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Способ 9: Анализ индексов

-- Какие индексы доступны
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Какие индексы используются
EXPLAIN
SELECT * FROM users WHERE email = 'test@example.com';

-- Неиспользуемые индексы
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Способ 10: BUFFERS для анализа I/O

-- Показывает сколько блоков прочитано из памяти/диска
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM large_table WHERE condition = true;

-- Вывод
/*
Seq Scan on large_table  (cost=0.00..35000.00 rows=1000000 width=32)
  (actual time=0.043..500.234 rows=500000 width=32)
  Filter: (condition = true)
  Buffers: shared hit=2000 read=1500  <-- 2000 из памяти, 1500 с диска
*/

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

Красные флаги (плохие знаки)

-- ❌ Sequential Scan на большой таблице
Seq Scan on large_table  (rows=1000000)  <-- Полный скан!

-- ❌ Hash Join когда должен быть Nested Loop
Hash Join  (rows=100000)  <-- Много памяти

-- ❌ Sort (требует дополнительной памяти)
Sort  (rows=50000)
  ->  Hash Aggregate  <-- Дополнительная сортировка

-- ❌ Filter с низким процентом
Filter: (status = 'active')  (rows=100000 -> 5000, filtered=5%)  <-- 95% отброшено

Зелёные флаги (хорошие знаки)

-- ✅ Index Scan
Index Scan using users_id_idx  (rows=1)  <-- Быстро и точно

-- ✅ Nested Loop с индексом
Nested Loop  (rows=10)
  ->  Index Scan
  ->  Index Scan

-- ✅ Реальные строки близко к предполагаемым
(actual 95 rows) ... (rows=100)  <-- Оптимизатор угадал

Способ 11: ANALYZE TABLE (обновление статистики)

-- PostgreSQL
ANALYZE users;  -- Обновить статистику

-- MySQL
ANALYZE TABLE users;

-- После этого оптимизатор будет лучше выбирать план

Практический пример (полный анализ)

import psycopg2
import json
import time

def analyze_slow_query(conn, query: str, params: tuple):
    """Полный анализ медленного запроса."""
    cur = conn.cursor()
    
    # 1. Получить план
    explain_query = f"EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) {query}"
    cur.execute(explain_query, params)
    plan = json.loads(cur.fetchone()[0])[0]
    
    # 2. Извлечь метрики
    exec_time = plan['Execution Time']
    planning_time = plan['Planning Time']
    rows = plan['Plan']['Actual Rows']
    buffers = plan['Plan'].get('Buffers', {})
    
    # 3. Вывести отчёт
    print("=" * 50)
    print(f"Запрос: {query[:60]}...")
    print("=" * 50)
    print(f"Время выполнения: {exec_time:.2f} ms")
    print(f"Время планирования: {planning_time:.3f} ms")
    print(f"Строк получено: {rows}")
    
    if buffers:
        print(f"Буферы - Попадания в памяти: {buffers.get('shared_hit_blocks', 0)}")
        print(f"Буферы - Чтение с диска: {buffers.get('shared_read_blocks', 0)}")
    
    # 4. Рекомендации
    if exec_time > 1000:
        print("⚠️  МЕДЛЕННО: более 1 секунды")
    elif exec_time > 100:
        print("⚠️  НЕОПТИМАЛЬНО: более 100ms")
    else:
        print("✅ ХОРОШО: менее 100ms")
    
    cur.close()
    return plan

# Использование
conn = psycopg2.connect("dbname=mydb user=postgres")
query = "SELECT * FROM users WHERE age > %s ORDER BY created_at DESC"
analyze_slow_query(conn, query, (18,))

Лучшие практики

• EXPLAIN ANALYZE всегда для реальных запросов
• Смотрите Actual rows vs rows — если сильно отличаются, нужна ANALYZE таблицы
• Проверяйте индексы — Index Scan лучше Seq Scan
• Мониторьте в production через pg_stat_statements
• Используйте BUFFERS для I/O анализа
• Не гонитесь за миллисекундами — сначала избавьтесь от секунд

Понимание плана выполнения — ключ к оптимизации SQL запросов.