Как используется планировщик запросов (query planner) для диагностики производительности?

Question

Планировщик запросов — это компонент СУБД, который определяет наиболее эффективный способ выполнения SQL-запроса. Для диагностики производительности анализируют план выполнения, который показывает какие индексы используются, какие методы соединения применяются и сколько данных обрабатывается. С помощью EXPLAIN и EXPLAIN ANALYZE можно увидеть этот план и найти узкие места — отсутствие индексов, неправильные оценки количества строк, дорогие операции сортировки или соединения.

YeaHub · Accepted Answer

Планировщик запросов преобразует SQL-запрос в последовательность операций для эффективного выполнения.1. Что анализирует планировщикДоступные индексы: Выбирает подходящие индексы для условий WHERE и JOINСтатистику таблиц: Использует информацию о распределении данныхСтоимость операций: Оценивает стоимость разных методов выполненияРазмер данных: Учитывает объем обрабатываемых данных2. Ключевые элементы плана запроса для диагностикиSeq Scan vs Index Scan: Полное сканирование таблицы или использование индексаNested Loop vs Hash Join vs Merge Join: Методы соединения таблицSort Operations: Сортировка данных для ORDER BY или JOINFilter: Применение условий WHEREBitmap Index Scan: Комбинированное использование индексов3. Практические примеры диагностики-- Проблема: Отсутствие индекса
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Решение: Seq Scan -> создаем индекс
CREATE INDEX idx_users_email ON users(email);

-- Проблема: Неправильная оценка количества строк
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2023-01-01';
-- Решение: Обновляем статистику
ANALYZE orders;

-- Проблема: Дорогая сортировка
EXPLAIN ANALYZE SELECT * FROM products ORDER BY price DESC;
-- Решение: Используем индекс для сортировки
CREATE INDEX idx_products_price ON products(price DESC);4. Анализ эффективности индексов-- Проверка использования индексов
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;  -- Неиспользуемые индексы

-- Статистика по таблицам
SELECT schemaname, tablename, seq_scan, seq_tup_read, 
       idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables;Вывод: Планировщик запросов — ключевой инструмент для диагностики производительности. Регулярно анализируйте планы выполнения медленных запросов, создавайте недостающие индексы, обновляйте статистику и удаляйте неиспользуемые индексы для поддержания оптимальной производительности.