Linux, Base de données Postgresql, développement, Internet, emailing et déliverabilité.

Aller au contenu | Aller au menu | Aller à la recherche

PostgreSQL : Vos index sont-ils utilisés ?


Pour compléter mon précédent billet sur L'espace disque de votre base de données, il est également particulièrement intéressant d'être en mesure d'évaluer si les index volumineux sont véritablement utiles en production. Un index peut être parfois coûteux en espace de stockage, mais savez-vous si il est véritablement pertinent pour votre applicatif en production ?

Quoi de mieux qu'une simple requête pour vous permettre d'un seul coup d'oeil de connaître à la fois :

  • le nom du schéma
  • le nom de la table concernée
  • le nom de l'index
  • le nombre d'utilisation de l'index
  • la taille de la table
  • la taille de l'index
  • le ratio entre la table et son index

Exemple :

select
    s.schemaname as sch,
    s.relname as rel,
    s.indexrelname as idx,
    s.idx_scan as scans,
    pg_size_pretty(pg_relation_size(s.relid)) as ts,
    pg_size_pretty(pg_relation_size(s.indexrelid)) as "is",
    (1.0 * pg_relation_size(s.indexrelid) / pg_relation_size(s.relid))::numeric(10, 2) as ratio
from
    pg_stat_user_indexes s
    join pg_index i on i.indexrelid=s.indexrelid
    left join pg_constraint c on i.indrelid=c.conrelid and array_to_string(i.indkey, ' ') = array_to_string(c.conkey, ' ')
where
    i.indisunique is false
    and pg_relation_size(s.relid) > 1000000
    and s.idx_scan < 100000
    and c.confrelid is null
order by
    CASE WHEN s.idx_scan < 1000 THEN 0 ELSE s.idx_scan END asc,
    pg_relation_size(s.indexrelid) desc,
    pg_relation_size(s.relid) desc;

Attention cette requête d'exemple, ne prend en compte que des tables de plus 1 Mo, et les index qui n'ont été scannés que moins de 100 000 fois. Vous pouvez adapter les paramètres de pg_relation_size(s.relid) et idx_scan qui se trouvent dans le WHERE en fonction de vos besoins.

Résultat :

  sch   |         rel         |                       idx                       | scans |   ts    |     is     | ratio
--------+---------------------+-------------------------------------------------+-------+---------+------------+-------
 public | table_name          | ix_table_name                                   |     0 | 24 MB   | 16 MB      |  0.68

On voit donc rapidement que si cet index (ix_table_name) est en production, il n'est à ce jour jamais utilisé, et coûte l'air de rien pas loin de 16 Mb d'espace inutile ... il est donc temps de se demander pourquoi il a été créé ...

Je remercie au passage Luddic qui m'a fait partager son expérience sur ce sujet.

Partager

  • Favories


Commentaires

1. Le lundi, 26 octobre 2009, 09:58 par Luddic

De rien :)

Je fournis moi aussi ma source:
http://radek.cc/2009/09/05/psqlrc-t...

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

La discussion continue ailleurs

URL de rétrolien : http://www.footcow.com/index.php/trackback/78

Fil des commentaires de ce billet