Forums PostgreSQL.fr

mde

Membre

détection rapide d'index manquants

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

gleu

Administrateur

Re : détection rapide d'index manquants

C'est un domaine très complexe. Il est possible de connaître les tables qui font plus de parcours séquentiel que de parcours d'index :

SELECT schemaname,relname,seq_scan,
  idx_scan,cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS idx_scan_pct
FROM pg_stat_user_tables
WHERE (idx_scan + seq_scan)>0
ORDER BY idx_scan_pct;

Plus intéressant, il est aussi possible de connaître le nombre de lignes renvoyées en moyenne par parcours séquentiel et par parcours d'index :

SELECT relname,seq_tup_read,idx_tup_fetch,
  cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct
FROM pg_stat_ user_tables
WHERE (idx_tup_fetch + seq_tup_read)>0
ORDER BY idx_tup_pct

En dehors de ça, PostgreSQL ne propose pas automatiquement des index.

---

Guillaume.

mde

Membre

Re : détection rapide d'index manquants

Très intéressant.
Dommage cependant d'avoir l'info qu'au niveau table et pas au niveau colonne :-(

Merci

SQLpro

Membre

Re : détection rapide d'index manquants

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

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 +

---

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  * * * * *

Marc Cousin

Membre

Re : détection rapide d'index manquants

Ce n'est d'ailleurs pas comme si personne ne faisait rien à ce sujet:

http://www.inf.puc-rio.br/~postgresql/

Ça n'est pas intégré au moteur, mais les résultats de ce groupe de travail ont été présentés au PGCon 2010 (ils ont un système fonctionnel), avec beaucoup d'intérêt de la part des hackers.

---

Marc.

mde

Membre

Re : détection rapide d'index manquants

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.

Dernière modification par mde (25/01/2011 19:13:46)

mde

Membre

Re : détection rapide d'index manquants

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
;

Marc Cousin

Membre

Re : détection rapide d'index manquants

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

---

Marc.

mde

Membre

Re : détection rapide d'index manquants

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é !

Dernière modification par mde (26/01/2011 14:49:29)

Marc Cousin

Membre

Re : détection rapide d'index manquants

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

-> Ça serait surtout absurde. C'est la table qu'on scan, pas la colonne. Ça n'existe pas un scan de colonne.
On scan bien sûr des index aussi. Mais il faut qu'ils soient déclarés pour ça.

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.

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

---

Marc.

mde

Membre

Re : détection rapide d'index manquants

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

Dernière modification par mde (26/01/2011 15:21:27)

Marc Cousin

Membre

Re : détection rapide d'index manquants

Bon, de toutes façons le thread est terminé à mon avis. On vous a répondu, et plusieurs fois, sur ce qui était faisable.

Une petite dernière note pour la route. À froid cette fois-ci, en espérant que ça soit plus constructif :
- J'en suis à presque 1500 posts sur ce forum. C'est la seconde fois que je m'énerve.
- Pour information :
  - L'aide apportée ici est faite sur notre temps libre. Éviter donc de laisser sous-entendre que PostgreSQL est pourri parce que ceci ou cela n'est pas implémenté peut aider à obtenir une réponse. Donc attention par exemple aux 'c'est fastidieux et pas industriel'… ça peut être interprété de façon très négative.
  - Gleu et SQLPro, très tôt dans le thread, ont indiqué qu'il n'y avait pas d'outil à créer des index automatiques. Et que la seule démarche viable, c'était via pgfouine. Tout ce que vous pouvez obtenir à l'heure actuelle c'est : 'tiens, cette table est beaucoup scannée séquentiellement'. Un peu plus en fait, vous pouvez repérer les index pas assez sélectifs. Mais c'est tout. Et pour ça, il faut déjà des index.
  - J'ai expliqué que c'était en cours de développement. Mais très loin d'être fini. Donc que la seule façon possible aujourd'hui c'est par pgfouine. Mais vous avez persisté dans votre direction. Vous ne trouverez rien ou pas grand chose par votre requête. Je vous l'ai redit. Et réinsisté pour que vous utilisiez pgfouine.
  - Évitez les accusations de mauvaise foi. Du moins tant que vous ne saurez pas comment les choses sont implémentées dans le moteur. Quand je dis qu'on ne scan pas de colonne, c'est vrai. Le planificateur ne met pas à jour les statistiques (les pg_stat_*). C'est l'exécuteur qui le fait. Donc pas de possibilité de tracer ce qui vous intéresse. Et de toutes façons, tracer quoi ? Que le planificateur aurait pu envisager l'utilisation d'un index à tel ou tel endroit ? Ça ne vous dira pas si il aurait été bénéfique. La seule façon de le savoir, c'est d'avoir la liste des requêtes exécutées sur le moteur, de simuler l'exécution de ces requêtes avec la présence de cet index (c'est à cela que servent les index hypothétiques, première étape du patch de l'équipe brésilienne), et d'évaluer si le gain en sélection est supérieur à la perte en insertion. J'espère que maintenant vous comprenez  ce que je veux dire par tracer les 'scans de colonne', c'est absurde. Ça ne servirait de toutes façons qu'à vous donner des fausses informations.

Bref, en espérant que ça soit juste une incompréhension… mais j'en doute.

En tout cas, bonne chance, vous allez en avoir besoin, rajouter des dizaines d'index à posteriori sur une base en production, ça reste une opération très risquée.

---

Marc.