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

Ponieważ Postgres wyszedł z możliwością łączenia LATERAL, czytałem o tym, ponieważ obecnie robię złożone zrzuty danych dla mojego zespołu z wieloma nieefektywnymi zapytaniami podrzędnymi, które sprawiają, że ogólne zapytanie zajmuje cztery minuty lub więcej.

Rozumiem, że LATERAL może mi pomóc, ale nawet po przeczytaniu artykułów takich jak Ten {[8] } z Heap Analytics nadal nie nadążam.

Jaki jest przypadek użycia LATERAL join? Jaka jest różnica między LATERAL join i subquery?

Author: Erwin Brandstetter, 2015-02-17
Source

4 answers

Bardziej jak skorelowane subquery

A LATERALjoin (Postgres 9.3+) jest bardziej jak skorelowane subquery , a nie zwykłe subquery. Podobnie jak @Andomar wskazał , funkcja lub subquery po prawej stronie LATERAL join zazwyczaj muszą być oceniane wiele razy-raz dla każdego wiersza po lewej stronie LATERALjoin - podobnie jak skorelowane subquery - podczas gdy zwykłe subquery (wyrażenie tabeli) są oceniane tylko raz . (Planista zapytań ma sposoby na zoptymalizuj wydajność dla każdego z nich.)
Ta powiązana odpowiedź ma przykłady kodu dla obu stron obok siebie, rozwiązując ten sam problem: [44]}

Dla zwracania więcej niż jednej kolumny , połączenie LATERAL jest zazwyczaj prostsze, czystsze i szybsze. Należy również pamiętać, że odpowiednikiem skorelowanego zapytania podrzędnego jest LEFT JOIN LATERAL ... ON true:

Przeczytaj instrukcję Dla on LATERAL

Jest to bardziej autorytatywne niż cokolwiek, co umieścimy w odpowiedziach tutaj:]}

Rzeczy, których subquery nie mogą zrobić

Istnieją rzeczy, które może zrobić LATERAL dołączyć, ale (skorelowane) subquery nie mogą (łatwo). Skorelowane zapytania podrzędne mogą zwracać tylko jedną wartość, a nie wiele kolumn i nie wiele wierszy - z wyjątkiem zwykłych wywołań funkcji (które mnożą wiersze wyników, jeśli zwracają wiele wierszy). Ale nawet niektóre funkcje zwracające zbiory są dozwolone tylko w klauzuli FROM. Jak nowy unnest() z wieloma parametrami w Postgres 9.4. Instrukcja:

Jest to dozwolone tylko w klauzuli FROM;

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

CREATE TABLE tbl (a1 int[], a2 int[]);

SELECT *
FROM   tbl t, unnest(t.a1, t.a2) u(elem1, elem2);  -- implicit LATERAL

(przecinek (,) w klauzuli FROM jest krótką notacją dla CROSS JOIN.
LATERAL jest zakładana automatycznie dla funkcji tabeli.)

Więcej o szczególnym przypadku UNNEST( array_expression [, ... ] ) pod tym późniejszym pytaniem na dba.SE:

Set-zwracanie funkcji na liście SELECT

Ty może również bezpośrednio używać funkcji zwracających set, takich jak unnest() z listy SELECT. Zwykle wykazywało to zaskakujące zachowanie z więcej niż jedną instancją na tej samej liście SELECT aż do Postgres 9.6. ale w końcu został oczyszczony z Postgres 10 i jest teraz ważną alternatywą (nawet jeśli nie standardowym SQL).
Bazując na powyższym przykładzie: 

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

Porównanie:

dbfiddle dla pg 9.6 tutaj
dbfiddle dla pg 10 tutaj

Wyjaśnij dezinformację

Podręcznik wyjaśnia mylące informacje tutaj:

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

Więc te dwa zapytania są ważne (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;

Podczas gdy 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 JOIN nie wymaga warunku join) i @ Attila is was invalid.

 87
Author: Erwin Brandstetter,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-11 01:47:00

Różnica między NIE-lateral a lateral 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ąd zewnętrzny " oznacza, że zapytanie podrzędne musi zostać ocenione więcej niż jeden raz. W końcu {[4] } może przyjąć wiele wartości.

Dla kontrastu, zapytanie podrzędne po NIE - lateral join może być ocenione raz: 

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

Jak jest wymagane bez lateral, zapytanie wewnętrzne nie zależy w żaden sposób od zapytania zewnętrznego. A lateral zapytanie jest przykład zapytania correlated, ze względu na jego relację z wierszami spoza samego zapytania.

 22
Author: Andomar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-17 08:41:10

Po pierwsze, Zastosowanie poprzeczne i krzyżowe to to samo . Dlatego możesz również przeczytać o Cross Apply. Ponieważ był on zaimplementowany w SQL serverze od wieków, znajdziesz na jego temat więcej informacji.

Po Drugie, zgodnie z moim zrozumieniem , nie ma nic, czego nie można zrobić używając subquery zamiast używać lateral. 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ć lateral 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żyć normalne połączenie, ze względu na klauzulę ograniczenia. Zastosowanie poprzeczne lub krzyżowe może być stosowane , gdy nie ma prostego warunku połączenia .

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

 7
Author: Atilla Ozgur,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:26:33

Jedną z rzeczy, na którą nikt nie zwrócił uwagi, jest to, że można użyć zapytań LATERAL do zastosowania funkcji zdefiniowanej przez użytkownika w każdym zaznaczonym 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);

To jedyny sposób, w jaki umiem robić tego typu rzeczy w PostgreSQL.

 0
Author: Theodore R. Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-10-05 18:12:32