Comment LATERAL JOIN de PostgreSQL peut-il optimiser plusieurs appels de fonctions sur les arguments du tableau ?

Optimisation des requêtes pour plusieurs appels de fonctions sur des arguments de tableau

Dans Postgres, il peut être inefficace d'appeler une fonction plusieurs fois sur un argument de tableau , surtout lorsque la fonction renvoie plusieurs colonnes. Explorons une stratégie d'optimisation des requêtes pour relever ce défi.

Considérons une fonction foo qui traite un tableau de lignes avec un paramètre donné et renvoie un ensemble de lignes plus une nouvelle colonne :

CREATE OR REPLACE FUNCTION foo(data data[], parameter int) RETURNS SETOF enhanceddata AS
...

Initialement, la fonction fonctionne sur un seul ensemble de données en utilisant :

SELECT * FROM foo( (SELECT ARRAY_AGG(data) FROM datatable GROUP BY dataid WHERE dataid = something), 1) 

Cependant, l'objectif est de la faire fonctionner pour plusieurs groupes de données sans en spécifiant un paramètre dataid.

Une approche consiste à utiliser une sous-requête pour agréger les données dans un tableau, puis à les transmettre à la fonction foo :

SELECT dataid, (foo(ARRAY_AGG(data)),1).*
FROM dataset
WHERE dataid = something -- only testing on 1
GROUP BY dataid

Bien que cela puisse sembler logique, cela souffre du problème de l'appel de foo plusieurs fois, une pour chaque ligne de données.

Utilisation de la jointure latérale pour Optimisation

Pour optimiser cette requête, le PostgreSQl LATERAL JOIN peut être utilisé. Cette technique puissante crée un produit cartésien basé sur les lignes entre les résultats d'une sous-requête et les lignes d'une autre table. Dans ce cas, la sous-requête agrège les données dans un tableau et la jointure latérale exécute foo une fois pour chaque ligne du tableau agrégé.

En utilisant PostgreSQL 9.3 ou version ultérieure, la requête suivante optimise les multiples appels de fonction :

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

LEFT JOIN LATERAL garantit que toutes les lignes de la table de gauche (sous-requête) sont préservées, même si aucune ligne sont renvoyés par foo. Si la fonction ne peut renvoyer aucune ligne, c'est la syntaxe préférée.

Pour les fonctions qui renvoient toujours un résultat, la syntaxe simplifiée peut être utilisée :

CROSS JOIN LATERAL foo(sub.arr)

ou son raccourci équivalent :

, foo(sub.arr)

Comme souligné dans le manuel de Postgres, l'utilisation de LATERAL JOIN optimise les requêtes avec des fonctions de retour d'ensemble comme foo. Cette technique traite efficacement plusieurs groupes de données sans entraîner d'évaluations de fonctions inutiles.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!