В чем разница между LATERAL и подзапросом в PostgreSQL?

Так как Postgres вышел с возможностью делать LATERAL joins, я читал на нем, так как в настоящее время я делаю сложные дампы данных для своей команды с множеством неэффективных подзапросов, которые заставляют общий запрос принимать четыре минуты или больше.

Я понимаю, что объединения LATERAL могут мне помочь, но даже после чтения статей, таких как этот из Heap Analytics, я до сих пор не совсем понимаю.

Каков прецедент для соединения LATERAL? В чем разница между соединением LATERAL и подзапросом?

Ответы

Ответ 1

Больше похоже на коррелированный подзапрос

LATERAL соединение (Postgres 9.3 или новее) больше похоже на коррелированный подзапрос, а не простой подзапрос. Как указывал Андомар, функция или подзапрос справа от LATERAL соединения должен оцениваться один раз для каждой строки слева от него - как коррелированный подзапрос - в то время как простой подзапрос (табличное выражение) оценивается только один раз. (Однако у планировщика запросов есть способы оптимизировать производительность для любого из них.)
У этого связанного ответа есть примеры кода для обеих сторон, решая ту же самую проблему:

Оптимизировать запрос GROUP BY для получения последней строки для пользователя

Для возврата более одного столбца LATERAL соединение обычно проще, чище и быстрее.
Кроме того, помните, что эквивалентом коррелированного подзапроса является LEFT JOIN LATERAL... ON true:

Вызов функции, возвращающей множество, с аргументом массива несколько раз

Прочтите руководство по `LATERAL`

Это более авторитетно, чем все, что мы собираемся вставить в ответы здесь:

То, что подзапрос не может сделать

Есть вещи, которые может сделать LATERAL соединение, но (коррелированный) подзапрос не может (легко). Коррелированный подзапрос может возвращать только одно значение, а не несколько столбцов и не несколько строк, за исключением простых вызовов функций (которые умножают результирующие строки, если они возвращают несколько строк). Но даже некоторые возвращающие множество функции разрешены только в предложении FROM. Как и unnest() с несколькими параметрами в Postgres 9.4 или новее. Руководство:

Это разрешено только в предложении FROM;

Так что это работает, но не может быть легко заменено подзапросом:

CREATE TABLE tbl (a1 int[], a2 int[]);
SELECT * FROM tbl, unnest(a1, a2) u(elem1, elem2);  -- implicit LATERAL

Запятая (,) в предложении FROM является кратким обозначением для CROSS JOIN.
LATERAL предполагается автоматически для табличных функций.
Подробнее о специальном случае UNNEST( array_expression [,... ] ):

Как вы объявляете функцию, возвращающую множество, разрешенную только в предложении FROM?

Набор возвращающих функций в списке `SELECT`

Вы также можете использовать функции, возвращающие множество, например, unnest() в списке SELECT. Раньше это демонстрировало удивительное поведение с более чем одной такой функцией в одном списке SELECT до Postgres 9.6. Но он, наконец, был продезинфицирован с помощью Postgres 10 и теперь является допустимой альтернативой (даже если не стандартным SQL). Увидеть:

Каково ожидаемое поведение для нескольких функций, возвращающих множество в предложении SELECT?

Опираясь на приведенный выше пример:

SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2
FROM   tbl;

Сравнение:

dbfiddle для pg 9.6 здесь
dbfiddle для pg 10 здесь

Уточнить дезинформацию

Руководство:

Для типов соединения INNER и OUTER должно быть указано условие соединения, а именно одно из следующих: NATURAL, ON join_condition или USING (join_column [,...]). Смотрите ниже значение.
Для CROSS JOIN ни одно из этих предложений не может появиться.

Таким образом, эти два запроса действительны (даже если они не особенно полезны):

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON TRUE;

SELECT *
FROM   tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

Пока этого нет

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

Вот почему пример кода @Andomar является правильным (для CROSS JOIN не требуется условие соединения), а @Attila ~~является~~ недействительным.

Ответ 2

Разница между соединением non lateral и lateral заключается в том, можете ли вы смотреть в строку таблицы левой руки. Например:

select  *
from    table1 t1
cross join lateral
        (
        select  *
        from    t2
        where   t1.col1 = t2.col1 -- Only allowed because of lateral
        ) sub

Этот "внешний вид" означает, что подзапрос должен оцениваться более одного раза. В конце концов, t1.col1 может принимать множество значений.

В отличие от этого подзапрос после объединения lateral может быть оценен один раз:

select  *
from    table1 t1
cross join
        (
        select  *
        from    t2
        where   t2.col1 = 42 -- No reference to outer query
        ) sub

Как требуется без lateral, внутренний запрос никоим образом не зависит от внешнего запроса. Запрос lateral является примером запроса correlated из-за его связи с строками вне самого запроса.

Ответ 3

Во-первых, боковое и поперечное применение - это одно и то же. Поэтому вы также можете прочитать о Cross Apply. Поскольку он был внедрен в SQL Server целую вечность, вы найдете больше информации о нем, чем Lateral.

Во-вторых, согласно моему пониманию, вы ничего не можете сделать, используя подзапрос вместо использования бокового. Но:

Рассмотрим следующий запрос.

Select A.*
, (Select B.Column1 from B where B.Fk1 = A.PK and Limit 1)
, (Select B.Column2 from B where B.Fk1 = A.PK and Limit 1)
FROM A

Вы можете использовать боковой в этом состоянии.

Select A.*
, x.Column1
, x.Column2
FROM A LEFT JOIN LATERAL (
  Select B.Column1,B.Column2,B.Fk1 from B  Limit 1
) x ON X.Fk1 = A.PK

В этом запросе вы не можете использовать обычное объединение из-за предложения limit. Боковое или поперечное применение может использоваться, когда нет простого условия соединения.

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

Ответ 4

Одна вещь, на которую никто не указал, это то, что вы можете использовать LATERAL запросы, чтобы применить пользовательскую функцию к каждой выбранной строке.

Например:

CREATE OR REPLACE FUNCTION delete_company(companyId varchar(255))
RETURNS void AS $$
    BEGIN
        DELETE FROM company_settings WHERE "company_id"=company_id;
        DELETE FROM users WHERE "company_id"=companyId;
        DELETE FROM companies WHERE id=companyId;
    END; 
$$ LANGUAGE plpgsql;

SELECT * FROM (
    SELECT id, name, created_at FROM companies WHERE created_at < '2018-01-01'
) c, LATERAL delete_company(c.id);

Это единственный способ, которым я знаю, как делать такие вещи в PostgreSQL.