PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

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

HadanMarv
Membre

Ordre des jointures et performances

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

Hors ligne

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

rjuju
Administrateur

Re : Ordre des jointures et performances

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)

Hors ligne

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

HadanMarv
Membre

Re : Ordre des jointures et performances

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

Hors ligne

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

Marc Cousin
Membre

Re : Ordre des jointures et performances

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.

Hors ligne

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

Marc Cousin
Membre

Re : Ordre des jointures et performances

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.»


Marc.

Hors ligne

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

rjuju
Administrateur

Re : Ordre des jointures et performances

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)

Hors ligne

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

HadanMarv
Membre

Re : Ordre des jointures et performances

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

Hors ligne

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

rjuju
Administrateur

Re : Ordre des jointures et performances

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.

Hors ligne

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

Marc Cousin
Membre

Re : Ordre des jointures et performances

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 smile


Marc.

Hors ligne

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

HadanMarv
Membre

Re : Ordre des jointures et performances

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 ... ?

Hors ligne

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

rjuju
Administrateur

Re : Ordre des jointures et performances

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)

Hors ligne

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

HadanMarv
Membre

Re : Ordre des jointures et performances

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

Hors ligne

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

rjuju
Administrateur

Re : Ordre des jointures et performances

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.

Hors ligne

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

HadanMarv
Membre

Re : Ordre des jointures et performances

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 ?

Hors ligne

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

rjuju
Administrateur

Re : Ordre des jointures et performances

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.

Hors ligne

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

Marc Cousin
Membre

Re : Ordre des jointures et performances

Et le code de la requête…


Marc.

Hors ligne

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

tanguy
Membre

Re : Ordre des jointures et performances

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

Hors ligne

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

SQLpro
Membre

Re : Ordre des jointures et performances

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)


Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

Hors ligne

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

gleu
Administrateur

Re : Ordre des jointures et performances

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, ...).


Guillaume.

Hors ligne

Pied de page des forums