13.3. Contrôler le planificateur avec des clauses 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 allons 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.

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 n'est pas nécessaire de contenir l'ordre de la jointure. Mais il est possible d'instruire le planificateur de requêtes de PostgreSQL pour traiter les JOIN internes explicites comme s'ils contraignaient 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 beaucoup de tables.

Pour forcer le planificateur à suivre l'ordre JOIN pour les jointures internes, 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 sinon. Dans cet exemple, le nombre d'ordres de jointures possibles est réduit par un facteur de 5.

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 leur requêtes parent. Par exemple, considérez 

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

Cette 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, demande 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 une 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 sur plus de from_collapse_limit éléments d'une FROM 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 aura aplatissement des jointures internes 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.