JOIN
explicites
Il est possible de contrôler le planificateur de requêtes à un certain
point en utilisant une syntaxe JOIN
explicite. Pour voir en
quoi ceci est important, nous avons besoin de quelques connaissances.
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 les tables données dans n'importe
quel ordre. Par exemple, il pourrait générer un plan de requête qui joint A à
B en utilisant la condition WHERE
a.id = b.id
, puis
joint C à cette nouvelle table jointe en utilisant l'autre condition
WHERE
. Ou il pourrait joindre B à C, puis A au résultat de cette
jointure précédente. Ou il pourrait joindre A à C, puis les joindre avec B,
mais cela pourrait ne pas être efficace, car 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 permettre une optimisation de la jointure
(toutes les jointures dans l'exécuteur PostgreSQL
arrivent entre deux tables en entrées, donc il est 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 pourraient avoir des coûts d'exécution
grandement différents. Du coup, le planificateur va toutes les explorer pour
trouver le plan de requête le plus efficace.
Quand une requête implique seulement deux ou trois tables, il y a peu d'ordres de jointures à préparer. Mais le nombre d'ordres de jointures possibles grandit de façon exponentielle au fur et à mesure que le nombre de tables augmente. Au-delà de 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 pourrait prendre beaucoup de temps. Quand il y a trop de tables en entrée, le planificateur PostgreSQL basculera d'une recherche exhaustive à une recherche génétique probabiliste via un nombre limité de possibilités (la limite de bascule est initialisée par le paramètre en exécution 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'il ne l'est lors de 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 que les restrictions de cette requête semblent superficiellement similaires à l'exemple précédent, les sémantiques sont différentes, car une ligne doit être émise pour chaque ligne de A qui n'a pas de ligne correspondante dans la jointure entre B et C. Du coup, le planificateur n'a pas de choix dans l'ordre de la jointure ici : il doit joindre B à C puis joindre A à ce résultat. Du coup, cette requête prend moins de temps à planifier que la requête précédente. Dans d'autres cas, le planificateur pourrait être capable de 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 à soit B soit C en premier. Actuellement, seul un
FULL JOIN
contraint complètement l'ordre de jointure. La
plupart des cas pratiques impliquant un LEFT JOIN
ou un
RIGHT JOIN
peuvent être arrangés jusqu'à un certain degré.
La syntaxe de jointure interne explicite (INNER
JOIN
, CROSS JOIN
ou JOIN
) est sémantiquement
identique à lister les relations en entrées du FROM
, donc il
ne contraint pas l'ordre de la jointure.
Même si la plupart des types de JOIN
ne contraignent pas
complètement l'ordre de jointure, il est possible d'instruire le planificateur
de requête de PostgreSQL pour qu'il traite toutes
les clauses JOIN
de façon à contraindre quand même l'ordre
de jointure.
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 demandé par les
JOIN
explicites, initialisez le paramètre en exécution
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 bien d'utiliser les opérateurs JOIN
dans 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, ceci force le planificateur à
joindre A à B avant de les joindre aux autres tables, mais sans restreindre
ses choix. Dans cet exemple, le nombre d'ordres de jointures possibles
est réduit par un facteur de cinq.
Restreindre la recherche du planificateur de cette façon est une technique
utile pour réduire les temps de planification et pour diriger le
planificateur vers un bon plan de requêtes. Si le planificateur choisit un
mauvais ordre de jointure par défaut, vous pouvez le forcer à choisir un
meilleur ordre via la syntaxe JOIN
-- en supposant que vous
connaissiez un meilleur ordre. Une expérimentation est recommandée.
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 requête pourrait survenir suite à 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 plutôt identique à 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 résulte habituellement en un meilleur plan que de planifier séparément
la sous-requête (par exemple, les conditions WHERE
externes
pourraient être telles que joindre X à A élimine en premier lieu un bon
nombre de lignes de A, évitant ainsi le besoin de former la sortie complète
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 comparer le temps de planification avec la qualité du plan en
ajustant ce paramètre en exécution.
from_collapse_limit et join_collapse_limit sont
nommés de façon similaire parce qu'ils font pratiquement la même chose :
l'un d'eux contrôle le moment où le planificateur « aplatira » les
sous-requêtes et l'autre contrôle s'il y a aplatissement des jointures
explicites. Typiquement, vous initialiserez
join_collapse_limit
comme 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 initialiserez join_collapse_limit
à 1 (si
vous voulez contrôler l'ordre de jointure des jointures explicites). Mais
vous pourriez les initialiser différemment si vous tentez de configurer
finement la relation entre le temps de planification et le temps
d'exécution.