Discussion :

[sihem_info]

bonjour a tous

Question pour nos experts

Quel est la différence entre l'opération Hash join et nestead loop dans un plan d’exécution

aurai t'il un changement total du mon plan d’exécution entre ces deux opérations si mes statistiques sont obsolètes

faut t'il toujours lancer un vacuum analyse pour que j'aurai toujours la belle opération du jointure

merci pour l'explication du nos experts

[SQLpro]

Il existe de nombreux algorithme de jointure et les deux que vous indiquez ne sont pas les seuls.

• Pour le cas de la jointure par hachage, il s'agit de transformer les clefs de jointures de part et d'autre de l'opérateur en nombre entier par le biais d'une fonction de hachage, puis de classer ces clefs dans des "hash buckets" (sorte d'histogramme) afin d'assurer la correspondance des lignes sur un plus petit ensemble de données et de manière plus rapide.
• Pour le cas de la boucle imbriquée, il s'agit tout simplement d'itérer sur les lignes de la première table, puis pour chaque ligne de la première sur toutes les lignes de la seconde.
• Enfin, il existe le "merge join" qui est un tri fusion : on tri les clefs de part et d'autres de la jointure, puis on "descent" dans les données de part et d'autre, un peu à la manière d'une échelle de perroquet.

Notez qu'il en existe d'autres plus exotiques que certains SGBDR utilisent (pas PostGreSQL...)

D'un point de vue algorithmique, le coût de ces différentes méthode est la suivante (m et n étant les cardinalités des deux tables ):

• Pour le MERGE JOIN : cout des tris + m + n
• Pour le LOOP JOIN : m x n
• Pour le HASH JOIN : cout du calcul des hachage + cout intermédiaire entre m + n et m x n

Quel est alors la jointure gagnante ?
S'il existe des index sur les clefs, c'est toujours le MERGE JOIN qui devient le moins couteux, car il se résume à un cout de m + n. Dans le cas contraire, s'il existe un index sur l'une des clefs, alors l'optimiseur étudie le coût du tri de l'autre + la jointure MERGE et la compare aux autres techniques.
S'il existe très peu de ligne dans une des tables (par exemple 1 seule ligne) alors le LOOP JOIN est avantageux.
Dans tous les autres cas et en particulier si les clefs de jointures sont "longues" (littéraux ou multi colonne) alors c'est souvent la technique du HACHAGE qui s'avère la moins couteuse.

Bien évidemment, plus les statistiques sont précise et plus précis sera l'estimation des couts et donc le choix optimal effectué par l'optimiseur !

A +

[SQLpro]

Dans votre cas il est à notez que vous utilisez une fonction.. C'est très mal ! En effet une fonction n'étant par nature pas ensembliste, mais itérative et dynamique au niveau de ses données (pas statique comme ce serait le cas d'une table) aucune statistique n'est disponible pour l'optimiseur et il n'existe pas d'index. Le cout est alors établis par défaut à 100 dans PostGreSQL et conduit assez systématiquement à un LOOP JOIN ou si les clef sont très lourde parfois à un hachage.
Enfin, si ce chiffre est proche de la réalité, vous êtes gagnant. S'il est très éloigné, vous êtes dans la M....
Seule solution : transformer votre fonction table en vue SQL ou en intégrer le SQL directement dans la requête finale....

A +