Jaka jest różnica między LATERAL a podzapytaniem w PostgreSQL?

Odkąd Postgres wyszedł z możliwością wykonywania LATERALpołączeń, czytałem o tym, ponieważ obecnie wykonuję złożone zrzuty danych dla mojego zespołu z wieloma nieefektywnymi podzapytaniami, które sprawiają, że ogólne zapytanie zajmuje cztery minuty lub więcej.

Rozumiem, że LATERALłączenia mogą mi pomóc, ale nawet po przeczytaniu artykułów takich jak ten z Heap Analytics nadal nie do końca go śledzę.

Jaki jest przypadek użycia LATERALsprzężenia? Jaka jest różnica między LATERALzłączeniem a podzapytaniem?

Bardziej jak skorelowane podzapytanie

LATERALPrzyłączenia (Postgresa 9,3 lub później) jest większa jak skorelowanej podzapytaniu , a nie gładkie podzapytanie. Jak zauważył Andomar , funkcja lub podzapytanie po prawej stronie LATERALsprzężenia musi być ocenione raz dla każdego wiersza po lewej stronie - podobnie jak podzapytanie skorelowane - podczas gdy zwykłe podzapytanie (wyrażenie tabeli) jest oceniane tylko raz . (Planer zapytań ma jednak sposoby na optymalizację wydajności w obu przypadkach).
Ta pokrewna odpowiedź zawiera przykłady kodu dla obu stron, które rozwiązują ten sam problem:

• Zoptymalizuj zapytanie GROUP BY, aby pobrać najnowszy wiersz na użytkownika

Dla powracających więcej niż jedną kolumnę , o LATERALprzyłączenia jest zwykle prostsze, czystsze i szybsze.
Pamiętaj też, że odpowiednikiem skorelowanego podzapytania jest LEFT JOIN LATERAL ... ON true:

• Wywołaj wielokrotnie funkcję zwracającą zestaw z argumentem tablicy

Przeczytaj instrukcję dalej LATERAL

Jest bardziej autorytatywny niż cokolwiek, co zamieścimy tutaj w odpowiedziach:

• https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-LATERAL
• http://www.postgresql.org/docs/current/static/sql-select.html

Rzeczy, których podzapytanie nie może zrobić

Tam są rzeczy, które LATERALłączą może zrobić, ale (skorelowane) podzapytanie nie może (łatwo). Skorelowane podzapytanie może zwracać tylko jedną wartość, a nie wiele kolumn i wiele wierszy - z wyjątkiem samych wywołań funkcji (które mnożą wiersze wynikowe, jeśli zwracają wiele wierszy). Ale nawet niektóre funkcje zwracające zbiory są dozwolone tylko w FROMklauzuli. Podobnie jak w unnest()przypadku wielu parametrów w Postgres 9.4 lub nowszym. Instrukcja:

Jest to dozwolone tylko w FROMklauzuli;

Więc to działa, ale nie można go łatwo zastąpić podzapytaniem:

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

Przecinek ( ,) w FROMklauzuli jest krótką notacją dla CROSS JOIN.
LATERALjest przyjmowany automatycznie dla funkcji tabelarycznych.
Więcej o szczególnym przypadku UNNEST( array_expression [, ... ] ):

• Jak zadeklarować funkcję zwracającą zestaw, która ma być dozwolona tylko w klauzuli FROM?

Funkcje zwracające zestaw na SELECTliście

Możesz również bezpośrednio użyć funkcji zwracających zestaw, takich jak unnest()na SELECTliście. Kiedyś wykazywało zaskakujące zachowanie z więcej niż jedną taką funkcją na tej samej SELECTliście aż do Postgres 9.6. Ale w końcu został oczyszczony za pomocą Postgres 10 i jest teraz ważną alternatywą (nawet jeśli nie jest to standardowy SQL). Widzieć:

• Jakie jest oczekiwane zachowanie funkcji zwracających wiele zestawów w klauzuli SELECT?

Opierając się na powyższym przykładzie:

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

Porównanie:

dbfiddle dla strony 9.6 tutaj
dbfiddle dla strony 10 tutaj

Wyjaśnij błędne informacje

Instrukcja:

Dla INNERi OUTERłączą typów sprzężenia warunek musi być określony, a mianowicie dokładnie jedną NATURAL, ON join_condition lub USING( join_column [...]). Zobacz poniżej znaczenie.
Ponieważ CROSS JOINżadna z tych klauzul nie może się pojawić.

Więc te dwa zapytania są prawidłowe (nawet jeśli nie są szczególnie przydatne):

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;

Chociaż ten nie jest:

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

Dlatego przykład kodu @ Andomar jest poprawny ( CROSS JOINnie wymaga warunku łączenia), a kod @ Attila jest nieprawidłowy.

Różnica między non lateral-a lateraljoin polega na tym, czy możesz spojrzeć na wiersz tabeli po lewej stronie. Na przykład:

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

To „wyglądające na zewnątrz” oznacza, że ​​podzapytanie musi być ocenione więcej niż raz. Przecież t1.col1może przybierać wiele wartości.

W przeciwieństwie do tego podzapytanie po nierozłączeniu lateralmożna ocenić raz:

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

Jak jest to wymagane bez lateral, zapytanie wewnętrzne nie zależy w żaden sposób od zapytania zewnętrznego. lateralZapytanie jest przykładem correlatedzapytania, ze względu na jej związek z wierszy na zewnątrz samej zapytania.

Po pierwsze, zastosowanie boczne i krzyżowe to to samo . Dlatego możesz również przeczytać o Cross Apply. Ponieważ był on wdrażany w SQL Server od wieków, więcej informacji na jego temat znajdziesz w sekcji Lateral.

Po drugie, zgodnie z moim rozumieniem , nie ma niczego, czego nie można zrobić za pomocą podzapytania zamiast użycia lateralnego. Ale:

Rozważ następujące zapytanie.

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 

Możesz użyć bocznego w tym stanie.

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

W tym zapytaniu nie możesz używać zwykłego łączenia ze względu na klauzulę limit. Można zastosować nakładanie poprzeczne lub krzyżowe gdy nie ma prostego warunku łączenia .

Jest więcej zastosowań do nakładania bocznego lub krzyżowego, ale jest to najczęściej spotykane.

Jedną z rzeczy, na które nikt nie zwrócił uwagi, jest to, że możesz użyć LATERALzapytań do zastosowania funkcji zdefiniowanej przez użytkownika w każdym wybranym wierszu.

Na przykład:

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);

Tylko w ten sposób wiem, jak to zrobić w PostgreSQL.