Руководство по созданию эффективных запросов

Листинг 7.1 

Неэффективное использование временных таблиц
CREATE TEMP TABLE flights_totals AS
SELECT bl.flight_id,
departure_airport,
(avg(price))::numeric(7,2) AS avg_price,
count(DISTINCT passenger_id) AS num_passengers
FROM booking b
JOIN booking_leg bl USING (booking_id)
JOIN flight f USING (flight_id)
JOIN passenger p USING (booking_id)
GROUP BY 1,2;
SELECT flight_id,
avg_price,
num_passengers
FROM flights_totals
WHERE departure_airport = 'ORD'

---

Временные таблицы и общие табличные выражения 

137
Создание временной таблицы заняло 15 минут и дало более 500 000 строк, 
из которых нам нужно всего 10 000. В то же время для выполнения запроса 
из лис тинга 6.15 потребовалось чуть больше минуты.
Общие табличные выражения (CTE)
Если временные таблицы могут так навредить, нельзя ли использовать вмес-
то них 
общие
табличные
выражения
(common table expression, CTE)? Разбе-
ремся для начала, что это такое.
Общие табличные выражения можно рассматривать как временные таблицы, существу-
ющие только для одного запроса. В предложении 
WITH
могут использоваться команды 
SELECT
, 
INSERT
, 
UPDATE
или 
DELETE
, а само предложение 
WITH
присоединяется к основному 
оператору, который также может быть командой 
SELECT
, 
INSERT
, 
UPDATE
или 
DELETE
.
Давайте попробуем применить CTE. В лис тинге 7.2 запрос из лис тинга 7.1 
изменен, чтобы использовать общее табличное выражение вместо времен-
ной таблицы.
Листинг 7.2 

Пример запроса с общим табличным выражением
WITH flights_totals AS (
SELECT bl.flight_id,
departure_airport,
(avg(price))::numeric(7,2) AS avg_price,
count(DISTINCT passenger_id) AS num_passengers
FROM booking b
JOIN booking_leg bl USING (booking_id)
JOIN flight f USING (flight_id)
JOIN passenger p USING (booking_id)
GROUP BY 1,2
)
SELECT flight_id,
avg_price,
num_passengers
FROM flights_totals
WHERE departure_airport = 'ORD'
То, что вы увидите в плане выполнения, зависит от того, какую версию 
PostgreSQL вы используете. Для всех версий ниже 12 общее табличное вы-
ражение обрабатывалось точно так же, как временная таблица. Результаты 
материализовались в основной памяти с возможным сбросом на диск. Это 
означает, что использование CTE не имело никаких преимуществ перед вре-
менной таблицей.
Вообще-то CTE предполагалось использовать для другого. Идея была в том, 
что если какой-то вложенный подзапрос используется в запросе несколько 
раз, то его можно определить как общее табличное выражение и ссылаться 

---

138

Длинные запросы: дополнительные приемы
на него столько раз, сколько потребуется. В этом случае PostgreSQL вычислит 
результаты только один раз и повторно использует их при повторных обра-
щениях.
Из-за такого предполагаемого применения оптимизатор планировал вы-
полнение CTE отдельно от остальной части запроса и не перемещал никакие 
условия соединения внутрь общего табличного выражения, образуя
оптими-
зации
. Это особенно важно, если 
WITH
используется в командах 
INSERT
, 
DELETE
или 
UPDATE
, которые могут иметь побочные эффекты, или в рекурсивных вы-
зовах CTE. Кроме того, наличие барьера оптимизации означает, что табли-
цы, участвующие в общем табличном выражении, не входят в ограничение, 
устанавливаемое параметром 
join_collapse_limit
. Таким образом, мы можем 
использовать оптимизатор PostgreSQL для запросов, соединяющих большое 
количество таблиц.
Для запроса из лис тинга 7.2 в версиях PostgreSQL до 12 общее табличное 
выражение 
flight_totals
будет рассчитано для всех рейсов, и только после 
этого будет выбрано подмножество рейсов.
PostgreSQL 12 радикально изменил оптимизацию общих табличных вы-
ражений. Если нерекурсивное CTE используется в запросе только один раз, 
то барьер оптимизации снимается и CTE встраивается во внешний запрос. 
Если CTE вызывается несколько раз, то сохраняется старое поведение.
Описанное поведение используется по умолчанию, но его можно изменить, 
используя ключевые слова 
MATERIALIZED
и 
NOT
MATERIALIZED
(см. лис тинг 7.3). 
Первое ключевое слово вызывает старое поведение, а второе – встраивание, 
независимо от всех других соображений.