Ordre des jointures et performances

HadanMarv · 02/12/2011 11:39:12

Bonjour,

Nous avons depuis peu effectué une migration d'une base DB2 vers une base postgres.
Dans l'ensemble nous n'avons pas rencontré de grosses difficultés.
Cependant sur certaines extractions, nous avons pu constater que l'ordre des jointures avait un impact considérable.
La requête en question s'exécute en 10 secondes sur DB2.
La même requête, sur postgres calcul le plan d'exécution en 2 secondes, mais ne retourne jamais rien (même en exécutant la requête directement sur la base), kill du process après 35 minutes d'exécution...
En simplement changeant l'ordre d'une des jointures j'arrive à un résultat au bout de 2 secondes !!!!!!
Comment est-ce possible sachant que dans les deux cas le plan d'exécution était retourné rapidement ?

D'avance merci de vos lumières.
HadanMarv

rjuju · 02/12/2011 11:56:50

Bonjour.

Le plan d'exécution postgresql est fait pour ne jamais prendre beaucoup de temps, et c'est pourquoi certaines mécaniques et restrictions ont été mises en place pour pouvoir un peu influer dessus.
Le calcul du plan part du principe que la personne qui fait la requête essaye de mettre ses join pour limiter au maximum et au plus vite le nombre de lignes,j et dans la configuration par défaut postgresql ne tente de changer l'ordre des join qu'à partir de 8 join.

Si toutefois pour des raisons de facilité d'écriture ou autre ce n'est pas le cas, vous pouvez utiliser la variable "join_collapse_limit" afin de descendre cette limite, la modification de variable pouvant s'effectuer sur l'instance (plutôt déconseillé) ou sur la session en cours.

Ainsi, si vous faites précéder votre requête d'un "set join_collapse_limit to 5;" (la valeur sera sans doute à affiner selon votre nombre de jointures) vous pourrez sans doute garder la même requête et retrouver un temps d'exécution raisonnable. Vous pouvez également analyser les explain des requêtes en changeant l'ordre des join pour voir si d'autres problèmes ne sont pas présents (statistiques non à jour, index pertinent non présent ...)

Bien sur, descendre cette limite systématiquement ferait ralentir la plupart des autres requêtes en forçant postgresql à calculer plus de plans d'exécutions pour des requêtes plus simple.

Dernière modification par rjuju (02/12/2011 11:57:02)

HadanMarv · 02/12/2011 12:18:49

Merci beaucoup pour cette information.
Effectivement j'ai repris ma requête initial qui ne renvoyait même après 30 minutes et en passant le join_collapse_limit à 5 comme conseillé, la requête donne un résultat en 23 secondes.
Quel est donc la valeur par défaut car ma requête contient 7 joins direct, 8 left join direct, 3 joins indirect, 2 left join indirect, et enfin 2 inner join aussi indirect.
Direct lié au from principal.
Indirect join ou left join direct sur des sous-requêtes

D'avance merci
HadanMarv

Marc Cousin · 02/12/2011 12:30:12

Je pense que le problème rencontré est plutôt dans l'autre sens: la planification n'a pas fait l'effort de tester toutes les permutations, puisque le join_collapse_limit est à 8 (il n'essaye les permutations que sur les 8 premières tables entre elles)… si c'est bien ça, il faudrait augmenter le join_collapse_limit (au nombre de tables impliqués dans la requête par exemple), ne serait-ce que juste le temps de voir si c'est bien le problème… Mais évidemment, le temps de planification est exponentiel avec le nombre de tables à permuter, d'où la présence de cette limite.

Marc Cousin · 02/12/2011 12:33:11

C'est très étrange qu'abaisser le join_collapse_limit améliore le temps d'exécution de la requête (cf la doc sur le sujet : http://www.postgresql.org/docs/9.1/stat … uery.html)
«Smaller values reduce planning time but might yield inferior query plans.»

rjuju · 02/12/2011 12:44:18

Effectivement, la doc qui le contraire, mais pour mon expérience personnelle une baisse de ce paramètre pour certaines requêtes un peu longues aidait à réduire le temps d'exécution. Il est du coup possible que cela soit du à un problème de statistique sur les tables (ce qui était le cas sur mes bases à l'époque).

Dernière modification par rjuju (02/12/2011 12:44:53)

HadanMarv · 02/12/2011 12:57:17

On m'a déjà parlé de cette histoire de statistique. comment savoir si c'est bon ou pas ?

rjuju · 02/12/2011 13:01:19

Le plus simple est de faire un explain analyze de la requête, et de comparer le nombre de ligne estimées et le nombre de ligne réel (cost rows et actual rows) sur les différents noeuds.
Si le résultat est très éloigné les statistiques ne sont pas à jour.

Marc Cousin · 02/12/2011 13:05:10

L'encore plus simple est de la coller sur explain.depesz.com, de regarder ce qui ressort rouge, et de regarder pourquoi c'est rouge

HadanMarv · 02/12/2011 13:16:43

dans l'explain j'ai :

"              ->  Merge Right Join  (cost=59401.53..64132.78 rows=3 width=204)"

l'actual row correspond au chiffre après les ... ?

rjuju · 02/12/2011 13:19:50

Non, les chiffres 59401.53 et 64132.78 correspondent au coût pour ramener la première ligne et pour ramener toutes les lignes.
Il faut faire un explain analyze de votre requête pour avoir le temps d'exécution en plus du coût calculé.

Dernière modification par rjuju (02/12/2011 13:23:25)

HadanMarv · 02/12/2011 14:47:40

"              ->  Merge Right Join  (cost=59401.53..64132.78 rows=3 width=204) (actual time=1409.023..334186.511 rows=9139 loops=1)"

rjuju · 02/12/2011 14:54:42

Lignes estimées : 3
Lignes trouvées : 9139
Il y a donc un problème sur les statistiques d'au moins une table.
Vous pouvez essayer de lancer des analyze sur les différentes tables de la requête et voir si le plan d'exécution évolue, et/ou poster un lien du résultat sur explain.depesz.com pour nous donner plus d'informations.

HadanMarv · 02/12/2011 18:10:54

Malheureusement pas d'amélioration significative, il continue à me retourner le même résultat...
Dois-je redémarrer la base à l'issue de l'analyse / Vaccum ?

rjuju · 02/12/2011 18:43:43

Non il n'est pas du tout nécessaire de redémarrer.
Vous avez lancé un vacuum analyze de toutes les tables utilisées dans la requete ? (un vacuum seul ne suffit pas)
Il faudrait le explain analyze complet pour pouvoir en dire plus.

Marc Cousin · 05/12/2011 09:56:20

Et le code de la requête…

tanguy · 27/12/2011 19:39:16

Petite question qui n'a pas grand chose a voir ( quoique )

Pourquoi avez vous basculé de DB2 vers postgres ?

Nous sommes en train de réfléchir sur un choix de base pour des appli web
nous utilisons actuellement firebird pour nos applis et pour le web mais c'est pas trop prévue pour cette usage
donc quelque soucis sur de la montée en charge

Merci
tout element est actuellement le bienvenue

Tanguy

SQLpro · 02/01/2012 19:15:43

Vous êtes confronté à un problème que je dénonce au sujet de PostGreSQL et qui pose de nombreux problèmes pour des bases à forte volumétrie et pour des requêtes complexes : l'absence de "hint" ou tag de requête, permettant de contraindre l'optimiseur à adopter une technique particulière (par exemple pour une jointure ou pour lire un index).

La plupart des SGBDR implémente ce genre de choses mais les concepteurs actuels de PostGreSQL sont contre l'existence de telle choses, en avançant un argument pour le moins naïf qui se résume en gros à : "postGreSQL est doté d'un optimiseur parfait pourvu qu'on fasse attention aux statistiques"... Bizarrement, Entreprise DB propose un PostGreSQL amélioré supportant ce type de tag, mais c'est payant !!!

J'ai eu l'occasion de faire débat sur ce sujet et biien d'autres, pour montrer comment la migration de grosses bases vers PG pouvait s'avérer cauchemardesques...

A lire, donc, afin de vous faire une opinion
Migration Oracle PostGreSQL : Les 13 grandes lacunes qui peuvent s'avérer cauchemardesque !
Oracle, SQL Server, postGreSQL
Nouvel article : Migration Oracle ou SQL Server vers PosteGreSQL - les écueils
Nouvel article : Migration Oracle ou SQL Server vers PosteGreSQL

A +

Dernière modification par SQLpro (02/01/2012 19:24:32)

gleu · 02/01/2012 19:23:01

les concepteurs actuels de PostGreSQL sont contre l'existence de telle choses, en avançant un argument pour le moins naïf qui se résume en gros à : "postGreSQL est doté d'un optimiseur parfait pourvu qu'on fasse attention aux statistiques"

Je vous met au défi de trouver un seul développeur PostgreSQL qui aurait dit ça. Personne n'a jamais dit que l'optimiseur de PostgreSQL est parfait. Au contraire, les développeurs de PostgreSQL sont bien conscients des limites que peut avoir son implémentation et ils passent beaucoup de temps à améliorer le planificateur. C'est pour cela qu'ils ne veulent pas implémenter les hints. S'ils implémentaient les hints, ils auraient moins de retour sur certaines inexactitudes du planificateur et seraient donc moins à même de le corriger.

Concernant EnterpriseDB, il est possible qu'ils implémentent les hints. C'est leur choix, cela ne concerne en rien PostgreSQL (en tout cas, pas plus que ce que fait Oracle, Microsoft, ...).

#1 02/12/2011 11:39:12

Ordre des jointures et performances

#2 02/12/2011 11:56:50

Re : Ordre des jointures et performances

#3 02/12/2011 12:18:49

Re : Ordre des jointures et performances

#4 02/12/2011 12:30:12

Re : Ordre des jointures et performances

#5 02/12/2011 12:33:11

Re : Ordre des jointures et performances

#6 02/12/2011 12:44:18

Re : Ordre des jointures et performances

#7 02/12/2011 12:57:17

Re : Ordre des jointures et performances

#8 02/12/2011 13:01:19

Re : Ordre des jointures et performances

#9 02/12/2011 13:05:10

Re : Ordre des jointures et performances

#10 02/12/2011 13:16:43

Re : Ordre des jointures et performances

#11 02/12/2011 13:19:50

Re : Ordre des jointures et performances

#12 02/12/2011 14:47:40

Re : Ordre des jointures et performances

#13 02/12/2011 14:54:42

Re : Ordre des jointures et performances

#14 02/12/2011 18:10:54

Re : Ordre des jointures et performances

#15 02/12/2011 18:43:43

Re : Ordre des jointures et performances

#16 05/12/2011 09:56:20

Re : Ordre des jointures et performances

#17 27/12/2011 19:39:16

Re : Ordre des jointures et performances

#18 02/01/2012 19:15:43

Re : Ordre des jointures et performances

#19 02/01/2012 19:23:01

Re : Ordre des jointures et performances

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux