Qual è la differenza tra LATERAL e una sottoquery in PostgreSQL?

Da quando Postgres è uscito con la possibilità di fare join su LATERAL , ho letto su di esso, dato che al momento eseguo complessi dump di dati per il mio team con molte subquery inefficienti che rendono la query complessiva di quattro o più minuti.

Capisco che i join di LATERAL potrebbero essere in grado di aiutarmi, ma anche dopo aver letto articoli come questo di Heap Analytics, non lo seguo ancora.

Qual è il caso d’uso per un join LATERAL ? Qual è la differenza tra un join LATERAL e una sottoquery?

Più simile a una sottoquery correlata

Un join LATERAL (Postgres 9.3+) è più simile a una sottoquery correlata , non a una subquery semplice. Come sottolineato da @Andomar , una funzione o sottoquery a destra di un join LATERAL deve essere valutata molte volte, una volta per ogni riga a sinistra del join LATERAL , proprio come una sottoquery correlata , mentre una sottoquery (espressione di tabella) semplice è valutato solo una volta . (Tuttavia, il pianificatore di query ha modi per ottimizzare le prestazioni per entrambi).
Questa risposta correlata ha esempi di codice per entrambi, affiancati, che risolvono lo stesso problema:

  • Ottimizza la query GROUP BY per recuperare il record più recente per utente

Per restituire più di una colonna , un join LATERAL è in genere più semplice, più pulito e più veloce. Inoltre, ricorda che l’equivalente di una sottoquery correlata è LEFT JOIN LATERAL ... ON true :

  • Chiama una funzione set-return con un argomento array più volte

Leggi il manuale per LATERAL

È più autorevole di qualsiasi altra cosa metteremo qui in risposta:

Cose che una sottoquery non può fare

Ci sono cose che un join LATERAL può fare, ma una subquery (correlata) non può (facilmente). Una sottoquery correlata può restituire solo un singolo valore, non più colonne e non più righe, ad eccezione delle chiamate a funzioni nude (che moltiplicano le righe di risultato se restituiscono più righe). Ma anche alcune funzioni di invio dei set sono consentite solo nella clausola FROM . Come il nuovo unnest() con più parametri in Postgres 9.4. Il manuale:

Questo è consentito solo nella clausola FROM ;

Quindi funziona, ma non può essere facilmente sostituito con una sottoquery:

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

(La virgola ( , ) nella clausola FROM è una notazione breve per CROSS JOIN .
LATERAL viene assunto automaticamente per le funzioni della tabella.)

Ulteriori informazioni sul caso speciale di UNNEST( array_expression [, ... ] ) sotto questa domanda successiva su dba.SE:

  • Come si dichiara che una funzione set-return è consentita solo nella clausola FROM?

Impostare le funzioni di restituzione nell’elenco SELECT

È inoltre ansible utilizzare direttamente le funzioni di set- unnest() come unnest() nell’elenco SELECT . Questo mostrava un comportamento sorprendente con più di una istanza nella stessa lista SELECT fino a Postgres 9.6. Ma è stato finalmente sterilizzato con Postgres 10 ed è ora un’alternativa valida (anche se non è SQL standard).
Basandosi sull’esempio sopra:

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

Confronto:

dbfiddle per pg 9.6 qui
dbfiddle per pg 10 qui

Chiarire la disinformazione

Il manuale chiarisce le informazioni fuorvianti qui:

Per i tipi di join INNER e OUTER , è necessario specificare una condizione di join, precisamente una di NATURAL , ON join_condition o USING ( join_column [, …]). Vedi sotto per il significato.
Per CROSS JOIN , nessuna di queste clausole può apparire.

Quindi queste due domande sono valide (anche se non particolarmente utili):

 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; 

Mentre questo non lo è:

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

Ecco perché l’ esempio di codice di @ Andomar è corretto (il CROSS JOIN non richiede una condizione di join) e @ Attila non è valido.

La differenza tra un join non lateral e uno lateral consiste nel se si può guardare alla riga del tavolo della mano sinistra. Per esempio:

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

Questo “aspetto esteriore” significa che la sottoquery deve essere valutata più di una volta. Dopotutto, t1.col1 può assumere molti valori.

Al contrario, la subquery dopo un join non lateral può essere valutata una volta:

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

Come richiesto senza lateral , la query interna non dipende in alcun modo dalla query esterna. Una query lateral è un esempio di una query correlated , a causa della sua relazione con le righe all’esterno della query stessa.

Primo, Laterale e Croce Applicare è la stessa cosa . Pertanto si può anche leggere su Cross Apply. Dal momento che è stato implementato in SQL Server per anni, troverai ulteriori informazioni su di esso, quindi Laterale.

Secondo, secondo la mia comprensione , non c’è nulla che tu non possa fare usando la subquery invece di usare il laterale. Ma:

Considera la seguente query.

 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 

Puoi usare laterale in questa condizione.

 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 

In questa query non è ansible utilizzare il join normale, a causa della clausola limite. Laterale o Cross Apply può essere usato quando non c’è una condizione di join semplice .

Ci sono altri usi per applicazione laterale o incrociata, ma questo è il più comune che ho trovato.