Documentation PostgreSQL 14.22 » Langage SQL » Conseils sur les performances » Contrôler le planificateur avec des clauses `JOIN` explicites

Précédent | Niveau supérieur | Suivant
14.2. Statistiques utilisées par le planificateur | Sommaire | 14.4. Remplir une base de données

## 14.3. Contrôler le planificateur avec des clauses `JOIN` explicites

Il est possible de contrôler le planificateur de requêtes jusqu'à un certain point en utilisant une syntaxe `JOIN` explicite. Pour voir en quoi ceci est important, nous avons besoin d'un peu de théorie.

Dans une simple requête de jointure, telle que :

    SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

le planificateur est libre de joindre dans n'importe quel ordre les tables indiquées. Par exemple, il peut générer un plan de requête joignant A à B par la condition `WHERE` `a.id = b.id`, puis joignant C à cette table jointe par l'autre condition `WHERE`. Ou il peut joindre B à C, puis le résultat avec A. Ou il peut joindre A à C, puis les joindre avec B, mais cela serait inefficace, puisque le produit cartésien complet de A et C devra être formé alors qu'il n'y a pas de condition applicable dans la clause `WHERE` pour optimiser cette jointure. (Toutes les jointures dans l'exécuteur PostgreSQL se produisent entre deux tables, il est donc nécessaire de construire le résultat de l'une ou de l'autre de ces façons). Le point important est que ces différentes possibilités de jointures donnent des résultats sémantiquement équivalents, mais peuvent avoir des coûts d'exécution extrêmement différents. Le planificateur va donc toutes les explorer pour trouver le plan de requête le plus efficace.

Quand une requête n'implique que deux ou trois tables, il y a peu d'ordres de jointures à considérer. Mais le nombre d'ordres de jointures possibles grandit de façon exponentielle quand le nombre de tables augmente. Au-delà d'environ dix tables en entrée, il n'est plus possible de faire une recherche exhaustive de toutes les possibilités, et même la planification de six ou sept tables peut prendre une durée gênante. Avec trop de tables en entrée, le planificateur PostgreSQL basculera d'une recherche exhaustive à une recherche génétique probabiliste depuis un nombre limité de possibilités (la limite de bascule est définie par le paramètre geqo_threshold). La recherche génétique prend moins de temps, mais elle ne trouvera pas nécessairement le meilleur plan possible.

Quand la requête implique des jointures externes, le planificateur est moins libre qu'avec des jointures internes. Par exemple, considérez :

    SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Bien qu'à première vue les conditions de cette requête semblent similaires à l'exemple précédent, les sémantiques sont différentes, car une ligne doit être émise pour chaque ligne de A qui sans ligne correspondante dans la jointure entre B et C. Le planificateur n'a alors pas le choix dans l'ordre de la jointure : ici, il doit joindre B à C, puis joindre A au résultat. En conséquence, cette requête prend moins de temps à planifier que la requête précédente. Dans d'autres cas, le planificateur peut arriver à déterminer que plus d'un ordre de jointure est sûr. Par exemple, étant donné :

    SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);

il est valide de joindre A en premier soit à B, soit à C. Actuellement, seul un `FULL JOIN` contraint complètement l'ordre de jointure. En pratique, la plupart des cas impliquant un `LEFT JOIN` ou un `RIGHT JOIN` peuvent être réarrangés jusqu'à un certain degré.

Sémantiquement, la syntaxe d'une jointure interne explicite (`INNER JOIN`, `CROSS JOIN` ou `JOIN`) revient à lister les relations en entrée du `FROM`, donc sans contraindre l'ordre de la jointure.

Même si la plupart des types de `JOIN` ne sont pas complètement contraignantes pour l'ordre de jointure, il est possible de forcer le planificateur de requête de PostgreSQL de les considérer comme contraignantes. Par exemple, ces trois requêtes sont logiquement équivalentes :

    SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
    SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
    SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Mais si nous disons au planificateur d'honorer l'ordre des `JOIN`, la deuxième et la troisième prendront moins de temps à planifier que la première. Cet effet n'est pas inquiétant pour seulement trois tables, mais cela pourrait bien nous aider avec un nombre important de tables.

Pour forcer le planificateur à suivre l'ordre de jointure des `JOIN` explicites, passez le paramètre join_collapse_limit à 1 (d'autres valeurs possibles sont discutées plus bas).

Vous n'avez pas besoin de restreindre l'ordre de jointure pour diminuer le temps de recherche car il est correct d'utiliser des opérateurs `JOIN` parmi les éléments d'une liste `FROM`. Par exemple, considérez :

    SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;

Avec `join_collapse_limit` = 1, le planificateur est forcé de joindre A à B avant la jointure aux autres tables, mais sans restreindre ses choix par ailleurs. Dans cet exemple, le nombre d'ordres de jointures possibles est réduit par un facteur de cinq.

Cette technique pour restreindre la recherche du planificateur peut servir à réduire les temps de planification et aiguiller le planificateur vers un bon plan de requête. Si le planificateur choisit spontanément un mauvais ordre de jointure, vous pouvez le forcer à choisir un meilleur via la syntaxe `JOIN` -- à supposer que vous connaissiez un meilleur ordre. Il faut tester.

Un problème très proche et affectant le temps de planification est le regroupement de sous-requêtes dans leurs requêtes parentes. Par exemple, considérez :

    SELECT *
    FROM x, y,
        (SELECT * FROM a, b, c WHERE quelquechose) AS ss
    WHERE quelquechosedautre;

Cette situation peut apparaître lors de l'utilisation d'une vue contenant une jointure ; la règle `SELECT` de la vue sera insérée à la place de la référence de la vue, produisant une requête comme celle ci-dessus. Normalement, le planificateur essaiera de regrouper la sous-requête avec son parent, donnant :

    SELECT * FROM x, y, a, b, c WHERE quelquechose AND quelquechosedautre;

Ceci donne généralement un meilleur plan que planifier séparément la sous-requête. (Par exemple, grâce aux clauses `WHERE` externes, joindre X à A peut éliminer d'entrée un bon nombre de lignes de A, évitant ainsi le besoin de générer la totalité de la sous-requête). Mais en même temps, nous avons accru le temps de planification ; ici, nous avons un problème de jointure à cinq tables remplaçant un problème de deux jointures séparées à trois tables. À cause de l'augmentation exponentielle du nombre de possibilités, ceci fait une grande différence. Le planificateur essaie d'éviter de se retrouver coincé dans des problèmes de recherche de grosses jointures en ne regroupant pas une sous-requête si plus de `from_collapse_limit` éléments sont la résultante de la requête parent. Vous pouvez arbitrer entre le temps de planification et la qualité du plan en ajustant ce paramètre à la hausse ou à la baisse.

from_collapse_limit et join_collapse_limit sont nommés de façon similaire car ils font pratiquement la même chose : le premier contrôle le moment où le planificateur « aplatira » les sous-requêtes, et le second contrôle quand aplatir les jointures explicites. Typiquement, vous définirez `join_collapse_limit` à la même valeur que `from_collapse_limit` (de façon à ce que les jointures explicites et les sous-requêtes agissent de la même façon) ou vous affecterez `join_collapse_limit` à 1 (si vous voulez contrôler l'ordre de jointure par des jointures explicites). Mais vous pouvez les définir différemment en essayant de configurer finement la relation entre le temps de planification et le temps d'exécution.