Еще одна классная статья-перевод от Cybertec, на этот раз про подзапросы. Только примеры у них очень простые (хотя, наверное, в этом и суть). Но можно было бы и демобазу использовать 🙂
Ссылка на оригинал.
SQL позволяет нам использовать подзапросы почти везде, где нужно вернуть таблицу или столбец. Все, что нужно сделать — обернуть запрос в скобки, например (SELECT…), и можно использовать его в произвольных выражениях. Это делает SQL мощным языком, но читать такие конструкции будет сложновато. Но я не хочу обсуждать красоту или уродство SQL. В этой статье я хочу рассказать вам, как писать подзапросы, которые будут эффективно работать. Я начну с простых моментов, но позже перейду к более удивительным и сложным.
Коррелированные и некоррелированные подзапросы
В подзапросе вы можете использовать столбцы внешней таблицы, например:
SELECT a.col1,
(SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;
Подзапрос будет отличаться для каждого нового значения «a». Такой подзапрос обычно называют коррелированным подзапросом [ну т. е. связанным с внешним]. Некоррелированный подзапрос — это запрос, который не ссылается никуда во внешние таблицы.
Некоррелированные подзапросы просты. Если оптимизатор PostgreSQL не «прокинет» его в основной запрос (добавит в основной текст запроса), сервер будет оценивать подзапрос отдельно. Вы можете увидеть это как InitPlan (initial plan, первоначальный план) в EXPLAIN’e:
[От перводчика: Например, так:
EXPLAIN
SELECT a.col1
FROM a
WHERE a.x = (SELECT max(b.x) FROM b);
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on a (cost=38.26..76.51 rows=11 width=4)
Filter: (x = $0)
InitPlan 1 (returns $0) -- Это как раз и есть некоррелированный подзапрос
-> Aggregate (cost=38.25..38.26 rows=1 width=4)
-> Seq Scan on b (cost=0.00..32.60 rows=2260 width=4)
(5 rows)
]
Некоррелированные подзапросы почти никогда не вызывают проблем с производительностью. В оставшейся части статьи я, в основном, буду рассматривать коррелированные подзапросы.
Скалярные и табличные подзапросы
Если вы пишете подзапрос, который возвращает одно значение — это скалярный подзапрос. Пример скалярного подзапроса приведен в предыдущем разделе. Вот другой пример:
SELECT a.col1
FROM a
WHERE 1 = (SELECT count(*)
FROM b
WHERE b.x = a.x);
Если скалярный подзапрос ничего не возвращает, результирующее значение будет равно NULL. Если запрос возвращает более одной строки, вы получите ошибку:
ERROR: more than one row returned by a subquery used as an expression
Табличный подзапрос может возвращать более одного значения:
- Секция FROM: FROM (SELECT …) AS alias
- Общие табличное выражение (CTE): WITH q AS (SELECT …) SELECT …
- Выражение IN или NOT IN: WHERE a.x IN (SELECT …)
- Выражение EXISTS или NOT EXISTS: WHERE NOT EXISTS (SELECT …)
Скалярные подзапросы обычно являются проблемой производительности
Моё жизненное правило: избегайте коррелированных скалярных подзапросов, если это возможно. Потому что PostgreSQL может выполнять скалярный подзапрос только как вложенный цикл nested loop. Например, Postgres’у нужно выполнить подзапрос из первой части запроса один раз для каждой строки из таблицы «a». Всё будет нормально, если «а» — небольшая таблица (помните, моя рекомендация — это просто правило, добытое опытным путем). Однако если таблица «а» большая, даже быстрый подзапрос сделает выполнение всего запроса неприятно медленным.
Переписывание скалярного подзапроса в списке SELECT или предложении WHERE.
Если коррелированные скалярные подзапросы вредны для производительности, как их избежать? Не существует простого и однозначного ответа, и вы, вероятно, не сможете переписать запрос, чтобы избежать таких подзапросов во всех случаях. Но, обычно, решение состоит в том, чтобы преобразовать подзапрос в JOIN. Для нашего первого запроса это будет выглядеть так:
SELECT a.col1,
b.col2
FROM a
LEFT JOIN b ON b.x = a.x;
Этот запрос семантически эквивалентен, за исключением того, что мы не получаем ошибку, если строка в «a» соответствует более чем одной строке в «b». Нам нужно использовать внешнее соединение в случае, если подзапрос не возвращает результата.
Вот переписанный запрос для второго примера:
SELECT a.col1 FROM a JOIN b ON b.x = a.x GROUP BY a.pkey, a.col1 HAVING count(*) = 1;
Здесь a.pkey — это первичный ключ таблицы «a». Группировки по a.col1 будет недостаточно, поскольку две разные строки из таблицы «a» могут иметь одно и то же значение для col1.
Преимущество переписывания запросов, как показано выше, заключается в том, что PostgreSQL может выбрать оптимальную стратегию соединения и не ограничиваться вложенными циклами. Если в таблице «a» мало строк, это может не иметь значения, поскольку соединение вложенным циклом в любом случае может быть наиболее эффективной стратегией соединения. Но и в этом случае вы не прогадаете, переписав запрос. А если «a» велико, запрос будет работать намного быстрее с хэшем или соединением слиянием.
Табличные подзапросы и производительность
Коррелированные скалярные подзапросы обычно плохи, но с табличными подзапросами дело обстоит не так однозначно. Рассмотрим разные случаи отдельно.
CTE и подзапросы во FROM
Эти случаи очень похожи, поскольку вы всегда можете переписать CTE в подзапрос во FROM, если только это не рекурсивный CTE, MATERIALIZED или CTE, изменяющий данные. CTE никогда не могут быть коррелированными, поэтому они никогда не создают проблем. Однако строки из таблиц, перечисленных в предложении FROM могут быть связаны с подзапросом с помощью lateral join:
SELECT a.col1, sub.col2
FROM a
CROSS JOIN LATERAL
(SELECT b.col2
FROM b
WHERE b.x = a.x
ORDER BY b.sort
LIMIT 1) AS sub;
Опять же, PostgreSQL выполнит такой подзапрос используя вложенный цикл, что может плохо работать для большой таблицы «a». Поэтому обычно рекомендуется переписать запрос, чтобы избежать коррелированного подзапроса:
SELECT DISTINCT ON (a.pkey)
a.col1, b.col2
FROM a
JOIN b ON b.x = a.x
ORDER BY a.pkey, b.sort;
Переписанный запрос будет работать лучше, если «a» имеет много строк, но он может работать хуже, если «a» маленькое, а «b» большое, но имеет индекс (x, sort).
Подзапросы в EXISTS и NOT EXISTS
Это особый случай. До сих пор я всегда рекомендовал избегать коррелированных подзапросов. Но с помощью EXISTS и NOT EXISTS оптимизатор PostgreSQL может преобразовать предложение в полусоединение (semi-join) и антиобъединение (anti-join) соответственно. Это позволяет PostgreSQL использовать все стратегии соединения, а не только вложенные циклы.
Следовательно, PostgreSQL может эффективно обрабатывать коррелированные подзапросы в EXISTS и NOT EXISTS.
Сложный случай в IN и NOT IN
Возможно, вы ожидаете, что эти два случая будут вести себя одинаково, но это не так. Запрос, использующий IN с подзапросом, всегда можно переписать с помощью EXISTS. Например:
SELECT a.col1
FROM a
WHERE a.foo IN (SELECT b.col2
FROM b
WHERE a.x = b.x);
эквивалентно
SELECT a.col1
FROM a
WHERE EXISTS (SELECT NULL
FROM b
WHERE a.x = b.x
AND a.foo = b.col2);
Оптимизатор PostgreSQL может это сделать и обработать подзапрос в IN так же эффективно, как и в EXISTS.
Однако случай с NOT IN немного другой. Вы можете переписать NOT IN на NOT EXISTS аналогично приведенному выше, но это не то преобразование, которое PostgreSQL cможет выполнить автоматически, поскольку переписанный оператор семантически отличается: если подзапрос возвращает хотя бы одно значение NULL, NOT IN никогда не будет иметь значение TRUE. Предложение NOT EXISTS не ведёт себя так удивительно.
Сейчас людей обычно не волнует это свойство NOT IN (и на самом деле о нем мало кто знает [кхм]). Большинство людей в любом случае предпочли бы поведение NOT EXISTS. Но вам придется переписать оператор SQL самостоятельно, и вы не ожидайте, что PostgreSQL сделает это автоматически. Поэтому я рекомендую никогда не использовать NOT IN с подзапросом, а вместо этого всегда использовать NOT EXISTS.
Использование коррелированных подзапросов для принудительного использования соединения вложенным циклом
До сих пор я рассказывал вам, как переписать оператор SQL, чтобы оптимизатор PostgreSQL использовал не только вложенный цикл. Однако иногда вам нужно прямо противоположное: вы хотите, чтобы оптимизатор использовал соединение с вложенным циклом, потому что вы знаете, что это лучшая стратегия соединения. Поэтому вы можете намеренно переписать обычный join на lateral cross join, чтобы явно использовать вложенный цикл. Посмотрим на этот запрос:
SELECT a.col1, b.col2 FROM a JOIN b ON a.x = b.x;
семантически эквивалентен
SELECT a.col1, sub.col2
FROM a
CROSS JOIN LATERAL
(SELECT b.col2
FROM b
WHERE a.x = b.x) AS sub;
Вывод
Если вам нужна хорошая производительность с подзапросами, полезно следовать этим рекомендациям:
- используйте некоррелированные подзапросы столько, сколько захотите, если они не усложняют понимание запроса;
- избегайте коррелированных подзапросов везде, кроме предложений EXISTS, NOT EXISTS и IN;
- всегда переписывайте NOT IN на NOT EXISTS.
Не воспринимайте эти правила как непреложную истину. Иногда коррелированный подзапрос действительно может работать лучше, а иногда вы можете использовать коррелированный подзапрос, чтобы заставить оптимизатор использовать вложенный цикл, если вы уверены, что так и нужно.
Если вы заинтересованы в повышении производительности запроса путем его переписывания, возможно, вы захотите прочитать мою статью о принудительном порядке соединения в PostgreSQL.
Лоренц Альбе — старший консультант и инженер службы поддержки CYBERTEC. Он работает с PostgreSQL и вносит свой вклад в развитие PostgreSQL с 2006 года, писал патчи для ядра и написал oracle_fdw. Он имеет степень магистра математики Венского университета и степень магистра компьютерных наук Венского технического университета. В свободное время он любит читать своим детям и размышлять о корнях языка.
Еще раз ссылка на оригинал.
Leave a Reply