Analyse de l’utilisation de l’index PostgreSQL

Existe-t-il un outil ou une méthode pour parsingr 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 en faisant cela avec l’outil “profiler” pour SQLServer, mais je ne connais pas un outil similaire inclus avec Postgres.

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 then 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 d’parsings de séquence, puis d’parsings d’index. Si la table est petite, elle est ignorée, car Postgres semble préférer les parsings de séquence.

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

La prochaine étape serait de détecter les index combinés manquants. Je suppose que ce n’est pas facile, mais faisable. Peut-être parsingr les requêtes lentes … J’ai entendu que pg_stat_statements pourrait aider …

Vérifiez les statistiques. pg_stat_user_tables et pg_stat_user_indexes sont les premiers à commencer.

Voir ” The Statistics Collector “.

Sur la détermination des indices manquants approche …. Non. Mais il y a des plans pour rendre cela plus facile dans les prochaines versions, comme les pseudo-index et EXPLAIN lisible par machine.

Actuellement, vous devrez EXPLAIN ANALYZE les performances médiocres, puis déterminer manuellement le meilleur itinéraire. Certains parsingurs de journaux tels que pgFouine peuvent aider à déterminer les requêtes.

En ce qui concerne un index non utilisé, vous pouvez utiliser les éléments suivants pour les identifier:

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

Cela aidera à identifier les tuples lus, scannés, récupérés.

Un autre nouvel outil intéressant pour parsingr PostgreSQL est PgHero . Il est plus axé sur le réglage de la firebase database et fait de nombreuses parsings et suggestions.

capture d'écran

Il existe plusieurs liens vers des scripts qui vous aideront à trouver des index inutilisés sur le wiki PostgreSQL . La technique de base consiste à examiner pg_stat_user_indexes et à rechercher celles où idx_scan , le nombre de fois où cet index a été utilisé pour répondre aux requêtes, est nul ou du moins très faible. Si l’application a changé et qu’un index précédemment utilisé n’est probablement pas maintenant, vous devez parfois exécuter pg_stat_reset() pour récupérer toutes les statistiques à 0, puis collecter de nouvelles données; Vous pouvez enregistrer les valeurs actuelles pour tout et calculer un delta au lieu de cela.

Il n’y a pas encore de bons outils pour suggérer des index manquants. Une approche consiste à enregistrer les requêtes que vous exécutez et à parsingr celles qui prennent beaucoup de temps à utiliser un outil d’parsing de journal de requêtes comme pgFouine ou pqa. Voir ” Journalisation des requêtes difficiles ” pour plus d’informations.

L’autre approche consiste à examiner pg_stat_user_tables et à rechercher les tables qui contiennent un grand nombre d’parsings séquentielles, où seq_tup_fetch est volumineux. Lorsqu’un index est utilisé, le nombre idx_fetch_tup est augmenté à la place. Cela peut vous indiquer quand une table n’est pas suffisamment indexée pour répondre aux requêtes.

En fait, déterminer les colonnes sur lesquelles vous devez ensuite indexer? Cela conduit généralement à nouveau à l’parsing des journaux de requêtes.

Vous pouvez utiliser la requête ci-dessous pour rechercher l’utilisation de l’index et la taille de l’index:

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; 

PoWA semble être un outil intéressant pour PostgreSQL 9.4+. Il collecte des statistiques, les visualise et propose des index. Il utilise l’extension pg_stat_statements .

PoWA est PostgreSQL Workload Analyzer qui rassemble des statistiques de performances et fournit des graphiques et des graphiques en temps réel pour surveiller et optimiser vos serveurs PostgreSQL. Il est similaire à Oracle AWR ou SQL Server MDW.

Cela devrait aider: Analyse des requêtes pratiques