Mauvais plan d'exec

mde · 07/07/2011 15:05:38

Bonjour,

J'ai une requête lente dont le plan d'exec est le suivant :

"HashAggregate (cost=1274.27..1274.28 rows=1 width=40) (actual time=8181.352..8181.387 rows=113 loops=1)"
" -> Nested Loop (cost=0.00..1274.25 rows=1 width=40) (actual time=1197.680..8180.764 rows=113 loops=1)"
" -> Nested Loop (cost=0.00..1266.01 rows=1 width=56) (actual time=1133.863..8099.979 rows=113 loops=1)"
" -> Nested Loop (cost=0.00..579.60 rows=1687 width=16) (actual time=49.151..3209.842 rows=1317136 loops=1)"
" -> Nested Loop (cost=0.00..118.98 rows=29 width=8) (actual time=21.994..39.772 rows=8640 loops=1)"
" -> Index Scan using idx_typefournisseur_fournisseur on fournisseur fournisseu4_ (cost=0.00..8.27 rows=1 width=8) (actual time=10.000..10.002 rows=1 loops=1)"
" Index Cond: (typefournisseur = 0)"
" -> Index Scan using idx_fournisseur_referenceproduitfournisseur on referenceproduitfournisseur referencep3_ (cost=0.00..110.35 rows=29 width=16) (actual time=11.987..26.546 rows=8640 loops=1)"
" Index Cond: (referencep3_.fk_fournisseur = fournisseu4_.id)"
" -> Index Scan using idx_referenceproduitfournisseur_re_produitref on re_produit_referenceproduitfournisseur referencep2_ (cost=0.00..10.22 rows=453 width=16) (actual time=0.102..0.325 rows=152 loops=8640)"
" Index Cond: (referencep2_.fk_referenceproduitfournisseur = referencep3_.id)"
" -> Index Scan using idx_produit_historiquecommandeproduit on historiquecommandeproduit historique0_ (cost=0.00..0.39 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=1317136)"
" Index Cond: (historique0_.fk_produit = referencep2_.fk_produit)"
" Filter: ((historique0_.datedebutcommande < '2011-07-02 00:00:00'::timestamp without time zone) AND (historique0_.groupeid = 11833))"
" -> Index Scan using produit_pkey on produit produit1_ (cost=0.00..8.23 rows=1 width=8) (actual time=0.712..0.713 rows=1 loops=113)"
" Index Cond: (produit1_.id = historique0_.fk_produit)"
"Total runtime: 8181.619 ms"

En forçant le join order par un SET join_collapse_limit TO 1, j'obtiens le plan d'exec suivant, qui s'avère 60 fois plus rapide.

"HashAggregate (cost=8930.65..8930.66 rows=1 width=40) (actual time=124.975..124.994 rows=113 loops=1)"
" -> Hash Join (cost=8145.28..8930.64 rows=1 width=40) (actual time=116.497..124.914 rows=113 loops=1)"
" Hash Cond: (referencep3_.fk_fournisseur = fournisseu4_.id)"
" -> Hash Join (cost=8137.00..8920.75 rows=425 width=48) (actual time=116.474..124.826 rows=114 loops=1)"
" Hash Cond: (referencep3_.id = referencep2_.fk_referenceproduitfournisseur)"
" -> Seq Scan on referenceproduitfournisseur referencep3_ (cost=0.00..693.73 rows=22873 width=16) (actual time=0.007..4.698 rows=22873 loops=1)"
" -> Hash (cost=8131.69..8131.69 rows=425 width=40) (actual time=116.324..116.324 rows=114 loops=1)"
" -> Nested Loop (cost=11.58..8131.69 rows=425 width=40) (actual time=76.367..116.231 rows=114 loops=1)"
" -> Nested Loop (cost=11.58..4667.43 rows=421 width=48) (actual time=43.419..55.467 rows=112 loops=1)"
" -> Bitmap Heap Scan on historiquecommandeproduit historique0_ (cost=11.58..1199.53 rows=421 width=40) (actual time=43.385..43.672 rows=112 loops=1)"
" Recheck Cond: (groupeid = 11833)"
" Filter: (datedebutcommande < '2011-07-02 00:00:00'::timestamp without time zone)"
" -> Bitmap Index Scan on idx_groupeid_historiquecommandeproduit (cost=0.00..11.47 rows=424 width=0) (actual time=43.342..43.342 rows=112 loops=1)"
" Index Cond: (groupeid = 11833)"
" -> Index Scan using produit_pkey on produit produit1_ (cost=0.00..8.22 rows=1 width=8) (actual time=0.104..0.104 rows=1 loops=112)"
" Index Cond: (produit1_.id = historique0_.fk_produit)"
" -> Index Scan using idx_produit_referenceproduitfournisseur on re_produit_referenceproduitfournisseur referencep2_ (cost=0.00..8.22 rows=1 width=16) (actual time=0.541..0.542 rows=1 loops=112)"
" Index Cond: (referencep2_.fk_produit = historique0_.fk_produit)"
" -> Hash (cost=8.27..8.27 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)"
" -> Index Scan using idx_typefournisseur_fournisseur on fournisseur fournisseu4_ (cost=0.00..8.27 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)"
" Index Cond: (typefournisseur = 0)"
"Total runtime: 125.128 ms"

Ma compréhension est que l'erreur n°1 du query planner est sur l'estimation de lignes renvoyées.

Quelle est la meilleure approche pour traiter ce problème ?
Jouer avec ALTER TABLE SET STATISTICS ? Comment choisir la colonne à modifier, avec quelles valeur de stats ?

Y a-t-il une autre approche ?

Merci par avance !

Dernière modification par mde (07/07/2011 15:24:16)

SQLpro · 07/07/2011 16:28:09

Sans la requête originale, ni le DDL des tables en jeu, pensez vous que la boule de cristal intégrée dans PG puisse vous donner une réponse ?

mde · 07/07/2011 17:12:07

Oui, évidemment, désolé. Voilà la requête :

SELECT DISTINCT 
    historique0_.id AS col_0_0_, 
    referencep3_.id AS col_1_0_, 
    historique0_.venteUVPeriode AS col_2_0_, 
    historique0_.nombreJourPeriode AS col_3_0_, 
    historique0_.datedebutcommande AS col_4_0_, 
    historique0_.datefincommande AS col_5_0_ 
FROM 
    public.HistoriqueCommandeProduit historique0_ 
    INNER JOIN public.Produit produit1_ ON historique0_.fk_produit=produit1_.id 
    INNER JOIN public.re_produit_referenceproduitfournisseur referencep2_ ON produit1_.id=referencep2_.fk_produit 
    INNER JOIN public.referenceproduitfournisseur referencep3_ ON referencep2_.fk_referenceproduitfournisseur=referencep3_.id 
    INNER JOIN public.Fournisseur fournisseu4_ ON referencep3_.fk_fournisseur=fournisseu4_.id 
WHERE 
    historique0_.groupeid=11833 
    AND historique0_.datedebutcommande<'2011-07-02 00:00:00' 
    AND fournisseu4_.typeFournisseur=0

Pour le DDL, c'est plutôt volumineux, et pas forcément pertinent à mon sens de te plonger dedans ??

gleu · 07/07/2011 22:11:52

pensez vous que la boule de cristal intégrée dans PG puisse vous donner une réponse ?

SQLpro, je crois vous avoir déjà dit qu'ici on traitait correctement les gens. On vous a déjà prévenu de ce qui risque d'arriver dans le cas contraire. Ne m'obligez pas à vous banir.

Ma compréhension est que l'erreur n°1 du query planner est sur l'estimation de lignes renvoyées.

Le planificateur estime les lignes grâce aux statistiques. Avez-vous essayé de faire un ANALYZE (pour mettre à jour les statistiques) puis de relancer votre requête ?

Comme le montre http://explain.depesz.com/s/otX , le coût principal de la requête se trouve sur le parcours de l'index idx_produit_historiquecommandeproduit. La récupération d'une valeur est très rapide (0.003 ms), mais 1317136 valeurs sont récupérées au lieu d'une seule ligne. Donc, la première hypothèse, c'est que les statistiques ne sont pas à jour. Essayez de refaire un EXPLAIN ANALYZE après avoir fait un ANALYZE sur la base complète.

mde · 08/07/2011 00:08:47

Ma compréhension est que l'erreur n°1 du query planner est sur l'estimation de lignes renvoyées.
Le planificateur estime les lignes grâce aux statistiques. Avez-vous essayé de faire un ANALYZE (pour mettre à jour les statistiques) puis de relancer votre requête ?
Comme le montre http://explain.depesz.com/s/otX , le coût principal de la requête se trouve sur le parcours de l'index idx_produit_historiquecommandeproduit. La récupération d'une valeur est très rapide (0.003 ms), mais 1317136 valeurs sont récupérées au lieu d'une seule ligne. Donc, la première hypothèse, c'est que les statistiques ne sont pas à jour. Essayez de refaire un EXPLAIN ANALYZE après avoir fait un ANALYZE sur la base complète.

Oui, j'avais déjà lancé un ANALYZE sur la DB. Je l'ai refait et j'obtiens le même plan.
(de toutes façons, le vacuum + analyze auto est actif)

J'ai surtout l'impression que c'est sur l'index idx_fournisseur_referenceproduitfournisseur que démarre le pb : 8640 lignes renvoyées vs. 29 estimées. LA combinatoire fait le reste. Mais ça ne m'avance pas plus.

J'ai tenté de monter les stats à 1000 sur toutes les colonnes impliquées dans les join et le where, sans résultat significatif.

PS : j'utilise un pgsql 8.4

gleu · 08/07/2011 07:57:09

(de toutes façons, le vacuum + analyze auto est actif)

Ça ne veut pas dire que vos statistiques sont à jour, pour cette table comme pour les autres. Ça veut juste dire que l'autovacuum est en mesure d'exécuter un VACUUM et un ANALYZE si un certain nombre de lignes ont subi un traitement sur les tables.

J'ai tenté de monter les stats à 1000 sur toutes les colonnes impliquées dans les join et le where, sans résultat significatif.

Pas très étonnant en fait. Les stats sont sur une colonne donnée et sont donc intéressantes pour estimer le nombre de lignes qui correspondent à une valeur ou plusieurs valeurs connues de cette colonne. Or vos jointures se font en indiquant que la colonne X de la table t1 doit être égale à la colonne Y de la table t2, ce qui est très difficile à estimer. Augmenter en précision sur les valeurs de chaque colonne a peu de chances d'améliorer les choses.

Il me semble que, si vous savez que l'ordre de vos tables dans le FROM est bon (ie, la première table aura le plus petit nombre de lignes, suivi de la deuxième table, puis de la troisième, etc), alors votre meilleure option est de configurer temporairement, le temps d'exécuter la requête en fait, le join_collapse_limit à 1.

mde · 08/07/2011 10:39:16

gleu a écrit :

votre meilleure option est de configurer temporairement, le temps d'exécuter la requête en fait, le join_collapse_limit à 1.

Ahhhh, c'est justement ce que je voulais pas entendre :-((

Je veux bien tuner le planificateur, je veux bien recalibrer tel ou tel paramètre du serveur en fonction des résultats d'un test de charge, ou que sais-je d'autre encore.... Mais faire le boulot du planificateur à sa place, j'aime pas. Et j'ai pas très envie de laisser trainer des 'set join_collapse_limit' dans le code un peu partout.

Merci pour la réponse en tout cas.

cedric · 10/07/2011 01:07:55

le cpu_tuple_cost est un déterminant interessant, réduire sa valeur peut aider a obtenir un bitmap scan.

Idéalement il faudrait connaitre la version de votre serveur PostgreSQL, ainsi que les paramètres de la configuration qui ont déjà été modifiés.

mde · 11/07/2011 11:37:09

cedric a écrit :

le cpu_tuple_cost est un déterminant interessant, réduire sa valeur peut aider a obtenir un bitmap scan.

Non, ça n'a pas vraiment d'effet sur le plan d'exec. Sauf des valeurs extrêmes qui donnent un peu n'importe quoi.

cedric a écrit :

Idéalement il faudrait connaitre la version de votre serveur PostgreSQL, ainsi que les paramètres de la configuration qui ont déjà été modifiés.

v 8.4
Tous les params sont à leur valeur par défaut, sauf ceux en rapport avec la taille mémoire (work_mem, shared_buffers & co), et sauf effective_cache_size qui a été mis à 75% de la mémoire totale.

mde · 11/07/2011 19:39:14

Je m'aperçois également qu'il y a un index qui perturbe pgsql. Si je le supprime, le plan d'exécution bascule vers le 2° que j'ai mentionné.

Dernière modification par mde (11/07/2011 19:39:39)

cedric · 14/07/2011 16:33:36

mde a écrit :

Je m'aperçois également qu'il y a un index qui perturbe pgsql. Si je le supprime, le plan d'exécution bascule vers le 2° que j'ai mentionné.

Pouvez-vous fournir les structures des tables, index, contraintes, etc ... ?

SQLpro · 15/07/2011 14:53:20

Amusant... On en revient à la demande de DDL que j'ai posté en 1ere réponse à ce fil de discussion et pour laquelle on m'a menacé d'être banni !

A +

gleu · 15/07/2011 14:59:53

Je ne t'ai pas menacé de te banir pour avoir demandé la DDL mais pour ta façon de le faire. La demande de Cédric est très correcte, la tienne non.

mde · 18/07/2011 14:31:02

cedric a écrit :

mde a écrit :
Je m'aperçois également qu'il y a un index qui perturbe pgsql. Si je le supprime, le plan d'exécution bascule vers le 2° que j'ai mentionné.
Pouvez-vous fournir les structures des tables, index, contraintes, etc ... ?

Après avoir creusé un peu, le problème se situe sur la relation many-to-many : produit (P) <-> referenceproduitfournisseur (R) (matérialisée par la table re_produit_referenceproduitfournisseur (RE) ).
A l'origine, cette table de relation RE a été créé avec un index unique avec les 2 colonnes, plus un index sur la 2° colonne seule. Cette combinaison d'index permettant le parcours de jointure P <-> R dans les 2 sens.

Hors, la structure et la volumétrie des données sont dissymétriques. Dans la requête mentionnée plus haut, si la jointure est parcourue dans le sens R -> P, on passe par une combinatoire importante dans la table de relation RE (1.3M lignes), puis ça se ressert dans la table P (les conditions limitent à qqs milliers de lignes).

Par contre, en supprimant le 2° index, j'empêche le parcours dans ce sens, ce qui force la jointure dans le sens R -> P. Dans ce sens, la combinatoire est très limitée, on manipule qqs milliers de lignes dans la relation RE, et c'est fini.

Cette relation étant fortement dissymétrique, je pense donc définitivement supprimer ce 2° index, ce qui forcera le parcours des jointures dans le bon sens.

PS : Je n'ai pas donné le DDL jusque là, car je ne suis pas sûr que ça vous aide énormément (c'est plutôt 'confusing' en fait), mais bon, si vous y tenez : bit.ly/nFckq7

SQLpro · 21/07/2011 14:11:01

Il aurait fallu créer les 3 index :
(A), (A, B) et (B)

A +

mde · 21/07/2011 15:04:03

SQLpro a écrit :

Il aurait fallu créer les 3 index :
(A), (A, B) et (B)
A +

Il me semblait qu'un index (A) était complètement superflu si on a un index (A,B) ?????

cedric · 21/07/2011 15:10:28

mde a écrit :

SQLpro a écrit :
Il aurait fallu créer les 3 index :
(A), (A, B) et (B)
A +
Il me semblait qu'un index (A) était complètement superflu si on a un index (A,B) ?????

c'est bien le cas.

gleu · 21/07/2011 15:16:15

Oui, et non. Avec un index seulement sur la colonne A, le parcours de cet index sera plus rapide et plus efficace que le parcours d'un index (A, B). Reste à savoir si, dans votre cas, il vaut mieux un index sur (A, B) et (B) ou un index sur (A) et un index sur (B), voire en plus un index sur (A, B).

flo · 21/07/2011 16:35:38

gleu, il me semble aussi que s'il existe un index sur (A,B), il ne sert à rien de créer un index sur A. Or c'est ce que tu proposes : n'es-tu donc pas d'accord avec Cédric? pourquoi?
(bon, si c'est trop long ou hors sujet, il faudra peut-être reporter sur une autre discussion)

gleu · 21/07/2011 16:50:45

Je ne propose pas de créer d'index. Je précise que, entre un index sur (A,B) et un index sur (A), PostgreSQL choisira plus facilement ce dernier : plus petit, ne contient que les infos dont il a besoin. Je ne sais pas trop si ça répond à ta question.

mde · 21/07/2011 18:22:19

En fait, ma conclusion c'est que pg n'a aucun moyen de savoir que ma relation m2m est dissymétrique (il me semble pas que les statistiques soient suffisantes pour qu'il le sache).
Du coup, il choisit un sens de parcours de cette relation, qui en l’occurrence génère une combinatoire énorme.
Je ne vois pas comment le forcer dans l'autre sens, et je ne pense pas qu'alléger l'index multi-colonne en le rendant mono-colonne soit suffisant pour ça.

cedric · 21/07/2011 19:21:02

mde a écrit :

En fait, ma conclusion c'est que pg n'a aucun moyen de savoir que ma relation m2m est dissymétrique (il me semble pas que les statistiques soient suffisantes pour qu'il le sache).
Du coup, il choisit un sens de parcours de cette relation, qui en l’occurrence génère une combinatoire énorme.
Je ne vois pas comment le forcer dans l'autre sens, et je ne pense pas qu'alléger l'index multi-colonne en le rendant mono-colonne soit suffisant pour ça.

d'autant que l'index sur (A,B) correspond a une contrainte UNIQUE, il n'est pas question de le supprimer.

mde · 22/07/2011 14:41:00

cedric a écrit :

mde a écrit :
En fait, ma conclusion c'est que pg n'a aucun moyen de savoir que ma relation m2m est dissymétrique (il me semble pas que les statistiques soient suffisantes pour qu'il le sache).
Du coup, il choisit un sens de parcours de cette relation, qui en l’occurrence génère une combinatoire énorme.
Je ne vois pas comment le forcer dans l'autre sens, et je ne pense pas qu'alléger l'index multi-colonne en le rendant mono-colonne soit suffisant pour ça.
d'autant que l'index sur (A,B) correspond a une contrainte UNIQUE, il n'est pas question de le supprimer.

Non bien sûr, mais je peux le transformer en (B,A) sans 2° index pour "changer de direction"

#1 07/07/2011 15:05:38

Mauvais plan d'exec

#2 07/07/2011 16:28:09

Re : Mauvais plan d'exec

#3 07/07/2011 17:12:07

Re : Mauvais plan d'exec

#4 07/07/2011 22:11:52

Re : Mauvais plan d'exec

#5 08/07/2011 00:08:47

Re : Mauvais plan d'exec

#6 08/07/2011 07:57:09

Re : Mauvais plan d'exec

#7 08/07/2011 10:39:16

Re : Mauvais plan d'exec

#8 10/07/2011 01:07:55

Re : Mauvais plan d'exec

#9 11/07/2011 11:37:09

Re : Mauvais plan d'exec

#10 11/07/2011 19:39:14

Re : Mauvais plan d'exec

#11 14/07/2011 16:33:36

Re : Mauvais plan d'exec

#12 15/07/2011 14:53:20

Re : Mauvais plan d'exec

#13 15/07/2011 14:59:53

Re : Mauvais plan d'exec

#14 18/07/2011 14:31:02

Re : Mauvais plan d'exec

#15 21/07/2011 14:11:01

Re : Mauvais plan d'exec

#16 21/07/2011 15:04:03

Re : Mauvais plan d'exec

#17 21/07/2011 15:10:28

Re : Mauvais plan d'exec

#18 21/07/2011 15:16:15

Re : Mauvais plan d'exec

#19 21/07/2011 16:35:38

Re : Mauvais plan d'exec

#20 21/07/2011 16:50:45

Re : Mauvais plan d'exec

#21 21/07/2011 18:22:19

Re : Mauvais plan d'exec

#22 21/07/2011 19:21:02

Re : Mauvais plan d'exec

#23 22/07/2011 14:41:00

Re : Mauvais plan d'exec

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux