Пример (PostgreSQL). Схема:
- customers(id PRIMARY KEY, name)
- orders(id PRIMARY KEY, customer_id, total, created_at)
Предположение: индекс на orders.customer_id отсутствует.
Запрос с неоптимальным планом:
SELECT c.id, c.name, o.id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01';
Пример плохого плана (EXPLAIN ANALYZE) — упрощённо:
Nested Loop (cost=$\mathrm{0.00..50000.00}$ rows=$1000000$ width=64)
-> Seq Scan on customers (cost=$\mathrm{0.00..1.00}$ rows=$100000$ width=32)
-> Seq Scan on orders (cost=$\mathrm{0.00..40000.00}$ rows=$1000000$ width=32)
Почему это плохо (кратко):
- План делает последовательные сканирования (Seq Scan) по большой таблице orders: высокая I/O и CPU.
- Nested Loop с большим числом строк даёт сложность примерно $O(n\times m)$, что быстро растёт при больших таблицах.
- Частая причина: нет индекса по условию соединения/фильтрации (orders.customer_id, orders.created_at).
Как анализировать план выполнения:
1. Выполнить EXPLAIN ANALYZE и смотреть реальные значения:
- сравнить estimated rows vs actual rows (если сильно отличаются — статистика неверная).
- искать дорогие узлы: Seq Scan, Nested Loop, Sort, Materialize с большими cost/time/buffers.
- смотреть время и счётчики (loops, rows, actual time).
2. Внимание на:
- большие значения rows и actual time в узлах;
- узлы с high I/O (buffers) или многократные loops;
- mismatch между estimate и actual → выполнить ANALYZE/ANALYZE VERBOSE.
3. Использовать инструменты: pg_stat_statements, EXPLAIN (BUFFERS), auto_explain для slow queries.
Какие изменения улучшат производительность (конкретные действия):
1. Создать индекс на колонку соединения/фильтрации:
CREATE INDEX idx_orders_customer_id_created_at ON orders(customer_id, created_at);
— после этого план может выбрать Index Scan по orders или Hash Join; уменьшится число прочитанных страниц.
2. Если join большой на обеих таблицах — использовать Hash Join (увеличить work_mem при необходимости), например:
SET work_mem = '128MB';
3. Обновить статистику:
ANALYZE customers;
ANALYZE orders;
4. Проверить порядок JOIN и WHERE — фильтры применять как можно раньше; в некоторых случаях переписать запрос (EXISTS вместо JOIN) снижает дублирование.
5. Для очень больших таблиц — партиционирование по created_at или материализованный агрегат.
6. Убедиться, что есть ограничения внешнего ключа и правильно заданы типы, чтобы планировщик мог выбрать оптимальный план.
Ожидаемый улучшённый план (после индекса), упрощённо:
Hash Join (cost=$\mathrm{0.00..1500.00}$ rows=$10000$ width=64)
-> Seq Scan on customers (cost=$\mathrm{0.00..1.00}$ rows=$100000$ width=32)
-> Index Scan using idx_orders_customer_id_created_at on orders (cost=$\mathrm{0.00..1400.00}$ rows=$10000$ width=32)
Краткое резюме:
- сначала получить EXPLAIN ANALYZE и смотреть реальные vs оценённые строки;
- для JOIN по внешнему ключу добавить индекс(ы) на колонку(ы) соединения и фильтра;
- при необходимости корректировать work_mem, статистику и рассматривать партиционирование/материализацию.