Перейти к основному содержимому

Практика: Оптимизация запросов в PostgreSQL


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Индексы: от простого к сложному​

Задание 1: Создание и проверка индексов

⏱️ Примерное время: 15-30 минут

Создай индексы для ускорения типичных запросов и сравни планы.


Требования:

  • выполни EXPLAIN ANALYZE до создания индекса
  • создай подходящий индекс (простой / составной / частичный / INCLUDE)
  • снова выполни EXPLAIN ANALYZE и сравни время / cost / тип сканирования
  • напиши, какой индекс ты выбрал и почему именно такой порядок колонок

Пример ответа:

До: Seq Scan, 45 ms, cost=5200
После: Index Scan on idx_orders_customer_date, 0.8 ms, cost=8.4
Индекс: CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Задание 2 — Переписывание запросов​

Задание 2: Переписывание медленных запросов

⏱️ Примерное время: 25-45 минут

Возьми неоптимальный запрос и сделай его быстрее.


Требования:

  • запусти EXPLAIN ANALYZE на исходном варианте
  • перепиши запрос (индексируемое выражение, JOIN вместо подзапроса, EXISTS, FILTER, диапазон вместо функции и т.д.)
  • покажи новый план и прирост скорости
  • обоснуй, почему стало быстрее

Пример:

Исходный: Seq Scan + Filter UPPER(email), 120 ms
Переписано: Index Scan на LOWER(email), 1.2 ms
Прирост: ×100

Задание 3 — JOIN и подзапросы vs LATERAL / CTE​

Задание 3: Оптимизация JOIN и подзапросов

⏱️ Примерное время: 30-50 минут

Сравни разные способы соединения и агрегации.


Требования:

  • реализуй 3–4 разных подхода к одному запросу:
    • коррелированный подзапрос
    • JOIN + GROUP BY
    • LATERAL
    • CTE / WINDOW
  • сравни планы и время выполнения (EXPLAIN ANALYZE)
  • выбери лучший вариант и обоснуй

Пример:

-- Вариант 1: коррелированный
SELECT c.*, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS cnt
FROM customers c;

-- Вариант 2: JOIN
SELECT c.*, COUNT(o.id) AS cnt
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

-- Вариант 3: LATERAL
SELECT c.*, o.cnt
FROM customers c
CROSS JOIN LATERAL (SELECT COUNT(*) AS cnt FROM orders WHERE customer_id = c.id) o;

Задание 4 — Агрегаты, FILTER, оконные функции​

Задание 4: Оптимизация агрегации и аналитики

⏱️ Примерное время: 30-50 минут

Ускорь сложные отчёты с помощью современных конструкций.


Требования:

  • используй минимум 2–3 техники:
    • FILTER внутри агрегатов
    • оконные функции (RANK, SUM OVER, AVG OVER)
    • GROUP BY + ORDER BY
    • CTE для промежуточных вычислений
  • сравни с «классическим» вариантом (множественные подзапросы)
  • покажи разницу во времени и плане

Пример:

-- Классика (медленно)
SELECT category, AVG(price) FROM products GROUP BY category;

-- Современно (быстрее + информативно)
SELECT
    category,
    COUNT(*) AS total,
    AVG(price) FILTER (WHERE stock_quantity > 0) AS avg_in_stock,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median_price
FROM products
GROUP BY category;

Задание 5 — Итоговый аудит и оптимизация магазина​

Задание 5: Полный аудит и оптимизация интернет-магазина (итоговое)

⏱️ Примерное время: 90-180 минут

Оптимизируй набор реальных запросов интернет-магазина.


Требования:

  • выбери 6–8 типичных запросов из сценария
  • для каждого:
    • исходный EXPLAIN ANALYZE
    • выявленное узкое место
    • предложенная оптимизация (индекс / переписывание / матвью / денормализация)
    • новый план + прирост скорости
  • сделай 2–3 глобальные рекомендации по схеме (новые индексы, материализованные представления, триггеры, статистика)
  • составь итоговый список из 8–12 рекомендаций

Это задание — полноценный кейс для собеседования на роль SQL-оптимизатора / DBA / backend-разработчика.

подсказка

Оптимизация — это всегда компромисс между скоростью чтения и скоростью записи, между простотой и производительностью.
Главное правило:

  1. Сначала измерь (EXPLAIN ANALYZE + BUFFERS)
  2. Потом оптимизируй (индексы → переписывание → кэширование)
  3. Не трогай то, что уже быстро