Analyse de l'utilisation des index PostgreSQL

Question

Existe-t-il un outil ou une méthode pour analyser Postgres et déterminer quels index manquants doivent être créés et quels index inutilisés doivent être supprimés ? J'ai un peu d'expérience dans ce domaine avec l'outil "profiler" de SQLServer, mais je ne connais pas d'outil similaire inclus dans Postgres.

Answer 1

J'aime ça pour trouver les index manquants :

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
      AND 50 * seq_scan > idx_scan -- more than 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

Cela vérifie s'il y a plus de scans de séquences que de scans d'index. Si la table est petite, elle est ignorée, car Postgres semble préférer les scans de séquence pour ces tables.

La requête ci-dessus révèle les index manquants.

L'étape suivante consisterait à détecter les index combinés manquants. Je suppose que ce n'est pas facile, mais faisable. Peut-être en analysant les requêtes lentes ... J'ai entendu pg_stat_statements pourrait aider...

Answer 2

Vérifiez les statistiques. pg_stat_user_tables et pg_stat_user_indexes sont celles par lesquelles il faut commencer.

Voir " Le collecteur de statistiques ".

Answer 3

En ce qui concerne la détermination des index manquants, ....Nope. Mais il y a des plans pour rendre cela plus facile dans les prochaines versions, comme les pseudo-index et les EXPLAIN lisibles par machine.

Actuellement, vous devez EXPLAIN ANALYZE les requêtes peu performantes, puis déterminer manuellement le meilleur itinéraire. Certains analyseurs de journaux comme pgFouine peut aider à déterminer les requêtes.

En ce qui concerne un index inutilisé, vous pouvez utiliser quelque chose comme ce qui suit pour aider à les identifier :

select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';

Cela permettra d'identifier les tuples lus, scannés, récupérés.

Answer 4

Un autre outil nouveau et intéressant pour l'analyse de PostgreSQL est PgHero . Il est davantage axé sur le réglage de la base de données et propose de nombreuses analyses et suggestions.

Answer 5

Vous pouvez utiliser la requête ci-dessous pour trouver l'utilisation de l'index et sa taille :

La référence est tirée de ce blog.

SELECT
    pt.tablename AS TableName
    ,t.indexname AS IndexName
    ,to_char(pc.reltuples, '999,999,999,999') AS TotalRows
    ,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
    ,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
    ,to_char(t.idx_scan, '999,999,999,999') AS TotalNumberOfScan
    ,to_char(t.idx_tup_read, '999,999,999,999') AS TotalTupleRead
    ,to_char(t.idx_tup_fetch, '999,999,999,999') AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc 
    ON pt.tablename=pc.relname
LEFT OUTER JOIN
( 
    SELECT 
        pc.relname AS TableName
        ,pc2.relname AS IndexName
        ,psai.idx_scan
        ,psai.idx_tup_read
        ,psai.idx_tup_fetch
        ,psai.indexrelname 
    FROM pg_index AS pi
    JOIN pg_class AS pc 
        ON pc.oid = pi.indrelid
    JOIN pg_class AS pc2 
        ON pc2.oid = pi.indexrelid
    JOIN pg_stat_all_indexes AS psai 
        ON pi.indexrelid = psai.indexrelid 
)AS T
    ON pt.tablename = T.TableName
WHERE pt.schemaname='public'
ORDER BY 1;