Vous n'êtes pas identifié(e).
Pages : 1
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"
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.
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) ?????
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
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é.
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.
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.
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.
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
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=0Pour le DDL, c'est plutôt volumineux, et pas forcément pertinent à mon sens de te plonger dedans ??
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 !
Bonjour,
Y a-t-il des limitations sur les index multi-colonnes avec la version 8.4 de pg ?
J'ai des index sur des paires de colonnes (bigint, timestamp), pour des recherches avec égalité sur la première colonne et opérateur BETWEEN sur la 2°, et pg ne les utilise pas, alors que d'après la plan d'execution et la répartition des datas il aurait plutôt intérêt.
Si je remplace par un index mono-colonne avec le timestamp seulement, il l'utilise mais il filtre sur le champ bigint ensuite. Bref, je continue à creuser le plan d'exec, mais s'il y a des limitations dans la 8.4 ???
Merci pour votre aide.
Mathieu
Dommage que ça parte en mauvaise foi tout ça...
-> Ça serait surtout absurde. C'est la table qu'on scan, pas la colonne. Ça n'existe pas un scan de colonne.
La clause qui permet de choisir une ligne porte bien sur une colonne. Un index porte bien sur une colonne, et l'optimiseur va le choisir si la clause est compatible. Donc il va bien 'scanner' cet index ou cette colonne pour trouver la ligne-qui-va-bien. pg_stats.correlation est bien une info au niveau colonne. Donc pgsql pourrait également compter le nombre de fois qu'il l'explore pour choisir sa ligne. C'est tout.
On scan bien sûr des index aussi. Mais il faut qu'ils soient déclarés pour ça.
Voilà, on y est.
En tout cas, désolé pour vous: vous avez une application importante, développée en hibernate, sans benchmarks avant mise en production, avec beaucoup de mauvaises requêtes. Si vous pensez qu'un clickodrome peut vous sauver, allez y, changez de moteur. Mais votre problème n'est pas là. Votre problème est un projet mal géré, mal développé, à l'arrache.
Je ne viens pas sur ce forum pour être jugé sur ma démarche projet. Je vous remercie pour ce conseil que vous me donnez, mais ce n'est pas ma question. Lorsque j'aurai une question projet, je la poserai comme telle. Vous ne savez même pas si je ne suis pas moi-même dans une démarche d'audit, sur le projet de quelqu'un d'autre...
Bad things happen. C'est,comme ça. Une fois qu'on y est, il faut bien avancer, et en l'occurence j'évalue les pistes. D'où ma question technique dans la rubrique optim.
Il y a énormément de très gros sites, et de très grosses applications qui utilisent PostgreSQL. Apparemment, c'est incompatible avec votre façon de travailler, c'est à dire faire des tests de performance rigoureux avant la mise en production.
On travaille aux tests de perf. Merci. Ce n'est pas le sujet de ma question initiale.
Je sais que pgsql fonctionne sur de grosses appli. Je n'ai pas dit le contraire.
On est d'accord, un outil qui vous conseille sur les index est pratique. Mais ce n'est que 10% de la phase d'optimisation, à tout casser. Si on la fait sérieusement bien sûr. Quant au 'qui marchent très bien', je n'ai pas été DBA Oracle pendant 12 ans pour ne pas en connaître les (énormes) limites.
Très bien.
Ces 10%, en l'occurence, peuvent me faire gagner un facteur 2 pour des cas précis (testé à l'instant). Si ça va vite à les avoir, ces 10%, je les prends. C'est ça un quick-win.
Et pendant ce temps là, on travaille à d'autres activités en parallèle (construction d'injecteurs, banc de perf, refactoring de modèle de données, montée en compétence des développeurs, etc. etc.)
Bon, premier problème: si une table n'a pas d'index du tout (oui, ça veut dire pas de PK, mais il parait qu'il y a encore des gens qui font ça), idx_tup_fetch vaut null…
Ensuite, le fait qu'une table soit parcourue séquentiellement n'est pas du tout une preuve du fait qu'elle manque d'index. Il est très difficile de quantifier le coût de maintenance de l'index par rapport à ce qu'il apporte en sélection. Votre approche est celle qu'on voit habituellement en audit sur des bases sur-indexées (c'est aussi souvent ce qui se passe quand on abuse du clickodrome et de l'assistant d'aide à création d'index…).
Tout ça pour dire que rien ne remplace dans votre cas l'expertise du DBA et la connaissance des requêtes s'exécutant sur la base. Si vous voulez du 'quick win', faites comme on vous l'a suggéré: activez les traces demandées par pgfouine (ça prend 3 minutes, il suffit de savoir lire le mode d'emploi de pgfouine), laissez les traces pendant quelques minutes à quelques heures (suivant votre activité), puis faites les avaler à pgfouine. Vous aurez les n requêtes les plus coûteuses en temps d'exécution. Et améliorez les. Ça ne passe pas forcément par de la création d'index. On gagne souvent bien plus en les réécrivant correctement (on voit souvent des gains de X 1000).
Bonjour,
Oui, nous avons cette démarche pgfouine (ou plutôt pg_stat_statement qui a ma préférence pour l'instant), en parallèle. Mais c'est long, très long, car il y a beaucoup de candidats à l'amélioration.
L'application en question est assez importante et la couche d'accès aux données est en Hibernate... ce qui a l'effet néfaste que vous connaissez sans doute, à savoir que le développeur ne maîtrise pas bien la requête qu'il produit sur la DB. Ce qui rajoute encore du temps dans l'analyse et l'amélioration de l'accès aux données. Il se trouve que l'appli est déjà en prod (donc oui, c'est un peu tard, mais c'est comme ça), et il faut bien commencer qqpart.
Quant au risque de sur-indexation, ... il se trouve que je suis juste à l'opposé de ce problème. Donc je me permets cette approche bourrine à cet instant, pour dégager des améliorations rapides. Je sais aussi le revers de la médaille (coût de maintien d'index) et on développe des injecteurs pour mesurer les perfs et mesurer ce coût.
Certes on ne remplace pas l'expérience d'un DBA, mais il y a des projets où le DBA, on l'a pas sous la main, mais il faut faire le job en attendant.
Enfin les clickodromes qui proposent des index, même si ça n'a pas l'air de vous plaire, il y en existe pour d'autres SGBDR qui marchent très bien et qui permettent de faire en 2h ce que je cherche à faire depuis 4 jours. C'est un fait. Ce qui n'empêche pas de bosser sous contrôle d'un DBA au moment de les appliquer en prod.
Et je maintiens qu'il est fort dommage que pgsql ne soit pas capable de stocker les stats de scan au niveau colonne. Ca serait utile.
Merci en tout cas pour vos commentaires et avis, je regarde avec grand intérêt le projet brésilien que vous avez mentionné !
Toujours les mains dans une grosse appli que je dois optimiser en mode 'quick-win', avant de me palucher du pgfouine + EXPLAIN, voici ce que je me suis construit pour trouver des idées de colonnes à indexer.
Je browse là-dedans, et joue en triant et filtrant sur les colonnes de stats. Je pense arriver à détourer une bonne liste d'index manquants.
Etant donné que je débute sur pgsql, je suis preneur de vos retours sur l'approche et sur cette requête.
Thanks
SELECT
tables.relname as table
, allcol.attname
, n_live_tup
-- , seq_scan, idx_scan,cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS idx_scan_pct
, seq_tup_read,idx_tup_fetch, cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct
, statcol.n_distinct
, statcol.correlation
FROM
pg_class as tables
INNER JOIN pg_attribute as allcol ON (
allcol.attrelid = tables.oid
AND allcol.attnum > 0 -- negative attnum = system columns
)
LEFT JOIN pg_stat_user_tables sut ON (sut.relname = tables.relname)
LEFT JOIN pg_stats statcol ON (statcol.tablename = tables.relname AND statcol.attname = allcol.attname)
WHERE
tables.relkind = 'r' -- regular tables
AND tables.relnamespace = 2200 -- default namespace in DB iris ('public')
AND allcol.atttypid <>0 -- weird attributes
AND allcol.attname NOT IN
(
SELECT indexedcol.attname
FROM
pg_index as ind
INNER JOIN pg_class as indclass ON (indclass.oid = indexrelid)
INNER JOIN pg_attribute as indexedcol ON (
indexedcol.attrelid = tables.oid
AND indexedcol.attnum = ANY (ind.indkey)
AND indexedcol.attnum > 0 -- negative attnum = system columns
)
WHERE
ind.indrelid = tables.oid
)
AND (idx_tup_fetch + seq_tup_read)>0
AND statcol.n_distinct <> 0
ORDER BY n_live_tup DESC
;Non, actuellement PG ne propose pas de vue du genre "missing index", comme c'est le cas de MS SQL Server.
Pour faire cela, vous pouvez utiliser pgfouine pour trouver les requêtes les plus lentes, mais il faut mutualiser les paramètres, puis par la 1ere requête donnée par gleu trouver les scans relatifs aux tables exprimées dans les requêtes lentes et enfin analyser les plans de requête un a un et prévoir les index manquants.
A +
Comme dit dans mon post initial, c'est fastidieux et peu industriel.
Très intéressant.
Dommage cependant d'avoir l'info qu'au niveau table et pas au niveau colonne :-(
Merci
Hi guys,
J'ai une question concernant l'optimisation d'une DB déjà en prod (pgsql 8.4)
Je cherche à obtenir de façon un peu "industrielle" les index manquants.
Je connais un peu pg_fouine, et je joue actuellement avec pg_stat_statements.
Ca me permet de détecter un peu les requêtes problématiques, mais c'est plutôt fastidieux et pas assez industriel à mon goût.
L'optimiseur pgsql est-il capable de dire à chaque fois qu'il a cherché un index sur une table ? Quand il décide de faire un scan parce que l'index qui conviendrait n'existe pas ... est-ce qu'il stocke cette info qqpart ? Ca serait bien utile.
Merci pour vos réponses.
Mathieu
Pages : 1