.NET Разработчик: “День 2618. #МоиИнструменты #PG Инструменты Оптимизации Запросов в Post…”

День 2618. #МоиИнструменты #PG
Инструменты Оптимизации Запросов в PostgreSQL. Часть 5

5. EXPLAIN ANALYZE (для всех SQL баз данных)
Что даёт: точное понимание, как БД выполняет запрос.
Тип: встроенная команда (для всех основных БД)
Зачем: показывает план выполнения запроса — как БД фактически обрабатывает SQL-запрос. Без этого оптимизация — это гадание.

Использование в разных базах данных:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT … FROM orders WHERE …;

-- MySQL
EXPLAIN ANALYZE
SELECT … FROM orders WHERE …;

-- SQL Server
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
SELECT … FROM orders WHERE …;

Вывод (пример Postgres):

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01';

Seq Scan on orders  (cost=0.00..185234.25 rows=1 width=120) 
                    (actual time=0.045..2341.234 rows=247 loops=1)
  Filter: ((customer_id = 12345) AND (order_date >= '2024-01-01'::date))
  Rows Removed by Filter: 9847234
  Buffers: shared hit=47234 read=138000
Planning Time: 0.234 ms
Execution Time: 2341.567 ms

Что это значит:
- "Seq Scan" – полное сканирование таблицы (ПЛОХО – не используется индекс)
- "Rows Removed by Filter: 9847234" - Просканировано 9.8млн строк, возвращено 247
- "Execution Time: 2341ms" - 2.3 секунды

Решение – добавить индекс на поля customer_id и order_date:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

После:

Index Scan using idx_orders_customer_date on orders  
  (cost=0.43..8.45 rows=1 width=120) 
  (actual time=0.023..0.087 rows=247 loops=1)
  Index Cond: ((customer_id = 12345) AND (order_date >= '2024-01-01'::date))
  Buffers: shared hit=5
Planning Time: 0.123 ms
Execution Time: 0.112 ms

Что это значит:
- "Index Scan" – Используется индекс (ХОРОШО)
- "Buffers: shared hit=5" – только 5 блоков прочитано (против 185234 в предыдущем случае)
- "Execution Time: 0.112ms" – в 20000 раз быстрее

Основные шаги оптимизации запросов
1. Выполнить EXPLAIN ANALYZE
2. Обнаружить узкое место (seq scan, дорогой join или сортировку и т.п.)
3. Исправить (добавить индекс, переписать запрос, изменить порядок объединения)
4. Ещё раз выполнить EXPLAIN ANALYZE для проверки

Когда использовать
- Анализ любого медленного запроса;
- Перед написанием сложных запросов (прогноз производительности);
- После изменений схемы (проверка влияния);
- При добавлении индексов (обоснование использования).

Когда отказаться
Особых причин не использовать нет.

Скрытая функция
Сравнение планов запросов
1. Сохранить в файл

psql -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT …" > plan_before.json

2. Сделать изменения
3. Сохранить новый план

psql -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT …" > plan_after.json

4. Использовать утилиту визуализации плана:
- https://explain.dalibo.com/
- https://explain.depesz.com/
5. Использовать утилиту визуального сравнения (например, https://winmerge.org/)

С осторожностью
EXPLAIN с параметром ANALYZE на самом деле выполняет запрос.

-- Удалит данные!!!
EXPLAIN ANALYZE DELETE FROM orders WHERE …;

-- Безопасное тестирование
BEGIN;
 EXPLAIN ANALYZE DELETE FROM orders WHERE …;
 ROLLBACK;
END;

-- Либо без ANALYZE (только оценка)
EXPLAIN DELETE FROM orders WHERE …;

Источник: https://medium.com/@reliabledataengineering/15-sql-optimization-tools-that-make-queries-10x-faster-8629ac451d97