PrepBro
← Назад к вопросам

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

1.7 Middle🔥 221 комментариев
#SQL и базы данных

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI26 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Оптимизация медленных SQL-запросов

Медленные запросы — частая проблема для Product Analyst'ов. Я подожду на результатах аналитики, но процесс поиска узких мест хорошо структурирован. Вот мой пошаговый подход к оптимизации.

1. Диагностика: EXPLAIN и ANALYZE

Первый шаг — понять, ПОЧЕМУ запрос медленный. Используем EXPLAIN (ANALYZE, BUFFERS).

-- Плохо: просто смотрим план без данных
EXPLAIN SELECT * FROM users WHERE created_at > '2024-01-01';

-- Хорошо: смотрим план ВЫПОЛНЕНия с реальными данными
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE created_at > '2024-01-01';

Что ищем в выводе:

  • Seq Scan (полный скан таблицы) — плохо, нужен индекс
  • Index Scan — хорошо, используется индекс
  • Nested Loop — может быть медленно при больших таблицах
  • Hash Join — быстрее Nested Loop
  • Rows — реальное количество строк (если число растет в 1000 раз, это ненормально)
  • Actual time: X.XXX ms — реальное время выполнения
  • Buffers: reads=X shared=Y — сколько блоков прочитано с диска
-- Пример "плохого" плана:
EXPLAIN (ANALYZE) 
SELECT COUNT(*) FROM events 
WHERE DATE(created_at) = '2024-03-15';

-- Вывод:
-- Seq Scan on events (actual time=1500.5..2100.3)
-- Filter: (DATE(created_at) = '2024-03-15')
-- Rows: 50000 returned
-- Planning time: 0.2ms
-- Execution time: 2100.5ms

-- ПРОБЛЕМА: Seq Scan (полный скан) + функция DATE (блокирует индекс)

2. Основные проблемы и решения

Проблема 1: Отсутствие индекса на столбцах в WHERE

-- Медленно: нет индекса
SELECT COUNT(*) FROM users WHERE status = 'active';
-- Seq Scan: 5,000,000 rows
-- Time: 3,500ms

-- Решение: добавить индекс
CREATE INDEX idx_users_status ON users(status);

-- Быстро: есть индекс
SELECT COUNT(*) FROM users WHERE status = 'active';
-- Index Scan using idx_users_status
-- Time: 45ms
-- Ускорение: 77x

Проблема 2: Функции на столбцах с индексами

-- Медленно: функция блокирует индекс
SELECT COUNT(*) FROM events 
WHERE DATE(created_at) = '2024-03-15';
-- Time: 2,100ms (полный скан несмотря на индекс)

-- Быстро: используй диапазон вместо функции
SELECT COUNT(*) FROM events 
WHERE created_at >= '2024-03-15'::TIMESTAMPTZ 
  AND created_at < '2024-03-16'::TIMESTAMPTZ;
-- Time: 120ms (используется индекс)
-- Ускорение: 17x

Проблема 3: JOINS без индексов

-- Медленно: Nested Loop (O(n*m) сложность)
SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- План: Nested Loop -> Seq Scan users -> Seq Scan orders
-- Time: 8,500ms

-- Решение: добавить индекс на JOIN ключ
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Быстро: Hash Join
SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- План: Hash Join -> Seq Scan users -> Index Scan orders
-- Time: 450ms
-- Ускорение: 18x

Проблема 4: DISTINCT на большом наборе

-- Медленно: сортировка миллионов строк
SELECT DISTINCT user_id FROM events WHERE year = 2024;
-- Time: 5,200ms

-- Быстро: использование GROUP BY
SELECT user_id FROM events WHERE year = 2024 GROUP BY user_id;
-- Time: 800ms
-- Или еще быстрее с индексом

-- Но если нужна сортировка:
SELECT user_id 
FROM (SELECT user_id FROM events WHERE year = 2024 GROUP BY user_id LIMIT 100000)
ORDER BY user_id;

Проблема 5: Отсутствие LIMIT

-- Медленно: загружает ВСЕ результаты
SELECT * FROM events 
WHERE timestamp > '2024-01-01'
ORDER BY timestamp DESC;
-- Полный скан, сортировка, время: 10,000ms

-- Быстро: берем только что нужно
SELECT * FROM events 
WHERE timestamp > '2024-01-01'
ORDER BY timestamp DESC
LIMIT 1000;
-- Time: 200ms
-- Ускорение: 50x

3. Составные индексы (Composite Indexes)

Для запросов с несколькими условиями в WHERE нужны составные индексы.

-- Таблица:
CREATE TABLE user_events (
    user_id UUID,
    event_type VARCHAR,
    created_at TIMESTAMPTZ,
    amount DECIMAL
);

-- Частый запрос:
SELECT SUM(amount) FROM user_events
WHERE user_id = 'abc-123' 
  AND event_type = 'purchase'
  AND created_at > '2024-01-01';

-- Плохо: три отдельных индекса
CREATE INDEX idx_user ON user_events(user_id);
CREATE INDEX idx_type ON user_events(event_type);
CREATE INDEX idx_date ON user_events(created_at);
-- БД использует только ОДН из них (обычно наименее селективный)

-- Хорошо: один составной индекс
CREATE INDEX idx_user_events_composite 
ON user_events(user_id, event_type, created_at DESC);

-- Правило для составного индекса: (equality, equality, range)
-- user_id = (equality) -> event_type = (equality) -> created_at > (range)

4. Денормализация и материализованные представления

Для очень часто используемых сложных запросов можно кешировать результаты.

-- Исходный запрос (медленный):
SELECT 
    u.id, u.name,
    COUNT(*) as total_orders,
    SUM(o.amount) as lifetime_value,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- Time: 15,000ms (этот запрос часто используется в дашбордах)

-- Решение: материализованное представление
CREATE MATERIALIZED VIEW user_metrics AS
SELECT 
    u.id, u.name,
    COUNT(o.id) as total_orders,
    COALESCE(SUM(o.amount), 0) as lifetime_value,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Запрос теперь: Time: 50ms

-- Обновляем каждый час
REFRESH MATERIALIZED VIEW user_metrics;

5. Статистика: ANALYZE TABLE

Постгресс использует статистику для планирования. Если статистика устарела, план может быть неоптимальный.

-- Обновляем статистику
ANALYZE events;

-- Или более детально
ANALYZE events(created_at, user_id, event_type);

-- Проверяем статистику
SELECT schemaname, tablename, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE tablename = 'events';

6. Batch операции вместо единичных

-- Медленно: 1000 отдельных запросов
FOR user_id IN (select_users):
    SELECT COUNT(*) FROM orders WHERE user_id = user_id;

-- Быстро: один запрос с GROUP BY
SELECT user_id, COUNT(*)
FROM orders
WHERE user_id IN (select_users)
GROUP BY user_id;

-- Или еще быстрее с массивом:
WITH users_to_check AS (
    SELECT UNNEST(ARRAY['user1', 'user2', 'user3']) as user_id
)
SELECT u.user_id, COUNT(o.id) as order_count
FROM users_to_check u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

7. Пошаговый процесс оптимизации

def optimize_slow_query(query_text: str) -> dict:
    """
    Пошаговая оптимизация медленного запроса
    """
    steps = {
        "Step 1: Baseline": "Запускаю EXPLAIN (ANALYZE, BUFFERS)",
        "Step 2: Identify bottleneck": "Ищу Seq Scan, отсутствие индексов, функции в WHERE",
        "Step 3: Check statistics": "Запускаю ANALYZE для обновления статистики",
        "Step 4: Add indexes": "Создаю индексы на WHERE/JOIN ключи",
        "Step 5: Rewrite query": "Переписываю:  избегаю функций в WHERE, добавляю LIMIT, использую диапазоны",
        "Step 6: Verify plan": "Проверяю EXPLAIN — есть ли улучшения",
        "Step 7: Load test": "Проверяю на производстве с реальным трафиком",
        "Step 8: Monitor": "Добавляю мониторинг для отслеживания регрессий"
    }
    return steps

8. Инструменты для анализа

-- Самые медленные запросы на сервере
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Самые часто используемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

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

9. Реальный пример оптимизации

-- Исходный запрос (10,000ms)
SELECT 
    u.id, u.email, u.name,
    COUNT(DISTINCT o.id) as orders,
    SUM(o.amount) as revenue,
    MAX(o.created_at) as last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE DATE(u.created_at) >= '2024-01-01'
  AND u.status = 'active'
GROUP BY u.id, u.email, u.name;

-- EXPLAIN показывает:
-- Seq Scan users -> Seq Scan orders -> Seq Scan order_items
-- Filter: (DATE(u.created_at) >= '2024-01-01') AND (u.status = 'active')

-- Оптимизация:
-- 1. Удаляем DATE функцию
-- 2. Добавляем индексы
-- 3. Используем LIMIT если возможно

CREATE INDEX idx_users_status_created 
ON users(status, created_at DESC);
CREATE INDEX idx_orders_user_id 
ON orders(user_id);
CREATE INDEX idx_order_items_order_id 
ON order_items(order_id);

SELECT 
    u.id, u.email, u.name,
    COUNT(DISTINCT o.id) as orders,
    COALESCE(SUM(o.amount), 0) as revenue,
    MAX(o.created_at) as last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.created_at >= '2024-01-01'
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE u.created_at >= '2024-01-01'::TIMESTAMPTZ 
  AND u.status = 'active'
GROUP BY u.id, u.email, u.name
LIMIT 10000;

-- Результат: 250ms (40x ускорение!)
-- Index Scan используется, никакие Seq Scans

10. Чеклист оптимизации

  • ✅ Запустил EXPLAIN (ANALYZE, BUFFERS) для понимания плана
  • ✅ Обновил статистику (ANALYZE TABLE)
  • ✅ Добавил индексы на столбцы в WHERE и JOIN
  • ✅ Убрал функции из WHERE условий (используй диапазоны)
  • ✅ Добавил LIMIT где применимо
  • ✅ Проверил, что индексы действительно используются (не Seq Scan)
  • ✅ Разбил сложный запрос на несколько простых если нужно
  • ✅ Рассмотрел материализованные представления для часто используемых
  • ✅ Проверил на реальных данных в production
  • ✅ Настроил мониторинг для отслеживания регрессий

Заключение

Оптимизация медленных запросов — это процесс: диагностика через EXPLAIN, идентификация узких мест, добавление индексов, переписание запроса и верификация результата. Обычно комбинация индексов и переписания запроса дает 5-100x ускорение. Главное правило: всегда проверяй план запроса через EXPLAIN и убедись, что индексы используются.