Оптимизация запросов¶
Зачем это DE?¶
Запрос, который работает 30 секунд на 1 млн строк, будет работать 5 минут на 10 млн. Data Engineer должен уметь читать EXPLAIN ANALYZE, понимать алгоритмы JOIN и писать sargable запросы. Это разница между пайплайном, который укладывается в SLA, и тем, который его ломает.
EXPLAIN ANALYZE — рентген запроса¶
Базовое использование¶
SQL
EXPLAIN ANALYZE
SELECT c.name, SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c USING (customer_id)
WHERE o.order_date >= '2025-01-01'
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 10;
Чтение результата¶
Text Only
Limit (cost=1234.56..1234.58 rows=10 width=48)
(actual time=12.345..12.350 rows=10 loops=1)
-> Sort (cost=1234.56..1240.23 rows=2500 width=48)
(actual time=12.340..12.345 rows=10 loops=1)
Sort Key: (sum(o.total_amount)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=1200.00..1225.00 rows=2500 width=48)
(actual time=11.500..12.100 rows=2500 loops=1)
-> Hash Join (cost=100.00..1100.00 rows=20000 width=20)
(actual time=1.200..8.500 rows=20000 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (cost=0.00..800.00 rows=20000 width=12)
(actual time=0.010..3.500 rows=20000 loops=1)
Filter: (order_date >= '2025-01-01')
Rows Removed by Filter: 30000
-> Hash (cost=50.00..50.00 rows=2500 width=12)
(actual time=1.100..1.100 rows=2500 loops=1)
-> Seq Scan on customers c ...
Planning Time: 0.150 ms
Execution Time: 12.500 ms
Ключевые поля:
| Поле | Значение |
|---|---|
cost=start..total |
Оценка стоимости (в условных единицах) |
rows |
Оценочное / фактическое число строк |
actual time |
Реальное время в мс (первая строка..все строки) |
loops |
Сколько раз узел выполнялся |
Rows Removed by Filter |
Строки, отфильтрованные после чтения |
Большое Rows Removed by Filter = красный флаг
Если Seq Scan прочитал 50K строк, а фильтр оставил 1K — нужен индекс.
Полезные опции EXPLAIN¶
SQL
-- Подробный вывод с буферами
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;
-- shared hit = из кеша (быстро)
-- shared read = с диска (медленно)
-- temp read/written = не хватило work_mem, ушли на диск
Алгоритмы JOIN¶
PostgreSQL выбирает алгоритм автоматически на основе статистики:
Nested Loop¶
- Когда быстро: одна таблица маленькая + индекс на второй
- Когда медленно: обе таблицы большие → O(n × m)
- Подсказка: эффективен при
LIMIT(остановится рано)
Hash Join¶
- Когда быстро: средние таблицы, equi-join (
=) - Когда медленно: hash-таблица не помещается в
work_mem→ уходит на диск - Не работает: с
!=,>,<(только=)
Merge Join¶
Text Only
1. Отсортировать обе таблицы по ключу JOIN
2. Пройти параллельно (как слияние в merge sort)
- Когда быстро: обе таблицы уже отсортированы (индекс!) + большие объёмы
- Требует: сортировку обеих сторон (если нет индекса — дополнительный Sort)
Какой JOIN лучше?¶
| Ситуация | Лучший алгоритм |
|---|---|
| Маленькая + большая с индексом | Nested Loop |
| Средние таблицы, equi-join | Hash Join |
| Обе большие, есть индексы | Merge Join |
JOIN с > / < / BETWEEN |
Nested Loop или Merge Join |
Sargable запросы¶
Sargable (Search ARGument ABLE) — запрос, который может использовать индекс. Не sargable — не может.
Что ломает индексы¶
SQL
-- ❌ Функция на индексированном столбце
WHERE EXTRACT(YEAR FROM order_date) = 2025
-- ✅ Диапазон
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
-- ❌ Вычисление на столбце
WHERE price * quantity > 1000
-- ✅ Перенести вычисление на правую сторону
WHERE price > 1000 / quantity -- или добавить expression index
-- ❌ LIKE с начальным %
WHERE name LIKE '%ванов'
-- ✅ LIKE с начала строки
WHERE name LIKE 'Иванов%' -- использует B-tree
-- ❌ Приведение типов
WHERE customer_id::text = '42'
-- ✅ Правильный тип
WHERE customer_id = 42
-- ❌ OR на разных столбцах
WHERE city = 'Москва' OR status = 'delivered'
-- ✅ UNION ALL
SELECT ... WHERE city = 'Москва'
UNION ALL
SELECT ... WHERE status = 'delivered' AND city != 'Москва'
Типичные проблемы и решения¶
1. Seq Scan вместо Index Scan¶
SQL
-- Проверить: есть ли индекс?
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- Создать если нет
CREATE INDEX idx_orders_date ON orders(order_date);
-- Partial index — если фильтруем часто по условию
CREATE INDEX idx_orders_active ON orders(order_date)
WHERE status != 'cancelled';
Seq Scan не всегда плохо
Если запрос читает > 10-20% таблицы, Seq Scan быстрее Index Scan (последовательное чтение vs random I/O).
2. Hash Join уходит на диск¶
Text Only
-> Hash Join (actual time=5000ms ...)
-> Hash (actual time=3000ms ...)
Batches: 16 -- хеш не влез в память!
Решение: увеличить work_mem для сессии:
3. Sort на диске¶
work_mem мал → сортировка на диске. Решения:
- Увеличить work_mem
- Добавить индекс, совпадающий с ORDER BY
- Уменьшить объём данных (фильтровать раньше)
4. Bitmap Heap Scan — lossy¶
Text Only
-> Bitmap Heap Scan on orders
Recheck Cond: ...
Rows Removed by Index Recheck: 5000 -- неточный bitmap
work_mem мал → bitmap стал «lossy» (блочный вместо точного). Увеличить work_mem.
pg_stat_statements — поиск медленных запросов¶
SQL
-- Включить расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Топ-10 самых долгих запросов
SELECT
calls,
ROUND(total_exec_time::numeric / 1000, 2) AS total_sec,
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
ROUND(rows::numeric / calls) AS avg_rows,
LEFT(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
На что смотреть:
| Метрика | Опасно когда |
|---|---|
total_exec_time высокое |
Запрос вызывается часто и медленный |
calls огромное |
N+1 запрос (цикл вместо JOIN) |
avg_rows >> нужного |
Читаем лишнее, нет фильтра |
shared_blks_read >> shared_blks_hit |
Кеш промахивается, всё с диска |
Оптимизация типичных DE-запросов¶
INSERT ... SELECT — ускорение¶
SQL
-- Перед массовой вставкой:
ALTER TABLE target SET (autovacuum_enabled = false);
DROP INDEX idx_target_date; -- пересоздадим после
INSERT INTO target
SELECT ... FROM source WHERE ...;
-- После вставки:
CREATE INDEX idx_target_date ON target(order_date);
ALTER TABLE target SET (autovacuum_enabled = true);
ANALYZE target;
Partition Pruning¶
SQL
-- Если orders партиционирована по order_date:
EXPLAIN
SELECT * FROM orders WHERE order_date = '2025-03-15';
-- Должно показать: Seq Scan on orders_2025_03
-- Если нет — проверь тип: date vs timestamp, enable_partition_pruning
Materialized View для тяжёлых агрегатов¶
SQL
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
order_date,
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY order_date;
-- Обновлять по расписанию (из Airflow):
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;
-- CONCURRENTLY — не блокирует чтение, но нужен UNIQUE INDEX
Что запомнить¶
- EXPLAIN ANALYZE — всегда начинай оптимизацию с чтения плана
- Seq Scan на большой таблице — первый кандидат на индекс
- Rows Removed by Filter — показывает, сколько строк прочитано зря
- Sargable — не оборачивай индексированный столбец в функцию
- Nested Loop хорош для маленьких таблиц, Hash Join — для больших
- pg_stat_statements — must-have расширение для поиска тяжёлых запросов
- ANALYZE — обновляй статистику после массовых операций