Discussion :

[Lyche]

Bien le bonjour messieurs,

Je viens vers vous pour m'aider à comprendre (surtout pour m'assurer que mon interprétation n'est pas totalement foireuse!) parce que je ne suis pas certain d'avoir compris le fonctionnement d'une table de statistiques.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELLECT stat_i.*
     , statio_i.idx_blks_read, statio_i.idx_blks_hit
  FROM pg_stat_all_indexes stat_i
       INNER JOIN pg_statio_all_indexes statio_i on stat_i.indexrelid = statio_i.indexrelid;

J'exécute cette requête et j'ai donc 5 colonnes de statistiques qui s'affichent. Je me suis retrouvé avec pas mal de ces colonnes à 0, notamment les colonnes idx_tup_read, idx_scan et idx_tup_fetch.
J'ai donc rajouté ce petit filtre dessous en pensant que c'était des index non utilisés par les divers processus de traitement de l'application.

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
 WHERE idx_tup_read  = 0
   AND idx_scan      = 0
   AND idx_tup_fetch = 0

Est-ce que je peux considérer, à partir de ces 3 champs, que l'index qui possède ces 3 colonnes à 0 est un index inutilisé ? Ou peut-être ai-je mal compris la lecture de cette requête (et c'est probable).

Cordialement,
Lyche

[SQLpro]

Tout dépend de la profondeur des données collectées...
Si vous venez de démarrer votre serveur, de mettre en place la collecte de ces statistiques, ces données ne seront pas fiables. Il faut laisser le serveur fonctionner au moins un mois avant de prendre de telles décisions. Enfin, il convient d'éviter de péter des index sémantiques, tels que ceux :
1) sous K
2) sous contraintes d'unicité
3) sous FK
Il faut donc compléter votre requête par la jointure des tables de métadonnées des index et des contraintes...

De plus ces statistiques semble ne montrer que les lectures par balayage des index... (à confirmer) or l'intérêt des index est d'être utilisés en recherches (seek). Je n'ai pas vu de telles stats ! Se baser donc sur une mesure aussi peu fiable serait donc suicidaire !

A +

[Lyche]

Salut, et merci pour la réponse,

Le serveur n'est pas récent, l'applicatif a quelques années déjà. On m'a demandé de vérifier des lenteurs sur une requête et en cherchant du côté des index j'ai eu ce résultat. La lenteur de la requête provient d'une table dans laquelle il y a un type geometry qui bouffe 95% de l'espace occupé par la table. Du coup, même si les index ne sont pas utilisés, le souci ne vient pas de là et il provient de la lenteur de la table. Pas de tablespace, pas de configuration particulière ou autre. La table est lente (5minutes pour faire un count(*) de 18 Millions de lignes).

Je vais voir pour toucher au modèle de données et sortir la colonne geometry de la table pour la rentrer dans une table simple qui ne plombera pas les perfs.

Et je vais chercher pour compléter ma requête et voir les index seek.

Merci encore pour le temps pris.

Cordialement,
Lyche

[estofilo]

Dans son bouquin "Postgresql 9.0 high performance", l'auteur Greg Smith propose la requête suivante pour identifier les index inutiles:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT schemaname,relname,indexrelname,
  idx_scan,pg_size_pretty(pg_relation_size(i.indexrelid)) as index_size
  FROM pg_stat_user_indexes i join pg_index using(indexrelid)
  WHERE indisunique is false
  ORDER BY idx_scan,relname;

D'après ça, ceux dont idx_scan est à 0 ou très faible sont à suspecter d'être inutiles, même sans regarder les autres colonnes, mais après avoir quand même filtré les index uniques.

[Lyche]

Merci pour la requête

J'ai testé, et bon... pas folichon, sur une base avec plusieurs schemas, j'ai 56 index, dont 36 avec 0 Index Scan et un volume total de plusieurs Go. Ensuite, 13 ont moins de 4000 lectures en 5 ans de travail et le reste est "bon"..

[SQLpro]

Dans son bouquin "Postgresql 9.0 high performance", l'auteur Greg Smith propose la requête suivante pour identifier les index inutiles:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT schemaname,relname,indexrelname,
  idx_scan,pg_size_pretty(pg_relation_size(i.indexrelid)) as index_size
  FROM pg_stat_user_indexes i join pg_index using(indexrelid)
  WHERE indisunique is false
  ORDER BY idx_scan,relname;

D'après ça, ceux dont idx_scan est à 0 ou très faible sont à suspecter d'être inutiles, même sans regarder les autres colonnes, mais après avoir quand même filtré les index uniques.

J'ai déjà testé et c'est pas fiable du tout car seul les scans sont comptabilisés.....

A +

[estofilo]

J'ai déjà testé et c'est pas fiable du tout car seul les scans sont comptabilisés.....

Ce qui est bien ce qu'on veut.
Index Scan sur postgres veut dire qu'il accède à l'index, c'est tout.
Contrairement à la terminologie utilisée dans MS-SQL server, ça ne veut pas dire qu'il y accède via telle méthode par opposition à une autre méthode qui ne serait pas comptabilisée.