Vous n'êtes pas identifié(e).
Pages : 1
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
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)
Julien.
https://rjuju.github.io/
Hors ligne
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
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
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
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)
Julien.
https://rjuju.github.io/
Hors ligne
On m'a déjà parlé de cette histoire de statistique. comment savoir si c'est bon ou pas ?
Hors ligne
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.
Julien.
https://rjuju.github.io/
Hors ligne
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 
Marc.
Hors ligne
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
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)
Julien.
https://rjuju.github.io/
Hors ligne
" -> Merge Right Join (cost=59401.53..64132.78 rows=3 width=204) (actual time=1409.023..334186.511 rows=9139 loops=1)"Hors ligne
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.
Julien.
https://rjuju.github.io/
Hors ligne
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
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.
Julien.
https://rjuju.github.io/
Hors ligne
Et le code de la requête…
Marc.
Hors ligne
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
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
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
Pages : 1