Вы пишете SQL, описывая, ЧТО хотите получить, а не КАК это посчитать. Превратить декларативный запрос в конкретный план выполнения - задача планировщика (optimizer). Он перебирает варианты, прикидывает их стоимость и выбирает самый дешевый. В этом уроке разберем, откуда планировщик берет числа, как он оценивает количество строк, какими способами читает таблицы (seq, index, bitmap scan) и соединяет их (nested loop, hash, merge join). Понимание этой кухни - база для чтения explain analyze и для разговоров вроде postgresql для 1с, где медленные отчеты часто упираются именно в неудачный план.
Как это работает
Планировщик в PostgreSQL стоимостный. Для каждого возможного способа выполнить запрос он считает абстрактную стоимость в условных единицах и берет план с минимальным числом. Единицы не секунды и не миллисекунды - это относительная мера работы, где за точку отсчета взято чтение одной страницы при последовательном проходе.
Стоимость складывается из чтения страниц с диска и обработки строк процессором. За это отвечают параметры стоимости: seq_page_cost (последовательная страница, по умолчанию 1.0), random_page_cost (случайная страница, 4.0), cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost. Соотношение random к seq как 4 к 1 - наследие эпохи HDD. На SSD и NVMe случайный доступ почти бесплатен, поэтому random_page_cost обычно снижают до 1.1-1.5, иначе планировщик недооценивает индексы.
Чтобы посчитать стоимость, нужно знать, сколько строк вернет каждый шаг. Эту оценку называют кардинальностью. Берется она из статистики, которую собирает команда ANALYZE и складывает в системный каталог pg_statistic (читаемое представление - pg_stats). Там хранятся доля NULL, число уникальных значений, список самых частых значений с их частотами (most common values) и гистограмма распределения остальных. По этим данным планировщик прикидывает селективность условия в WHERE: например, какая доля строк удовлетворяет status = 'Confirmed'.
Дальше планировщик выбирает метод доступа к таблице. Sequential scan читает всю таблицу подряд - дешево на страницу, но страниц много; выгоден, когда нужна большая часть строк. Index scan идет по индексу и для каждого совпадения прыгает в таблицу за строкой - быстро при высокой селективности, но каждый прыжок это случайное чтение. Bitmap scan - компромисс: сначала по индексу строится битовая карта нужных страниц, потом таблица читается по порядку, без хаотичных прыжков; хорош для среднего числа совпадений и для объединения нескольких индексов.
Для соединения таблиц есть три алгоритма. Nested loop перебирает строки внешней таблицы и для каждой ищет совпадения во внутренней - выгоден на маленьких объемах или когда по внутренней таблице есть индекс. Hash join строит в памяти хеш-таблицу по меньшей стороне, затем прогоняет через нее большую - лучший выбор для крупных соединений по равенству. Merge join сортирует оба входа по ключу и сливает их как застежку-молнию - выигрывает, когда данные уже отсортированы (например, пришли из index scan).
SQL и примеры
Посмотрим, какую статистику PostgreSQL собрал по колонке. Сначала обновим ее, потом заглянем в pg_stats.
Код: Выделить всё
ANALYZE bookings.ticket_flights;
SELECT null_frac, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'ticket_flights' AND attname = 'fare_conditions';
Теперь сравним планы. Запрос по узкому условию - выборка одного бронирования по первичному ключу:
Код: Выделить всё
EXPLAIN
SELECT * FROM bookings.bookings WHERE book_ref = '0824C5';
А вот условие, под которое подходит заметная доля таблицы:
Код: Выделить всё
EXPLAIN
SELECT * FROM bookings.flights WHERE status = 'Arrived';
Проверим оценку против реальности на соединении. EXPLAIN ANALYZE реально выполняет запрос и показывает план рядом с фактом:
Код: Выделить всё
EXPLAIN (ANALYZE, BUFFERS)
SELECT t.passenger_name, tf.amount
FROM bookings.tickets t
JOIN bookings.ticket_flights tf ON tf.ticket_no = t.ticket_no
WHERE tf.fare_conditions = 'Business';
Можно временно подтолкнуть планировщик к другому методу, чтобы сравнить стоимости (только для эксперимента, не в продакшене):
Код: Выделить всё
SET enable_seqscan = off;
EXPLAIN SELECT * FROM bookings.flights WHERE status = 'Arrived';
RESET enable_seqscan;
Частые грабли
- Свежезагруженная или массово измененная таблица без ANALYZE - статистики нет или она древняя, оценки кардинальности мимо, планы плохие. После большой заливки данных всегда запускайте ANALYZE.
- Дефолтный random_page_cost = 4.0 на SSD заставляет планировщик избегать индексов и лишний раз делать seq scan. На быстрых дисках снижайте до 1.1-1.5.
- Функция или приведение типа на колонке в WHERE (date(created) = ..., col::text = ...) отключает обычную статистику по колонке и часто индекс. Помогает выражение на константе или индекс по выражению, либо расширенная статистика.
- Коррелированные колонки: планировщик по умолчанию считает условия независимыми и перемножает селективности, занижая оценку. Лечится командой CREATE STATISTICS по группе колонок.
- Маленький default_statistics_target (по умолчанию 100) на колонке с тысячами разных значений дает грубую гистограмму. Поднимите цель для проблемной колонки через ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.
- Привычка лепить хинты как в других СУБД: в ванильном PostgreSQL хинтов нет. Управляют планом через статистику, индексы и параметры стоимости, а не директивами в запросе.
- enable_seqscan = off и подобные флаги в продакшен-коде - это костыль, который маскирует настоящую причину (устаревшая статистика, неверный random_page_cost). Используйте их только для диагностики.
- Подключитесь к демобазе: psql -d demo. Выполните ANALYZE bookings.ticket_flights и ANALYZE bookings.flights.
- Через pg_stats посмотрите n_distinct и most_common_vals для ticket_flights.fare_conditions. Прикиньте руками, сколько строк должно попасть под fare_conditions = 'Business'.
- Сделайте EXPLAIN на выборке по book_ref = '0824C5' из bookings и убедитесь, что это Index Scan. Затем EXPLAIN на flights WHERE status = 'Arrived' и убедитесь, что это Seq Scan. Объясните разницу через селективность.
- Запустите EXPLAIN (ANALYZE, BUFFERS) на соединении tickets с ticket_flights по ticket_no с фильтром по классу Business. Найдите тип join и сравните rows с actual rows.
- Поменяйте SET random_page_cost = 1.1 в текущей сессии и повторите EXPLAIN проблемного запроса. Заметьте, изменился ли выбранный план.
- Через SET enable_hashjoin = off заставьте планировщик показать альтернативный метод соединения и сравните стоимость с исходным Hash Join. Верните параметр командой RESET.
- Создайте CREATE STATISTICS на паре коррелированных колонок (например, departure_airport и arrival_airport в flights), запустите ANALYZE и сравните оценку строк до и после.
- В каких единицах планировщик измеряет стоимость и почему random_page_cost больше seq_page_cost?
- Что хранится в pg_statistic и какая команда наполняет этот каталог?
- Чем bitmap scan отличается от index scan и в какой ситуации он выгоднее?
- Когда планировщик предпочтет nested loop, а когда hash join?
- Почему оценка кардинальности может сильно разойтись с фактом и как это исправить?
- Что изменится в выборе плана, если поднять default_statistics_target для колонки?
