Как планировщик запросов выбирает оптимальный план

Что проверяют

Понимаете ли вы, что планировщик не видит данные, а оценивает их по статистике, и что медленный запрос чаще всего следствие неверной оценки числа строк. Следом просят прочитать EXPLAIN ANALYZE или объяснить, почему запрос быстрый в консоли и медленный из приложения.

Как отвечать

На примере PostgreSQL. Оптимизатор стоимостной:

• статистика: ANALYZE (его запускает autovacuum) собирает по колонкам долю NULL, число различных значений, частые значения и гистограмму, всё видно в pg_stats. По ним оценивается, сколько строк вернёт условие;
• перебор вариантов: способы доступа (Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan), алгоритмы соединения (nested loop, hash join, merge join) и порядок таблиц. Порядок перебирается динамическим программированием, а начиная с geqo_threshold таблиц (по умолчанию 12) включается генетический алгоритм;
• стоимость: условные единицы, а не миллисекунды. Складывается из seq_page_cost (1), random_page_cost (4 по умолчанию, на SSD его снижают почти до 1), cpu_tuple_cost и других. Побеждает план с минимальной суммой.

Главный источник плохих планов это ошибка кардинальности. Если колонки коррелируют, например город и регион, планировщик перемножает селективности и недооценивает число строк. Помогает CREATE STATISTICS. Проверка: в EXPLAIN (ANALYZE, BUFFERS) сравнить оценку rows с фактической.

Для Java важен кэш планов. Глобального кэша, как в Oracle, у PostgreSQL нет, планы живут в подготовленных запросах сессии. PgJDBC после пяти выполнений одного PreparedStatement (prepareThreshold) переходит на серверную подготовку, а сервер позже может выбрать общий план, не зависящий от значений параметров. На неравномерных данных такой план бывает заметно хуже.

Где спотыкаются

Говорят, что при наличии индекса база всегда его использует. Если условие выбирает большую долю таблицы, последовательное чтение дешевле.

Не вспоминают про устаревшую статистику после массовой загрузки, когда ANALYZE ещё не прошёл.

Ищут в PostgreSQL хинты как в Oracle. Встроенных нет, есть расширение pg_hint_plan, а правильный путь это статистика и переписанный запрос.