Footcow BlogFootcow Blog

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.