Как планировщик определяет, нужно ли использовать индекс?

Как планировщик запросов (Query Planner) PostgreSQL определяет необходимость использования индекса

Планировщик в PostgreSQL — это сложная система принятия решений, которая анализирует запросы, статистику таблиц и доступные индексы, чтобы выбрать наиболее эффективный план выполнения. Решение об использовании индекса принимается на основе нескольких ключевых факторов.

Основные критерии принятия решения

1. Селективность данных (Selectivity)

Планировщик оценивает, какая доля строк таблицы будет возвращена запросом. Если выбирается небольшая часть данных (обычно менее 5-10%), индекс становится предпочтительным.

-- Пример: индекс будет использован при высокой селективности
SELECT * FROM users WHERE email = 'alex@example.com';
-- (один email среди миллионов записей)

-- Пример: индекс, скорее всего, НЕ будет использован при низкой селективности  
SELECT * FROM orders WHERE status = 'completed';
-- (большинство заказов имеют статус 'completed')

2. Статистика таблиц (Table Statistics)

PostgreSQL собирает статистику через ANALYZE, включая:

• n_distinct: количество уникальных значений в столбце
• most_common_vals: наиболее частые значения
• histogram_bounds: распределение данных
• correlation: корреляция между физическим порядком строк и логическим порядком значений

-- Просмотр статистики для столбца
SELECT attname, n_distinct, most_common_vals 
FROM pg_stats 
WHERE tablename = 'users' AND attname = 'email';

3. Стоимостная модель (Cost-Based Optimization)

Планировщик сравнивает стоимость разных планов выполнения, используя внутренние метрики:

• seq_page_cost: стоимость чтения страницы последовательным сканированием
• random_page_cost: стоимость чтения страницы случайным доступом
• cpu_tuple_cost: стоимость обработки одной строки
• cpu_index_tuple_cost: стоимость обработки индекса

-- Текущие стоимостные параметры
SHOW seq_page_cost;
SHOW random_page_cost;

4. Характеристики индекса

• Тип индекса: B-tree, Hash, GIN, GiST, BRIN, SP-GiST
• Составной индекс: покрывает ли запрос все условия
• Индекс только для чтения (INCLUDE): включает ли дополнительные столбцы
• Частичный индекс: применяется ли к подмножеству данных

-- Пример составного индекса
CREATE INDEX idx_users_name_email ON users(last_name, email);

-- Запрос может использовать этот индекс для:
SELECT * FROM users WHERE last_name = 'Ivanov' AND email LIKE 'a%';

5. Размер данных и параметры сервера

• Размер таблицы: для маленьких таблиц последовательное сканирование часто быстрее
• Размещение данных в кэше: если данные уже в shared_buffers
• Настройки памяти: work_mem, maintenance_work_mem
• Параллельное выполнение: может ли запрос использовать параллельное сканирование

Процесс принятия решения по шагам

1. Парсинг запроса → построение дерева разбора
2. Анализ статистики → оценка селективности предикатов
3. Генерация возможных путей доступа:
   • Последовательное сканирование (Seq Scan)
   • Сканирование по индексу (Index Scan, Index Only Scan)
   • Битовое сканирование индекса (Bitmap Index Scan)
4. Оценка стоимости каждого пути в условных единицах
5. Выбор пути с минимальной стоимостью

Практические примеры принятия решений

Пример 1: Когда индекс НЕ используется

-- Маленькая таблица
SELECT * FROM config WHERE id = 5;
-- Если таблица помещается в одну страницу (8KB), планировщик выберет Seq Scan

Пример 2: Когда индекс используется эффективно

-- Покрывающий индекс (Index Only Scan)
CREATE INDEX idx_orders_date_status ON orders(order_date, status) INCLUDE (amount);
SELECT order_date, status, amount FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- Все данные есть в индексе → Index Only Scan

Методы анализа решений планировщика

1. EXPLAIN — показывает выбранный план выполнения
2. EXPLAIN ANALYZE — показывает фактическое выполнение
3. EXPLAIN (BUFFERS, VERBOSE) — детальная информация

-- Анализ плана выполнения
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM products WHERE category_id = 42 AND price > 1000;

-- Принудительное отключение индексов для тестирования
SET enable_indexscan = off;
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
SET enable_indexscan = on;

Важные исключения и особенности

1. Выражения и функции — обычный индекс не работает с выражениями:

-- Не будет использовать индекс по колонке name
SELECT * FROM users WHERE UPPER(name) = 'ALEX';

-- Требуется функциональный индекс
CREATE INDEX idx_users_upper_name ON users(UPPER(name));

1. Неселективные индексы могут использоваться, если:

   • Запрос покрывается индексом (Index Only Scan)
   • Данные сильно коррелированы с физическим порядком
   • Используется параллельное сканирование

2. Критические настройки:

-- Повышение стоимости последовательного сканирования
SET random_page_cost = 1.0;  -- Для SSD
SET effective_cache_size = '8GB';  -- Учет кэша ОС

Заключение

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