15 апреля 2026 г.

Оптимизация SQL-запросов: шпаргалка

Зачем аналитику оптимизация

На позиции middle+ задача «этот запрос работает 10 минут, ускорь» — типична. Оптимизация — это не магия, а 5-6 повторяющихся приёмов.

Шаг 1. EXPLAIN — диагностика

Любая оптимизация начинается с EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT ... ;

Смотрим план:

  • Seq Scan на большой таблице → нужен индекс.
  • Nested Loop на 1M × 1M строк → скорее всего нужен Hash Join.
  • Sort Method: external merge Disk → сортировка ушла на диск, нужен индекс или больше памяти.

Подробнее про EXPLAIN.

Шаг 2. Индексы

Когда нужен

  • WHERE на большом поле с высокой кардинальностью (user_id, email, order_id).
  • JOIN по полю без PK.
  • ORDER BY, который не вписывается в память.
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Подробнее про индексы.

Композитные индексы

Порядок столбцов важен. Индекс (user_id, created_at):

  • ✅ Работает: WHERE user_id = 1 AND created_at > '2026-01-01'
  • ✅ Работает: WHERE user_id = 1
  • ❌ НЕ работает: WHERE created_at > '2026-01-01' (нет user_id)

Шаг 3. Фильтруйте рано

WHERE до JOIN

-- ❌ Медленно — JOIN всех, потом фильтр
SELECT * FROM orders o
JOIN users u USING (user_id)
WHERE u.country = 'RU';

-- ✅ Быстрее — фильтр до JOIN
SELECT * FROM orders o
JOIN (SELECT * FROM users WHERE country = 'RU') u USING (user_id);

PostgreSQL обычно умеет это сам, но в сложных запросах иногда нет.

WHERE + HAVING

-- ❌ HAVING фильтрует после GROUP BY
SELECT city, COUNT(*) FROM users
GROUP BY city
HAVING city = 'Moscow';

-- ✅ WHERE фильтрует до
SELECT city, COUNT(*) FROM users
WHERE city = 'Moscow'
GROUP BY city;

Шаг 4. Избегайте функций на индексированных столбцах

-- ❌ Индекс на email не используется
WHERE LOWER(email) = 'x@y.com'

-- ✅ Индекс работает
WHERE email = 'x@y.com'

-- Или создать функциональный индекс:
CREATE INDEX idx_email_lower ON users (LOWER(email));

Прокачать тему на реальных задачах удобно в боте @kariernik_bot — база вопросов собрана с собеседований в Яндексе, Авито, Ozon, Тинькофф.

Шаг 5. Замените коррелированные подзапросы на JOIN / оконные

-- ❌ Медленно — N подзапросов для N строк
SELECT o.*,
    (SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o.user_id) AS user_count
FROM orders o;

-- ✅ Быстрее — один проход
SELECT o.*,
    COUNT(*) OVER (PARTITION BY user_id) AS user_count
FROM orders o;

Шаг 6. LIMIT + ORDER BY с индексом

-- Если есть индекс на created_at — моментальный top-10
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Без индекса — sort всей таблицы → медленно.

Шаг 7. EXISTS vs IN (с NULL)

-- NOT IN + NULL → 0 строк (багi)
WHERE id NOT IN (SELECT x FROM t) -- если x = NULL — ничего не вернётся

-- ✅ NOT EXISTS — безопасно и обычно быстрее
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.x = id)

Шаг 8. Партиционирование таблицы

Для очень больших таблиц (10M+ строк) — делить на физические партиции по дате:

CREATE TABLE orders (
    id SERIAL, created_at TIMESTAMP, amount NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_q1 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

Запросы с WHERE created_at >= '2026-01-01' читают только нужные партиции.

Шаг 9. Materialized view для тяжёлых агрегатов

Если агрегат считается каждые 5 минут, но данные меняются раз в день — materialized view + daily refresh.

CREATE MATERIALIZED VIEW daily_stats AS
SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount) AS rev
FROM orders GROUP BY 1;

-- Обновлять через cron
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_stats;

Подробнее.

Шаг 10. Уменьшите объём данных

-- ❌ Читает всё
SELECT * FROM big_table;

-- ✅ Только нужные столбцы
SELECT id, name FROM big_table;

-- ✅ Нужный период
SELECT * FROM big_table WHERE created_at >= '2026-01-01';

-- ✅ С LIMIT для предпросмотра
SELECT * FROM big_table LIMIT 100;

На собесе такие штуки часто спрашивают. Быстрый способ довести до автоматизма — тренажёр в Telegram с задачами из реальных интервью.

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

1. SELECT * на большой таблице

Читает все столбцы, даже если нужны 2. Особенно плохо на wide tables с TEXT/JSON полями.

2. OR на разных столбцах

-- Плохо — может не использовать индексы
WHERE a = 1 OR b = 2

-- Лучше — UNION ALL двух запросов
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a != 1;

3. DISTINCT для удаления дубликатов после JOIN

Дубликаты после JOIN — часто признак m:n связи без агрегации. Вместо DISTINCT — используйте DISTINCT ON или сначала агрегируйте.

4. Сортировка в подзапросе

-- ❌ Бессмысленно — результат снова сортируется
SELECT * FROM (SELECT * FROM t ORDER BY x) t2 ORDER BY y;

5. Использование скалярной функции в WHERE в циклу

Каждая итерация — вызов функции. Лучше материализовать результат.

Как читать EXPLAIN (быстро)

Смотреть сверху вниз, плохие знаки:

  • Seq Scan on — полный скан.
  • Nested Loop (actual rows=1000000) — N×M где N велико.
  • Sort Method: external merge — сортировка на диске.
  • rows=100 … actual rows=1000000 — плохие статистики.

Хорошие знаки:

  • Index Scan using idx_...
  • Index Only Scan
  • Hash Join для больших таблиц.
  • estimated rows ≈ actual rows.

Шпаргалка EXPLAIN.

10 задач на оптимизацию

1. Запрос работает 30 сек, возвращает 10 строк. Что делать?

EXPLAIN ANALYZE → искать Seq Scan / Nested Loop на большом. Добавить индекс.

2. SELECT * FROM big_table WHERE LOWER(email) = 'x' медленный

Использовать WHERE email = 'x' (если регистр фиксирован) или создать функциональный индекс.

3. COUNT(*) на 100M строк

Приближённый из pg_stats (SELECT reltuples FROM pg_class). Точный невозможно быстро.

4. JOIN 5 таблиц работает 5 минут

Проверить порядок JOIN через SET join_collapse_limit = 1. Разбить на CTE с intermediate tables.

5. GROUP BY по 10M строк

Предварительная материализация в таблицу или использование columnstore (ClickHouse).

6. Подзапрос в SELECT тормозит

Заменить на оконную функцию (SUM OVER, COUNT OVER).

7. ORDER BY + LIMIT на большой таблице

Создать индекс на ORDER BY столбцы.

8. DISTINCT на 100M строк

Редко нужен точный — попробуйте GROUP BY + индекс. Или approximate count в BigQuery/ClickHouse.

9. Запрос возвращает дубликаты

Проверить кардинальность JOIN. Обычно забыли, что одна таблица имеет n записей на ключ.

10. CTE тормозит в PostgreSQL 11

В PG ≤12 CTE материализуется всегда. Используйте WITH … AS NOT MATERIALIZED или инлайн подзапрос.

Как тренироваться

Оптимизация SQL — практика. Запустите EXPLAIN на каждом запросе, попробуйте 2-3 варианта, сравнивайте времена.

Совет: на собесе по оптимизации первое, что нужно сказать — «посмотрю EXPLAIN». Без плана запроса любая «оптимизация» — гадание.

Читайте также

FAQ

Всегда ли индекс ускоряет?

Нет. На маленьких таблицах индекс не нужен — seq scan быстрее. На записях (INSERT/UPDATE) индексы замедляют — нужно обновлять индекс.

Что быстрее — JOIN или EXISTS?

Зависит от оптимизатора и данных. В современных СУБД обычно одинаково. EXISTS безопаснее при NULL. JOIN удобнее для получения колонок из правой таблицы.

PostgreSQL или ClickHouse для аналитики?

Postgres для OLTP + лёгкой аналитики. ClickHouse для тяжёлой аналитики (агрегаты на миллиардах). Подробнее.

Кэширование — это оптимизация?

Да, но на уровне приложения, не SQL. СУБД сама кэширует пахоты в памяти. Redis/Memcached — если запрос вызывается 100+ раз в секунду с одинаковыми параметрами.

Тренироваться в Telegram