Выполнение и оптимизация запросов

Язык SQL декларативен: разработчик описывает, какие данные ему нужны, но не указывает алгоритм их извлечения. Задача превращения декларативного SQL-запроса в эффективный императивный план выполнения возложена на ядро СУБД — планировщик (оптимизатор) и исполнитель (executor).

В этой теме мы проследим весь путь запроса от получения сетевого пакета до выдачи результирующего набора строк. Мы разберём классическую модель итераторов Volcano, алгоритмы соединений (Nested Loop, Hash Join, Merge Join), стоимостную модель планировщика PostgreSQL, чтение планов EXPLAIN (ANALYZE, BUFFERS) и методы устранения типичных узких мест в промышленных запросах.

Путь запроса в СУБД

Прежде чем запрос начнёт выполняться, он проходит через четыре стадии обработки ¹⁴:

1. Разбор (Parser): лексический и синтаксический анализ строки SQL. Парсер проверяет грамматику и строит дерево разбора (parse tree), где каждый узел представляет ключевое слово или выражение.
2. Переписывание (Rewriter): семантический анализ и трансформация дерева. На этом этапе проверяется существование таблиц и столбцов в системном каталоге pg_class и pg_attribute, вызовы представлений (VIEW) заменяются их телами, а правила (RULE) встраиваются в дерево запроса.
3. Планирование (Planner / Optimizer): поиск оптимального плана выполнения на основе стоимостной модели. Планировщик перебирает возможные порядки соединений, выбирает методы доступа к таблицам и оценивает затраты каждого варианта в условных единицах стоимости (cost units).
4. Выполнение (Executor): физическое исполнение выбранного плана и передача строк клиенту.

Модель итераторов (модель Volcano)

Исполнитель запросов в PostgreSQL построен по конвейерной итераторной модели Volcano Гоетца Грэфе ³. План выполнения представляет собой дерево узлов (Nodes). Каждый узел реализует стандартизированный интерфейс с тремя базовыми функциями:

• ExecInit(): инициализация узла, выделение структур памяти;
• ExecProcNode(): запрос следующей строки (Next);
• ExecEnd(): освобождение занятых ресурсов.

Главное достоинство модели итераторов — конвейерная обработка (pipelining): СУБД не материализует промежуточные результаты в памяти целиком. Корневой узел вызывает Next() у дочернего, тот передаёт запрос ещё ниже, и первая строка возвращается клиенту немедленно. Это обеспечивает мгновенное время отклика для запросов с LIMIT.

Исключение составляют блокирующие операции (англ. pipeline breakers): сортировка (Sort) и построение хеш-таблицы (Hash). Чтобы отсортировать данные или построить хеш, узел обязан сначала вычитать и накопить в памяти все входящие строки.

Методы доступа к таблицам

При обращении к отдельной таблице планировщик выбирает один из четырёх методов доступа ¹⁴:

1. Последовательное сканирование (Seq Scan): считывание всех страниц таблицы подряд. Оптимально при низкой селективности (выборке более 15–20 % строк), а также для таблиц, целиком помещающихся в несколько блоков памяти.
2. Индексное сканирование (Index Scan): точечный спуск по B-дереву и немедленное чтение соответствующей страницы кучи (heap). Идеально для уникальных выборок (WHERE id = 42) и диапазонного чтения с сортировкой.
3. Сканирование только индекса (Index Only Scan): извлечение данных напрямую из индекса без чтения страниц таблицы, если все запрошенные столбцы есть в индексе и подтверждены картой видимости (Visibility Map).
4. Сканирование по битовой карте (Bitmap Index Scan + Bitmap Heap Scan): двухфазный метод. Первая фаза сканирует индекс и строит в оперативной памяти битовую карту: в ней битами помечаются страницы таблицы, содержащие искомые строки. Вторая фаза сортирует номера страниц по физическому порядку на диске и последовательно считывает только нужные блоки. Это превращает хаотичные случайные чтения (random I/O) в эффективное последовательное чтение и позволяет комбинировать несколько индексов по условиям AND / OR логическими операциями BitmapAnd и BitmapOr.

Методы соединения отношений (Joins)

В реляционной алгебре соединение R \bowtie S требует сопоставления кортежей. В PostgreSQL реализованы три фундаментальных алгоритма соединения ¹²:

1. Соединение вложенными циклами (Nested Loop)

Внешний цикл перебирает строки отношения R, и для каждой строки во внутреннем цикле ищутся совпадения в S:

\text{Сложность без индекса: } O(|R| \cdot |S|), \quad \text{с индексом по } S: O(|R| \cdot \log |S|)

• Когда выигрывает: внешнее отношение R очень мало (1–100 строк), а по внутреннему отношению S доступен эффективный индекс.
• Особенность: не требует предварительного чтения всего набора данных и начинает отдавать строки немедленно.

2. Соединение хешированием (Hash Join)

Алгоритм выполняется в две фазы:

1. Фаза сборки (Build): меньшее отношение считывается целиком, и по ключу соединения в памяти строится хеш-таблица.
2. Фаза зондирования (Probe): большее отношение последовательно сканируется, и для каждой строки вычисляется хеш ключа, по которому в хеш-таблице мгновенно находятся совпадения.

• Сложность: линейная O(|R| + |S|).
• Когда выигрывает: соединение больших неотсортированных таблиц по предикату равенства (R.id = S.r_id).
• Ограничение: требует достаточного объёма памяти work_mem. Если хеш-таблица не помещается в work_mem, PostgreSQL разбивает соединение на несколько пакетов (batches) со сбросом временных файлов на диск, что резко замедляет запрос.

3. Соединение слиянием (Merge Join)

Оба отношения предварительно упорядочиваются по ключу соединения, после чего два курсора синхронно двигаются по потокам данных, как в алгоритме сортировки слиянием:

• Сложность: O(|R| \log |R| + |S| \log |S|) при необходимости сортировки, либо O(|R| + |S|), если данные уже упорядочены индексами.
• Когда выигрывает: обе таблицы уже отсортированы нужным образом (например, читаются по B-дереву), либо результат запроса сам требует ORDER BY по ключам соединения.

Стоимостная модель и статистика

Параметры стоимости

Оптимизатор PostgreSQL — стоимостной (Cost-Based Optimizer, CBO) ¹². Каждому элементарному действию приписан весовой коэффициент в условных единицах:

• seq_page_cost = 1.0 — базовый эталон: последовательное чтение одной страницы с диска;
• random_page_cost = 4.0 — случайное чтение страницы (для современных NVMe SSD администраторы снижают этот параметр до 1.1–1.5, чтобы планировщик охотнее выбирал индексный доступ);
• cpu_tuple_cost = 0.01 — обработка одной строки процессором;
• cpu_index_tuple_cost = 0.005 — обработка одной индексной записи;
• cpu_operator_cost = 0.0025 — вычисление одного оператора или функции в условии WHERE.

Итоговая оценка стоимости запроса складывается из затрат на ввод-вывод (I/O) и процессорную обработку:

\text{Total Cost} = (\text{Pages}_{\text{seq}} \cdot 1.0) + (\text{Pages}_{\text{rnd}} \cdot 4.0) + (\text{Tuples} \cdot 0.01) + (\text{Operators} \cdot 0.0025)

Системная статистика pg_stats

Чтобы оценить число строк, попадающих под условие, планировщик опирается на статистику, собираемую фоновым процессом ANALYZE в представлении pg_stats ¹⁴:

• n_distinct: оценка числа уникальных значений (положительное число — абсолютное количество; отрицательное — доля от общего числа строк);
• most_common_vals (MCV) и most_common_freqs (MCF): списки самых частых значений и их вероятностей (для условий вида WHERE status = 'delivered');
• histogram_bounds: гистограмма равных глубин (Equi-depth Histogram), делящая диапазон значений на интервалы с одинаковым числом строк (для условий WHERE price BETWEEN 500 AND 1500);
• correlation: мера физической корреляции порядка строк на диске с их логическим порядком (1.0 — строки идеально упорядочены; 0.0 — хаотичный разброс).

Коррелированные столбцы и CREATE STATISTICS

Стандартный планировщик предполагает, что условия по разным столбцам независимы:

P(A \land B) = P(A) \cdot P(B)

Если столбцы логически связаны (например, city = 'Москва' и region = 'Московская область'), перемножение селективностей занижает число ожидаемых строк в сотни раз. Планировщик ошибочно ожидает 1 строку вместо 10 000 и выбирает губительный Nested Loop.

Решение проблемы — сбор расширенной многостолбцовой статистики ⁴:

CREATE STATISTICS stat_city_region (dependencies, ndistinct, mcv)
ON city, region FROM bookstore.customer;
ANALYZE bookstore.customer;

Чтение планов EXPLAIN

Команда EXPLAIN показывает расчетный план планировщика, а EXPLAIN (ANALYZE, BUFFERS) физически выполняет запрос и сравнивает прогноз с реальностью ¹:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM bookstore.orders WHERE customer_id = 12345;

Пример разбора строки плана:

Bitmap Heap Scan on orders  (cost=12.45..450.20 rows=110 width=48) (actual rows=115 loops=1)

1. cost=12.45..450.20:
   • Первое число (12.45) — startup cost: затраты до выдачи первой строки клиенту (например, время на построение битовой карты);
   • Второе число (450.20) — total cost: полная расчетная стоимость извлечения всех строк.
2. rows=110: расчетная кардинальность (сколько строк ожидает планировщик).
3. width=48: средний размер результирующей строки в байтах.
4. actual rows=115 loops=1: фактические результаты. Если loops > 1 (узел вызывался многократно, например, во внутреннем цикле Nested Loop), общее число строк равно actual rows * loops.
5. Buffers: shared hit=42 read=5:
   • hit: страницы, прочитанные из оперативной памяти (Shared Buffers);
   • read: физические чтения страниц с диска.

Золотое правило оптимизации: главное внимание при чтении планов обращается на расхождение между расчетными rows и фактическими actual rows. Если они расходятся на порядки — статистика устарела, столбцы коррелированы либо в условии использована функция, лишающая оптимизатор математической модели.

Типичные проблемы и профилирование

Ошибки в коде запросов

Разбор типичных ошибок индексирования и написания запросов подробно систематизирован М. Винандом ⁵:

1. Неявное приведение типов (Implicit Casting): Если столбец phone имеет тип varchar, а в запросе написано WHERE phone = 79991234567 (числовой литерал), PostgreSQL неявно оборачивает столбец в приведение: WHERE phone::numeric = 79991234567. Индекс по phone отключается, вызывая полный Seq Scan.
2. Ловушка NOT IN с NULL: Запрос WHERE id NOT IN (SELECT parent_id FROM t) при наличии хотя бы одного NULL в подзапросе возвращает пустой результат для всей таблицы из-за трёхзначной логики SQL (NULL = x дает UNKNOWN). Планировщик вынужден использовать тяжелый SubPlan. Решение — оператор NOT EXISTS.

Профилирование с pg_stat_statements

Для поиска проблемных запросов на промышленном сервере нельзя опираться на ручной запуск EXPLAIN. Стандартом мониторинга является расширение pg_stat_statements ¹, аккумулирующее статистику всех выполняемых запросов с нормализацией параметров:

SELECT query, calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Этот отчет мгновенно показывает запросы, потребляющие 80 % ресурсов процессора и диска, позволяя точечно создавать недостающие индексы и оптимизировать соединения.

Итоги темы

Оптимизация запросов — это математически выверенный процесс сопоставления структуры запроса, индексов и статистических распределений данных. Планировщик транслирует SQL через конвейер Volcano, выбирая между последовательным чтением, индексами и битовыми картами. При соединениях Nested Loop оптимален для точечных индексных выборок, Hash Join масштабируется на большие несортированные объемы, а Merge Join эффективен для предварительно упорядоченных потоков. Анализ EXPLAIN (ANALYZE, BUFFERS) и контроль расхождения rows с actual rows — фундаментальный навык разработчика и администратора баз данных.

В следующей теме 12 мы перейдем к механизмам изоляции транзакций, блокировкам и многоверсионному управлению параллельным доступом (MVCC).

Литература

1. Рогов Е. В. PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024.
2. Selinger P. G., Astrahan M. M., Chamberlin D. D., Lorie R. A., Price T. G. Access Path Selection in a Relational Database Management System. — Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data, 1979, С. 23–34.
3. Graefe G. Volcano — An Extensible and Efficient Query Evaluation System. — IEEE Transactions on Knowledge and Data Engineering, 1994, Vol. 6, No. 1, С. 120–135.
4. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. Chapter 14. Performance Tips (Using EXPLAIN; Statistics Used by the Planner; Controlling the Planner with Guiding Options). — 2024. https://www.postgresql.org/docs/16/performance-tips.html
5. Winand M. SQL Performance Explained. — 2012. https://use-the-index-luke.com/

Дополнительные материалы

Вопросы для самопроверки

1. Какие четыре основных этапа проходит SQL-запрос от получения сервером до выдачи строк клиенту?
2. В чём заключается принцип итераторной модели (модель Volcano) и почему узлы плана не материализуют весь набор данных сразу, если этого не требует логика операции?
3. В чём принципиальное различие между методами доступа Seq Scan, Index Scan, Index Only Scan и Bitmap Heap Scan? Когда планировщик выбирает сканирование битовой карты?
4. Опишите алгоритмы работы трёх методов соединения: Nested Loop, Hash Join и Merge Join. Какова временная сложность каждого метода при наличии и отсутствии подходящих индексов?
5. Что означает параметр loops в выводе EXPLAIN (ANALYZE) и почему фактическое время и число строк в поддереве нужно умножать на это значение?
6. Как рассчитывается стоимость узла плана в единицах seq_page_cost, random_page_cost, cpu_tuple_cost и cpu_operator_cost?
7. Какую статистику о столбцах собирает процесс ANALYZE в системном представлении pg_stats? Для чего предназначены списки частых значений (MCV) и гистограммы?
8. Почему планировщик фатально ошибается в оценке кардинальности при наличии функциональной зависимости между столбцами, и как команда CREATE STATISTICS решает эту проблему?
9. Что происходит, когда размер хеш-таблицы или массива сортировки превышает выделенный лимит оперативной памяти work_mem?
10. Почему предикат WHERE date_trunc('month', created_at) = '2024-03-01' делает запрос non-sargable и отключает индекс по столбцу created_at?
11. В чём кроется опасность оператора NOT IN при наличии значений NULL в результатах подзапроса и почему антисоединение через NOT EXISTS работает существенно быстрее?
12. Как неявное приведение типов (например, сравнение столбца bpchar с литералом text) может скрыть существующий индекс от оптимизатора?
13. Каким образом расширение pg_stat_statements группирует запросы и почему оно является главным инструментом поиска узких мест в эксплуатируемой СУБД?

Глоссарий темы

Шпаргалка по анализу планов и настройкам

Формат команды EXPLAIN

-- Теоретический план без фактического выполнения:
EXPLAIN SELECT ...;

-- Полный профиль выполнения с реальным временем и буферным кэшем:
EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS) SELECT ...;

-- Текстовый план в формате JSON (удобно для программного анализа):
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Ключевые маркеры в плане выполнения

Sort Method: external merge  Disk: 4096kB  --> Критично! Не хватает work_mem, сортировка на диске.
Sort Method: quicksort  Memory: 1024kB     --> Нормально, сортировка целиком в RAM.
Hash (Buckets: 1024  Batches: 4  Memory)   --> Batches > 1 означает сброс хеш-таблицы на диск.
rows=10 width=32 (actual rows=500000)      --> Ошибка оценки кардинальности в 50 000 раз!
Buffers: shared hit=42 read=1250 dirtied=5 --> read указывает на физическое чтение с диска.

Диагностика через pg_stat_statements

-- Просмотр топ-5 запросов по общему времени ожидания ввода-вывода (I/O):
SELECT queryid,
       round(blk_read_time::numeric, 2) AS read_io_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       substr(query, 1, 80) AS query_text
FROM pg_stat_statements
ORDER BY blk_read_time DESC
LIMIT 5;

-- Сброс накопленных счетчиков:
SELECT pg_stat_statements_reset();

Типичные ошибки и антипаттерны

1. Запуск EXPLAIN ANALYZE для деструктивных запросов без транзакции: Команда EXPLAIN ANALYZE DELETE FROM orders; фактически удалит все записи из таблицы! Если необходимо исследовать план UPDATE или DELETE, оборачивайте команду в транзакцию с откатом: BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.
2. Игнорирование множителя loops: Если внутренний узел Nested Loop показывает actual time=0.010..0.015 rows=2 loops=100000, это означает, что узел выполнялся 100 000 раз, вернул 200 000 строк и суммарно потратил 1.5 секунды, а не 0.015 миллисекунды.
3. Глобальное завышение work_mem в postgresql.conf: Параметр work_mem выделяется на каждую операцию сортировки или хеширования внутри запроса. Сложный запрос с четырьмя соединениями и двумя сортировками может использовать 6 \times \text{work\_mem}. Завышение значения на многопользовательском сервере приведёт к аварийному завершению процессов по OOM-killer. Настройку следует повышать точечно в рамках ресурсоёмкой сессии: SET LOCAL work_mem = '64MB';.
4. Использование NOT IN со значениями NULL: Если подзапрос возвращает хотя бы одну строку со значением NULL, всё выражение col NOT IN (SELECT ...) принимает значение UNKNOWN (ложно для WHERE), что блокирует использование индекса и заставляет СУБД проверять условие через квадратичный SubPlan. Следует использовать NOT EXISTS или LEFT JOIN ... WHERE key IS NULL.
5. Манипуляции флагами enable_* в production: Параметры enable_seqscan, enable_nestloop, enable_hashjoin предназначены исключительно для экспериментов и отладки планов. Их отключение на рабочем сервере не ускоряет работу, а лишь искусственно накладывает штрафную стоимость в 10^{10} на соответствующие узлы.

Литература

1. Рогов Е. В. PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024. — Глава «Оптимизация и выполнение запросов: от дерева разбора к плану исполнения» ¹.
2. Мартин Клеппман. Высоконагруженные приложения. Программирование, масштабирование, поддержка. — СПб.: Питер, 2018. — Глава 3 «Подсистемы хранения и извлечения данных» ².
3. Документация PostgreSQL 16. Использование EXPLAIN (Using EXPLAIN). — URL: https://www.postgresql.org/docs/16/using-explain.html ³.
4. Документация PostgreSQL 16. Планировщик и сбор расширенной статистики (Multivariate Statistics Examples). — URL: https://www.postgresql.org/docs/16/multivariate-statistics-examples.html ⁴.
5. Документация PostgreSQL 16. Модуль pg_stat_statements. — URL: https://www.postgresql.org/docs/16/pgstatstatements.html ⁵.

Лабораторная работа 11. Чтение планов и оптимизация запросов

• Раздел курса: тема 11 «Выполнение и оптимизация запросов»

Введение

На небольших таблицах практически любой синтаксически корректный SQL-запрос выполняется за доли миллисекунды. Однако при росте объёма данных до сотен тысяч и миллионов строк неоптимальные запросы, неудачно выбранные методы соединений, устаревшая статистика или неявные приведения типов могут замедлить выполнение в сотни и тысячи раз.

Оптимизатор PostgreSQL работает на основе модели стоимости (cost-based optimizer). Чтобы принимать верные решения, разработчик или администратор баз данных должен уметь «читать мысли» оптимизатора с помощью команды EXPLAIN (ANALYZE, BUFFERS), выявлять расхождения между оценкой и фактом, понимать различия между методами сканирования и соединения, а также уметь профилировать реальную нагрузку сервера с помощью модуля pg_stat_statements.

В этой лабораторной работе вы научитесь читать планы выполнения, исследуете поведение алгоритмов соединения и сортировки, устраните систематические ошибки планировщика с помощью расширенной статистики, решите шесть практических задач оптимизации медленных запросов в учебной базе «Книжный узел» и настроите профилирование рабочей нагрузки.

Цель работы

Научиться анализировать планы выполнения запросов в PostgreSQL, выявлять и устранять узкие места в SQL-запросах, управлять статистикой планировщика и находить наиболее ресурсоёмкие операции в базе данных.

После выполнения работы вы сможете:

• интерпретировать дерево операторов EXPLAIN (ANALYZE, BUFFERS), соотнося затраты (cost), реальное время (actual time), количество строк (rows) и обращения к буферному кэшу (Buffers);
• определять условия применимости алгоритмов соединения: Nested Loop, Hash Join и Merge Join;
• выявлять и устранять сброс промежуточных данных сортировки и хеширования на диск с помощью настройки work_mem;
• создавать многомерную расширенную статистику (CREATE STATISTICS) при наличии коррелированных предикатов;
• находить и исправлять типичные ошибки: функции над индексируемыми столбцами, неявные приведения типов, неэффективные конструкции NOT IN и неоптимальные коррелированные подзапросы;
• собирать и анализировать метрики выполнения запросов с помощью модуля pg_stat_statements под нагрузкой pgbench.

Оборудование и учебная среда

Работа выполняется в учебной базе данных bookstore50 (масштаб scale=50, около 450 МБ). Для выполнения работы потребуются:

• клиент psql;
• утилита нагрузочного тестирования pgbench;
• интерпретатор python3 с библиотекой psycopg (для запуска проверочного скрипта check.py);
• расширение PostgreSQL pg_stat_statements;
• скрипты из архива окружения lr-env.zip.

Подготовка базы данных перед началом работы:

# Распаковать архив окружения темы 11:
unzip lr-env.zip && cd lr-env

# Если база bookstore50 ещё не создана (масштаб scale=50):
createdb bookstore50
psql -d bookstore50 -f schema.sql
psql -d bookstore50 -v scale=50 -f data.sql

# Привести базу к исходному состоянию работы 11:
psql -d bookstore50 -f setup.sql

Скрипт setup.sql идемпотентен: он удаляет пользовательские индексы, кроме первичных и уникальных ключей, удаляет расширенную статистику, создаёт контрольный индекс orders (created_at) и вспомогательную схему lab11 с таблицей order_log.

Порядок выполнения работы

Часть 1. Анатомия плана выполнения EXPLAIN (ANALYZE, BUFFERS)

1. Запустите сессию psql -d bookstore50.
2. Выполните команду EXPLAIN для простого фильтрующего запроса:

   EXPLAIN SELECT * FROM bookstore.orders WHERE customer_id = 42;
   

   Обратите внимание: запрос не выполнялся. Значения cost=... и rows=... — это теоретическая оценка планировщика.
3. Теперь выполните запрос с фактическим измерением времени и буферов:

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT * FROM bookstore.orders WHERE customer_id = 42;
   

4. Сопоставьте вывод команды со структурой плана:
   • Найдите узел сканирования: последовательное чтение Seq Scan по таблице orders.
   • Сравните расчётное число строк rows=... и фактическое actual rows=....
   • Посмотрите на строку Buffers: shared hit=... read=.... Сколько страниц было прочитано из оперативной памяти (hit), а сколько запрошено с диска (read)? При повторном запуске все страницы должны попасть в shared hit.
   • Зафиксируйте общее время планирования (Planning Time) и выполнения (Execution Time).

Часть 2. Методы соединений и принудительное переключение

PostgreSQL поддерживает три базовых метода соединений: Nested Loop, Hash Join и Merge Join. Планировщик выбирает метод с наименьшей расчётной стоимостью, однако для исследования мы можем временно запрещать планировщику выбор отдельных методов с помощью конфигурационных параметров сессии.

Рассмотрим соединение таблицы клиентов customer и таблицы заказов orders:

SELECT c.full_name, o.order_id, o.total_amount
FROM bookstore.customer c
JOIN bookstore.orders o ON o.customer_id = c.customer_id
WHERE c.city = 'Казань';

1. Получите базовый план выполнения запроса с помощью EXPLAIN (ANALYZE, BUFFERS). Зафиксируйте выбранный метод соединения (по умолчанию обычно Hash Join), общее время и объём использованной оперативной памяти под хеш-таблицу.
2. Принудительно отключите Hash Join и повторите замер:

   SET enable_hashjoin = off;
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT c.full_name, o.order_id, o.total_amount
   FROM bookstore.customer c
   JOIN bookstore.orders o ON o.customer_id = c.customer_id
   WHERE c.city = 'Казань';
   

   Планировщик переключится на Merge Join. Обратите внимание, что перед слиянием потребовалась сортировка (Sort) обоих наборов данных по ключу customer_id. Сравните время выполнения.
3. Отключите Merge Join, вынудив оптимизатор использовать вложенные циклы:

   SET enable_mergejoin = off;
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT c.full_name, o.order_id, o.total_amount
   FROM bookstore.customer c
   JOIN bookstore.orders o ON o.customer_id = c.customer_id
   WHERE c.city = 'Казань';
   

   Посмотрите на узел Nested Loop. Без индекса по orders.customer_id для каждого покупателя из Казани СУБД выполняет Seq Scan миллионной таблицы заказов! Обратите внимание на параметр loops=... во внутреннем узле плана.
4. Сбросьте параметры сессии в значения по умолчанию:

   RESET enable_hashjoin;
   RESET enable_mergejoin;
   

5. Заполните сравнительную таблицу времени выполнения и стоимости трёх методов соединения.

Часть 3. Влияние параметра work_mem на сортировку и хеширование

Когда объёма оперативной памяти, выделенной параметром work_mem, недостаточно для размещения хеш-таблицы или массива сортировки, PostgreSQL сбрасывает временные данные на диск в виде временных файлов (spill to disk).

1. Выполните сортировку книг по цене с малым значением work_mem:

   SET work_mem = '64kB';
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT book_id, title, price, publication_year
   FROM bookstore.book
   ORDER BY price DESC;
   

   Найдите в плане строки: Sort Method: external merge Disk: ...kB Зафиксируйте объём временных файлов, сброшенных на диск, и время выполнения.
2. Увеличьте work_mem до достаточного значения:

   SET work_mem = '32MB';
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT book_id, title, price, publication_year
   FROM bookstore.book
   ORDER BY price DESC;
   

   Обратите внимание на изменение метода сортировки: Sort Method: quicksort Memory: ...kB Сравните время выполнения запроса.
3. Сбросьте настройку:

   RESET work_mem;
   

Часть 4. Коррелированные столбцы и расширенная статистика

Планировщик предполагает, что условия в предикате WHERE col1 = 'A' AND col2 = 'B' независимы, и перемножает их селективности (P(A \cap B) = P(A) \cdot P(B)). Если столбцы коррелируют, планировщик катастрофически занижает оценку кардинальности.

В схеме bookstore город покупателя city строго коррелирует с префиксом телефона или почтовым индексом. Проверим поведение оптимизатора:

1. Выполните запрос с оценкой кардинальности:

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT customer_id, full_name
   FROM bookstore.customer
   WHERE city = 'Москва' AND phone_code = '495';
   

   Сравните расчётное число строк (rows=...) с реальным (actual rows=...). Расхождение может составлять порядок величины и более!
2. Создайте расширенную статистику зависимостей для коррелированных столбцов:

   CREATE STATISTICS customer_city_phone_stat (dependencies)
   ON city, phone_code FROM bookstore.customer;
   ANALYZE bookstore.customer;
   

3. Повторите выполнение EXPLAIN (ANALYZE, BUFFERS). Убедитесь, что оценка кардинальности приблизилась к фактическому числу строк.
4. Проверьте содержимое системного каталога pg_statistic_ext:

   SELECT stxname, stxkeys, stxdndeps
   FROM pg_statistic_ext
   WHERE stxname = 'customer_city_phone_stat';
   

Часть 5. Практическая оптимизация медленных запросов (Q1–Q6)

В подкаталоге queries/ находятся шесть «проблемных» запросов, каждый из которых страдает от классического архитектурного антипаттерна. Для каждого запроса выполните цикл:

1. Получите EXPLAIN (ANALYZE, BUFFERS) исходного запроса (кроме Q4, для которого выполняйте только EXPLAIN без ANALYZE!).
2. Выявите причину деградации производительности.
3. Устраните проблему (созданием индекса, переписыванием SQL или сбором статистики).
4. Проверьте эквивалентность результата и ускорение с помощью утилиты python3 check.py <номер> <файл_решения.sql>.

Запрос Q1 (queries/q1.sql) — Отсутствующий индекс по внешнему ключу

• Сценарий: Получение 10 последних заказов конкретного пользователя с вычислением суммы позиций.
• Диагностика: Поиск клиента по email выполняется по уникальному индексу, но соединение с миллионной таблицей orders приводит к сканированию всей таблицы заказов (Seq Scan), так как внешний ключ orders.customer_id не проиндексирован.
• Решение: Создайте необходимый B-дерево индекс на столбце bookstore.orders (customer_id). Проверьте, изменился ли план на Index Scan или Bitmap Heap Scan.

Запрос Q2 (queries/q2.sql) — Функция от индексированного столбца (SARGability)

• Сценарий: Подсчёт заказов за определённую дату по складам.
• Условие: WHERE created_at::date = date '2024-03-01'.
• Диагностика: Несмотря на наличие индекса orders (created_at), приведение столбца к типу date делает предикат не sargable (search-argument-able). Оптимизатор вынужден читать всю таблицу последовательно.
• Решение: Перепишите условие в виде полуоткрытого диапазона created_at >= '2024-03-01' AND created_at < '2024-03-02'.

Запрос Q3 (queries/q3.sql) — Неявное приведение типов

• Сценарий: Поиск книги по значению ISBN.
• Условие: Столбец book.isbn имеет тип char(13) (bpchar), а параметр передаётся как text: WHERE isbn = '9780000054321'::text.
• Диагностика: Из-за различия типов PostgreSQL применяет функцию приведения к столбцу таблицы, отключая использование B-дерева по первичному/уникальному ключу.
• Решение: Обеспечьте точное соответствие типов: приведите текстовый литерал к типу bpchar (::bpchar), либо параметризуйте запрос корректным типом.

Запрос Q4 (queries/q4.sql) — Опасность конструкции NOT IN

• Сценарий: Поиск покупателей, которые не оставили ни одного отзыва.
• Условие: WHERE customer_id NOT IN (SELECT customer_id FROM bookstore.review).
• Диагностика: Из-за семантики трёхзначной логики SQL при наличии хотя бы одного NULL в подзапросе NOT IN возвращает пустое множество. Поэтому СУБД применяет алгоритм SubPlan с квадратичной сложностью O(N \cdot M).
• Решение: Перепишите запрос с использованием оператора NOT EXISTS (SELECT 1 FROM bookstore.review r WHERE r.customer_id = c.customer_id) или через антисоединение LEFT JOIN ... WHERE r.customer_id IS NULL.

Запрос Q5 (queries/q5.sql) — Коррелированные скалярные подзапросы в SELECT

• Сценарий: Список покупателей города с подсчётом количества отзывов и среднего рейтинга.
• Диагностика: Для каждой строки из внешнего запроса СУБД дважды сканирует таблицу отзывов review.
• Решение: Замените зависимые скалярные подзапросы на одно левое соединение LEFT JOIN bookstore.review с явной группировкой GROUP BY c.customer_id, c.full_name.

Запрос Q6 (queries/q6.sql) — Устаревшая статистика планировщика

• Сценарий: Агрегация журнала заказов lab11.order_log за последние месяцы.
• Диагностика: Таблица была недавно создана и массово заполнена скриптом setup.sql, но демон autovacuum ещё не собрал актуальную статистику (pg_class.reltuples = -1 или 0). Планировщик ошибочно полагает, что в таблице мало строк, и выбирает неэффективный план Nested Loop.
• Решение: Выполните команду сбора статистики ANALYZE lab11.order_log; и проверьте, как планировщик изменит план соединения на Hash Join.

Часть 6. Анализ рабочей нагрузки с pg_stat_statements и pgbench

В реальной эксплуатации медленные запросы выявляют не вручную, а по статистике их выполнения. Для этого применяется системный модуль pg_stat_statements.

1. Подключите расширение в базе bookstore50:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   

2. Сбросьте накопленную статистику:

   SELECT pg_stat_statements_reset();
   

3. Запустите 30-секундный профиль рабочей нагрузки с помощью скрипта run_workload.sh:

   ./run_workload.sh bookstore50 30
   

   Скрипт имитирует работу четырёх параллельных клиентов интернет-магазина (просмотр карточки книги, проверка остатков, чтение отзывов и просмотр заказов).
4. Проанализируйте собранную статистику. Выполните запрос для поиска пяти наиболее «прожорливых» запросов по общему времени выполнения:

   SELECT queryid,
          round(total_exec_time::numeric, 2) AS total_time_ms,
          calls,
          round(mean_exec_time::numeric, 2) AS mean_time_ms,
          round((100.0 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
          shared_blks_hit,
          shared_blks_read,
          substr(query, 1, 80) AS short_query
   FROM pg_stat_statements
   WHERE query NOT LIKE '%pg_stat_statements%'
   ORDER BY total_exec_time DESC
   LIMIT 5;
   

5. Найдите запрос, создающий наибольшую нагрузку на дисковую подсистему (максимальное shared_blks_read). Объясните, почему он читает блоки с диска, и предложите способ снижения нагрузки на дисковый ввод-вывод.

Журнал работы

По итогам выполнения работы подготовьте структурированный журнал, содержащий:

1. Вывод EXPLAIN (ANALYZE, BUFFERS) из части 1 с аннотацией: расшифруйте каждое поле корневого узла.
2. Сравнительную таблицу трёх методов соединений (Nested Loop, Merge Join, Hash Join) из части 2: время выполнения, расчётная стоимость, объём прочитанных буферов.
3. Демонстрацию сброса сортировки на диск в части 3 (Sort Method: external merge) и результат устранения сброса настройкой work_mem.
4. Сравнение оценки кардинальности до и после создания CREATE STATISTICS в части 4.
5. Итоговые результаты оптимизации запросов Q1–Q6:
   • текст оптимизированного запроса или DDL созданного индекса;
   • время выполнения до и после оптимизации;
   • подтверждение эквивалентности от скрипта check.py.
6. Вывод топ-5 медленных запросов из pg_stat_statements после нагрузки pgbench с комментариями о причинах их ресурсоёмкости.