Оптимизация SQL-запросов веб-приложения

Оптимизация SQL-запросов веб-приложения

Медленные запросы убивают UX быстрее любого другого фактора. 95% проблем производительности на уровне БД решаются одним из четырёх методов: добавлением индекса, переписыванием запроса, денормализацией или добавлением кеша. Разберём диагностику и конкретные техники.

Инструменты диагностики

pg_stat_statements — первое, что нужно включить на продакшне:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Топ-20 запросов по суммарному времени
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    calls,
    round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

coeff_var_pct — коэффициент вариации: высокий процент говорит о нестабильном плане (разные параметры дают кардинально разное время).

EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'published'
  AND p.created_at > NOW() - INTERVAL '30 days'
ORDER BY p.created_at DESC
LIMIT 50;

Ключевые узлы в плане:

• Seq Scan на большой таблице — нет индекса или планировщик решил, что индекс не выгоден.
• Nested Loop с большим количеством итераций — N+1 на уровне SQL.
• Hash Join с Batches > 1 — не хватает work_mem.
• Sort без Index Scan на ORDER BY колонке — нет подходящего индекса.

Типичные антипаттерны и решения

1. SELECT * в ORM

-- Плохо: тянем все 30 колонок, включая BLOB-поля
SELECT * FROM products WHERE category_id = 5;

-- Хорошо: только нужные поля
SELECT id, title, slug, price, status FROM products WHERE category_id = 5;

2. OFFSET pagination на больших таблицах

-- Плохо: при offset=10000 PostgreSQL читает 10050 строк и отбрасывает 10000
SELECT * FROM products ORDER BY created_at DESC LIMIT 50 OFFSET 10000;

-- Хорошо: keyset pagination
SELECT * FROM products
WHERE (created_at, id) < ('2024-03-01 12:00:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Keyset pagination требует составного индекса (created_at DESC, id DESC) и передачи последнего значения предыдущей страницы.

3. OR на разных колонках

-- Плохо: OR часто не использует индекс
SELECT * FROM users WHERE email = $1 OR phone = $1;

-- Хорошо: UNION
SELECT * FROM users WHERE email = $1
UNION ALL
SELECT * FROM users WHERE phone = $1
LIMIT 1;

4. Функции в WHERE

-- Плохо: функция на колонке блокирует индекс
SELECT * FROM orders WHERE DATE(created_at) = '2024-03-15';

-- Хорошо: range condition
SELECT * FROM orders
WHERE created_at >= '2024-03-15'
  AND created_at < '2024-03-16';

5. NOT IN с подзапросом

-- Плохо: если подзапрос вернёт NULL — весь NOT IN вернёт пустой результат
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);

-- Хорошо: NOT EXISTS или LEFT JOIN
SELECT p.* FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

Оптимизация JOIN

-- Добавляем составной индекс для типичного фильтра
CREATE INDEX idx_orders_user_status_created
    ON orders (user_id, status, created_at DESC);

-- Запрос использует index scan без Sort
SELECT id, total, status, created_at
FROM orders
WHERE user_id = $1
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

Порядок колонок в индексе: сначала equality conditions (user_id = $1, status = 'completed'), затем range/sort (created_at DESC).

work_mem и sort spill

Если в EXPLAIN ANALYZE видим external merge (Disk: ...) при Sort — увеличиваем work_mem для сессии:

SET work_mem = '64MB';
-- Выполняем тяжёлый аналитический запрос
-- Возвращаем обратно или используем connection-level настройку

В postgresql.conf лучше оставить work_mem низким (4-8MB по умолчанию) и поднимать для конкретных запросов через SET LOCAL work_mem.

CTE vs subquery vs lateral

-- CTE (WITH) в PostgreSQL 12+ оптимизируется как subquery по умолчанию
-- До PG12: CTE всегда materialize — optimization fence
WITH recent_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.*, ro.cnt
FROM users u
JOIN recent_orders ro ON ro.user_id = u.id;

-- LATERAL: для row-dependent subqueries
SELECT u.id, u.email, recent.total
FROM users u
CROSS JOIN LATERAL (
    SELECT SUM(total) AS total
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at > NOW() - INTERVAL '30 days'
) AS recent;

LATERAL позволяет использовать переменные из внешнего запроса в subquery — это часто даёт лучший план, чем JOIN на агрегированный CTE.

Статистика планировщика

Если план выбирает неверный метод доступа — обновляем статистику:

ANALYZE products;
-- или для обновления статистики по конкретной колонке с повышенной точностью:
ALTER TABLE products ALTER COLUMN status SET STATISTICS 500;
ANALYZE products (status);

По умолчанию default_statistics_target = 100. Для колонок с высокой кардинальностью (timestamps, UUID) стоит поднять до 200–500.

Сроки

Диагностика и оптимизация 10–15 медленных запросов (через pg_stat_statements, EXPLAIN ANALYZE, добавление индексов, переписывание): 2–3 дня. Глубокий аудит схемы и запросов для высоконагруженного приложения: 3–5 дней.