Продуктовый взгляд | Аналитика данных: “Оптимизации SQL Оптимизация SQL в основном это уменьшение числа читаем…”

Оптимизации SQL

Оптимизация SQL в основном это уменьшение числа читаемых строк и корректная работа планировщика с индексами, а не сложный синтаксис запросов. Типичный кейс: большая таблица orders с полями user_id, created_at, status, amount и ежедневный отчёт по выручке за последние 30 дней. Наивный запрос:


SELECT
    user_id,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= now() - interval "30 days"
  AND status IN ("paid", "completed")
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 100;

По мере роста данных планировщик часто делает Seq Scan, потому что фильтр мало селективен и разрозненные индексы по created_at и status не помогают. Практичный подход — спроектировать один составной и покрывающий индекс под шаблон запроса:


CREATE INDEX CONCURRENTLY idx_orders_status_created_user
ON orders (status, created_at, user_id)
INCLUDE (amount);

Фильтры и JOIN должны быть sargable: столбцы используются напрямую, без обёртки в функции. Вариант WHERE date_trunc("day", created_at) >= current_date - interval "30 days" часто ломает использование индекса по created_at, и в таком случае либо переписывают условие на диапазон по created_at, либо заводят функциональный индекс:


CREATE INDEX CONCURRENTLY idx_orders_created_day
ON orders (date_trunc("day", created_at));

Во многих системах часть нагрузки создаёт ORM через шаблон N+1, когда вместо одного агрегирующего запроса выполняются десятки маленьких. Чаще всего их можно заменить одним запросом с JOIN и агрегацией:


SELECT
    u.id,
    u.name,
    SUM(o.amount) AS revenue_30d
FROM users u
JOIN orders o
  ON o.user_id = u.id
WHERE o.created_at >= now() - interval "30 days"
  AND o.status IN ("paid", "completed")
GROUP BY u.id, u.name
ORDER BY revenue_30d DESC
LIMIT 100;

Под рабочие запросы разумно делать частичные индексы только по нужным данным, например:


CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at, user_id)
WHERE status IN ("paid", "completed");

Ключевая идея при любой оптимизации - смотреть планы через EXPLAIN (ANALYZE, BUFFERS) и сравнивать их до и после изменений: тип скана, количество строк, время выполнения. Получается, что при написании эффективного кода мы выделяем важные запросы, делаем под них индексы с продуманным порядком колонок, формулируем условия в sargable виде и избегаем N+1 и тяжёлых агрегаций по сырым данным.

А также напоминаем, что уже скоро стартует наш курс по SQL, где вас научат писать сложные, эффективные запросы с нуля, а также покажут основы устройства и проектирования БД😎

@ProdAnalysis