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

Question

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

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

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

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

317

sql postgresql subquery lateral

Источник

user1351335 16 фев '15 в 21:29

5 ответов

Решение

Разница между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 запрос, из-за его связи со строками вне самого запроса.

137

Источник

user50552 16 фев '15 в 22:16

Таблица базы данных

Имея следующую таблицу базы данных, в которой хранятся блоги, размещенные на нашей платформе:

И в настоящее время у нас есть два блога:

Намного лучше, правда?

В age_in_years рассчитывается для каждой записи blogстол. Таким образом, он работает как коррелированный подзапрос, но записи подзапроса объединяются с основной таблицей, и по этой причине мы можем ссылаться на столбцы, созданные подзапросом.

50

Источник

user1025118 22 янв '21 в 18:01

Одна вещь, которую никто не указал, это то, что вы можете использовать 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.

23

Источник

user430062 05 окт '18 в 18:12

Во-первых, боковое и поперечное применение - это одно и то же. Поэтому вы также можете прочитать о 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. Боковое или поперечное применение может использоваться, когда нет простого условия соединения.

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

22

Источник

user41782 16 фев '15 в 21:51

Другие вопросы по тегам sql postgresql subquery lateral

user939860 17 фев '15 в 08:40 2015-02-17 08:40 · Accepted Answer · 2015-02-17 08:40

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

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

Оптимизировать запрос 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 t, unnest(t.a1, t.a2) u(elem1, elem2);  -- implicit LATERAL

^{(Запятая (} , ^{) в} FROM ^{предложение является кратким обозначением} CROSS JOIN ^, LATERAL ^{предполагается автоматически для табличных функций.)}

Подробнее о частном случае UNNEST( array_expression [, ... ] ) под этим последующим вопросом на dba.SE:

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

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

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

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

Сравнение:

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

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

Руководство разъясняет вводящую в заблуждение информацию здесь:

Для INNER а также OUTER типы соединения, должно быть указано условие соединения, а именно одно из NATURAL, ON условие соединения, или 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's недействительно.

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

5 ответов

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

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

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

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

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

Таблица базы данных

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

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