Quelle est la différence entre un latéral et un subquery dans PostgreSQL?

depuis que Postgres est sorti avec la capacité de faire LATERAL joins, j'ai lu sur elle, depuis que je fais actuellement des dumps de données complexes pour mon équipe avec beaucoup de sous-séries inefficaces qui font la requête globale prend quatre minutes ou plus.

je comprends que LATERAL joins peut-être être en mesure de m'aider, mais même après avoir lu des articles comme celui-ci de Heap Analytics, Je ne suis toujours pas tout à fait.

qu'est-Ce que le cas d'utilisation pour un LATERAL rejoindre? Quelle est la différence entre une jointure LATERAL et une sous-query?

76
demandé sur Erwin Brandstetter 2015-02-17 00:29:08

4 réponses

plus comme un corrélé subquery

a LATERAL join (Postgres 9.3+) est plus comme un sous-query corrélé , pas un sous-query simple. Comme @Andomar a souligné , une fonction ou sous-requête à la droite d'une LATERAL jointure doit généralement être évaluée plusieurs fois - une fois pour chaque rangée à gauche de la LATERAL jointure - tout comme une corrélé sous-requête - alors qu'un simple sous-jeu (expression de table) est évalué une fois seulement. (Le planificateur de requête a des moyens pour optimiser les performances, soit, si.)

Cette réponse liée a des exemples de code pour les deux côte à côte, résoudre le même problème:

pour le retour plus d'un colonne , une jonction LATERAL est typiquement plus simple, plus propre et plus rapide. Aussi, rappelez - vous que l'équivalent d'une sous-quantité corrélée est LEFT JOIN LATERAL ... ON true :

lire le manuel pour LATERAL

il est plus autoritaire que tout ce que nous allons mettre dans des réponses ici:

les Choses d'une sous-requête ne peut pas le faire

là sont choses qu'une LATERAL peut faire, mais une sous-espèce (corrélée) ne peut pas (facilement.) Un sous-jeu corrélé ne peut retourner qu'une seule valeur, pas plusieurs colonnes et pas plusieurs lignes - à l'exception des appels de fonctions nues (qui multiplient les lignes de résultats s'ils renvoient plusieurs lignes). Mais même certaines fonctions de retour de jeu ne sont autorisées que dans la clause FROM . Comme le nouveau unnest() avec plusieurs paramètres dans Postgresql 9.4. Le manuel:

ce n'est autorisé que dans le FROM clause;

donc cela fonctionne, mais ne peut pas facilement être remplacé par un sous-article: 

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

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

(la virgule ( , ) dans la clause FROM est une notation courte pour CROSS JOIN .

LATERAL est automatiquement pris en compte pour les fonctions de table.)

pour en savoir plus sur le cas particulier de UNNEST( array_expression [, ... ] ) dans le cadre de cette question ultérieure sur dba.SE:

Set-de retour des fonctions dans le SELECT liste

vous pouvez également utiliser des fonctions de retour de set comme unnest() dans la liste SELECT directement. Ceci avait l'habitude de montrer un comportement surprenant avec plus d'une instance dans la même Liste SELECT jusqu'à Postgres 9.6. But il a finalement été nettoyé avec Postgres 10 et est une alternative valide maintenant (même si pas standard SQL).

En s'appuyant sur l'exemple ci-dessus: 

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

Comparaison:

dbfiddle pour pg 9.6 ici

dbfiddle pour pg 10 ici

clarifier désinformation

le manuel clarifie l'information trompeuse ici:

pour les types de jointure INNER et OUTER , une condition de jointure doit être spécifié, c'est-à-dire exactement l'un des NATURAL , ON join_condition , ou USING ( join_column [,...]). Voir ci-dessous pour le sens.

Pour CROSS JOIN , aucune de ces clauses ne peut apparaître.

ces deux requêtes sont donc valables (même si elles ne sont pas particulièrement utiles): 

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;

Tandis que celui-ci n'est pas: 

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

C'est pourquoi @Andomar exemple de code est correct (le CROSS JOIN ne nécessite pas une condition de jointure) et @Attila est était invalide.

87
répondu Erwin Brandstetter 2017-11-11 01:47:00

la différence entre un non - lateral et un lateral jointure se trouve dans Si vous pouvez regarder à la rangée de la table de gauche. Par exemple: 

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

Cet "extérieur" signifie que la sous-requête doit être évalué plus d'une fois. Après tout, t1.col1 peut supposer de nombreuses valeurs.

en revanche, le sous-jeu après une jointure non - lateral peut être évalué une fois: 

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

comme requis sans lateral , la requête interne ne dépendent en aucune manière de la requête externe. Une requête lateral est un exemple de requête correlated , en raison de sa relation avec des lignes en dehors de la requête elle-même.

22
répondu Andomar 2015-02-17 08:41:10

Première", 151930920" et transversales Appliquer est la même chose . Par conséquent vous pouvez également lire sur la Croix s'Appliquent. Depuis QU'il a été implémenté dans SQL Server for ages, vous trouverez plus d'informations à son sujet puis Lateral.

Second, selon ma compréhension , il n'y a rien que vous ne pouvez pas faire en utilisant subquery au lieu d'utiliser lateral. Mais:

envisager la requête suivante. 

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

vous pouvez utiliser latéral dans cette condition. 

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

dans cette requête, vous ne pouvez pas utiliser la jointure normale, en raison de la clause limit. Application latérale ou croisée peut être utilisé lorsqu'il n'y a pas de simple condition d'assemblage .

il y a plus d'usages pour appliquer latéralement ou transversalement, mais c'est le plus commun que j'ai trouvé.

7
répondu Atilla Ozgur 2017-05-23 12:26:33

Une chose que personne n'a souligné, c'est que vous pouvez utiliser LATERAL requêtes à appliquer une fonction définie par l'utilisateur sur chaque ligne sélectionnée.

par exemple: 

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

C'est le seul moyen que je connaisse pour faire ce genre de chose à PostgreSQL.

0
répondu Theodore R. Smith 2018-10-05 18:12:32