Книга «PostgreSQL 18. Оптимизация запросов»: основы работы планировщика в компактном формате

Компания Postgres Professional представляет книгу «PostgreSQL 18. Оптимизация запросов». Это введение в работу планировщика PostgreSQL. В книге рассказано о методах доступа к данным и способах соединения, показано, как читать планы выполнения, объяснена роль статистики и разобраны основные приёмы оптимизации. 

В основе книги лежит учебный курс «QPT. Оптимизация запросов». Павел Толмачёв переработал его материалы так, чтобы нужные сведения были под рукой. Её удобно читать в метро или на остановке, когда нет возможности открыть ноутбук. Результаты демонстрационных запросов вплетены в повествование, а часть второстепенных примеров убрана ради уменьшения объёма.

Для кого эта книга?

Книга ориентирована на прикладных разработчиков и администраторов баз данных, которые уже имеют базовый опыт работы с PostgreSQL и хотят перестать воспринимать планировщик запросов как «чёрный ящик». Руководство будет особенно полезно тем, кто:

• уверенно владеет языком SQL, знаком с операционными системами семейства Unix и обладает базовыми знаниями об устройстве и конфигурировании сервера на уровне курсов DBA1 или DEV1;

• хочет детально разобраться в том, как именно СУБД разбирает, трансформирует, планирует и выполняет запросы, а также как планировщик оценивает кардинальность и стоимость операций;

• сталкивается с падением производительности, хочет научиться находить узкие места с помощью профилирования и осознанно оптимизировать как короткие (OLTP), так и длинные аналитические (OLAP) запросы;

• планирует пройти курс «QPT. Оптимизация запросов» или хочет оперативно освежить знания, когда под рукой нет компьютера с виртуальной машиной (например, в дороге или метро).

Что внутри?

Материал разделён на девять глав. Во водной части авторы рассказывают о связи книги с курсом QPT, объясняют отличия книжного изложения от работы с лабораторными стендами и перечисляют необходимые предварительные знания.

Основной материал книги последовательно раскрывает все этапы анализа и ускорения запросов:

• Глава I. Демонстрационная база данных. Знакомство с предметной областью и схемой учебной базы данных «Авиаперевозки», состоящей из девяти таблиц и трёх представлений. Разбираются основные сущности (бронирования, билеты, сегменты перелётов, рейсы, маршруты с темпоральными интервалами, самолёты, места в салоне и посадочные талоны), а также механизм локализации данных с помощью JSON.

• Глава II. Планирование и выполнение. Полный цикл обработки SQL‑запроса по простому протоколу: синтаксический и семантический разбор, переписывание (трансформация правил и представлений), планирование и конвейерное выполнение. Отдельно рассматривается расширенный протокол запросов: подготовленные операторы (PREPARE / EXECUTE), работа с частными (custom) и общими (generic) планами, использование курсоров. Подробно объясняется математическая модель планировщика: рекурсивная оценка селективности, кардинальности и стоимости (начальной и полной), поиск лучшего плана и анализ вывода команды EXPLAIN (ANALYZE).

• Глава III. Методы доступа. Детальный разбор способов чтения данных: последовательное сканирование (Seq Scan, влияние «мёртвых» версий строк, важность очистки VACUUM и работа буферных колец), индексное сканирование (Index Scan на примере B‑дерева при поиске одного значения или диапазона в прямом и обратном направлениях), сканирование по битовой карте (Bitmap Index Scan и Bitmap Heap Scan, объединение карт BitmapOr, появление неточных lossy‑фрагментов при нехватке work_mem, физическая кластеризация таблиц CLUSTER) и сканирование только индекса (Index‑Only Scan, роль карты видимости и покрывающих INCLUDE‑индексов). Также рассматривается параллельный доступ (Gather, настройка пула рабочих процессов, Parallel Seq Scan, Parallel Index (Only) Scan, Parallel Bitmap Heap Scan), приводится итоговое сравнение эффективности всех методов в зависимости от селективности условий.

• Глава IV. Сортировка и группировка. Устройство механизмов упорядочивания данных: быстрая сортировка в оперативной памяти (quicksort), частичная сортировка (top‑N heapsort при наличии LIMIT), внешняя сортировка слиянием на диске (external merge с записью временных файлов), инкрементальная сортировка (Incremental Sort) частично упорядоченных наборов и применение сортировки при построении B‑деревьев. Во второй части главы разбираются группировка и удаление дубликатов методом хеширования (HashAggregate) и сортировки (GroupAggregate, Unique), а также особенности вычисления и партиционирования оконных функций (WindowAgg).

• Глава V. Способы соединения. Подробный анализ трёх базовых алгоритмов попарного соединения наборов строк. Рассматриваются соединение вложенными циклами (Nested Loop — параметризованное и непараметризованное, а также кеширование результатов внутреннего набора в узле Memoize), соединение хешированием (Hash Join — однопроходное в памяти и двухпроходное с разбиением на пакеты и сбросом на диск, а также параллельный алгоритм Parallel Hash Join с разделяемой хеш‑таблицей) и соединение слиянием (Merge Join для отсортированных наборов). Для каждого алгоритма оцениваются вычислительная сложность, расходование памяти и работа в параллельных планах.

• Глава VI. Статистика. Раздел о том, на какие данные опирается оптимизатор при расчётах. Описана базовая статистика уровня таблицы (reltuples, relpages, relallvisible) и уровня отдельных столбцов (доля неопределённых значений null_frac, число уникальных значений n_distinct, списки наиболее частых значений most_common_vals и их частот most_common_freqs, гистограммы histogram_bounds, физическая корреляция correlation), а также логика переключения между частными и общими планами (plan_cache_mode). Во второй части главы разбирается расширенная статистика (CREATE STATISTICS), помогающая планировщику на коррелированных предикатах: функциональные зависимости (dependencies), наиболее частые комбинации (mcv), число уникальных комбинаций (ndistinct) и статистика по выражениям.

• Глава VII. Профилирование. Практическое руководство по поиску проблемных участков системы. Авторы объясняют, что именно нужно измерять (почему объём ввода‑вывода в страницах зачастую информативнее времени отклика), как оценивать профиль приложения и профилировать процедурный код на PL/pgSQL (PL Profiler, plpgsql_check). Рассматривается построение профиля SQL‑запросов по журналу сервера (auto_explain, pgBadger) и статистике выполнения (pg_stat_statements). Приведён детальный разбор одного проблемного запроса с помощью EXPLAIN (analyze, buffers).

• Глава VIII. Материализация и функции. Анализ сохранения промежуточных наборов строк на разных уровнях: узел Materialize, материализация подзапросов CTE (WITH... AS MATERIALIZED / NOT MATERIALIZED), работа с временными таблицами и материализованными представлениями (MATERIALIZED VIEW, включая неблокирующее обновление CONCURRENTLY и инкрементальное обновление через pg_ivm). Во второй части подробно разбираются функции: категории изменчивости (VOLATILE, STABLE, IMMUTABLE), подстановка (inlining) тела функции в запрос, вызов табличных функций во FROM (Function Scan) и в SELECT (ProjectSet с выдачей строк по требованию), настройка параметров COST и ROWS, вспомогательные функции планировщика (SUPPORT) и пометки параллельности (PARALLEL UNSAFE, RESTRICTED, SAFE).

• Глава IX. Подходы к настройке. Изученные механизмы сводятся в единую методологию. Рассматривается базовая настройка сервера (параллелизм, параметры ввода‑вывода и асинхронного чтения, распределение памяти, табличные пространства, секционирование и шардирование, настройки статистики и весовых коэффициентов стоимости) и настройка на уровне приложения (пулы соединений, курсоры, борьба с мелкими запросами от ORM, нормализация и денормализация схемы данных, выбор типов данных и влияние ограничений целостности на планы запросов). В конце приведены примеры пошаговой оптимизации «коротких» (OLTP) и «длинных» (OLAP) запросов.

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

Что нового в этом издании?

• Актуализация под PostgreSQL 18. Все планы запросов и описания механизмов приведены в соответствии с 18-й версией СУБД. Читатели увидят в выводе EXPLAIN ANALYZE актуальную диагностическую информацию PostgreSQL 18 (например, счётчик обращений к индексу Index Searches и обновлённый формат отображения числа строк actual rows), а в разделе о конфигурировании сервера — новые настройки подсистемы ввода‑вывода, включая выбор метода асинхронного ввода‑вывода (io_method) и параметр io_combine_limit.

• Переход на новую демонстрационную базу данных «Авиаперевозки». В отличие от издания по PostgreSQL 16, использовавшего демобазу от 15.08.2017, новая книга полностью построена на обновлённой базе «Авиаперевозки» от 01.09.2025 (объёмом около 2,7 Гбайт с данными за полгода). В новой схеме переработана структура таблиц и представлений (появились темпоральные интервалы действия маршрутов validity в таблице routes, таблица сегментов segments, представление расписания timetable и др.), что позволило сделать практические примеры оптимизации ещё более интересными и жизненными.

• Взгляд в будущее. В PostgreSQL 18 нет подсказок оптимизатору, а в 19-й версии появится расширение pg_plan_advice. Вместе с pg_stash_advice оно позволит управлять подсказками без изменения текста запроса. Пока же читатель получает неявные подсказки через параметры конфигурации и сторонние расширения вроде pg_hint_plan. 

Как получить книгу?

Книга «PostgreSQL 18. Оптимизация запросов» распространяется свободно в электронном виде: 

https://postgrespro.ru/education/books/qptbook

Найти демонстрационную базу данных «Авиаперевозки» и материалы одноимённого учебного курса QPT можно на официальном сайте Postgres Professional.