PostgreSQL索引使用分析 [英] PostgreSQL Index Usage Analysis

问题描述

有没有一种分析Postgres的工具或方法，并确定应该创建哪些缺失的索引，以及哪些未使用的索引应该被删除？我有一些经验使用SQLServer的profiler工具，但我不知道Postgres附带的类似工具。

解决方案

我喜欢这样找到缺少的索引：

  SELECT schemaname，relname，seq_scan-idx_scan AS too_much_seq，case when seq_scan-idx_scan> 0 THEN'Missing Index？'ELSE'OK'END，pg_relation_size（format（'％I.％I'，schemaname，relname）:: regclass）AS rel_size，seq_scan，idx_scan 
 FROM pg_stat_user_tables 
 WHERE pg_relation_size（format（'％I.％I'，schemaname，relname）:: regclass）> 80000 ORDER BY too_much_seq DESC; 
  

这将检查是否有更多的序列扫描，然后进行索引扫描。如果表格很小，则会被忽略，因为Postgres似乎更喜欢顺序扫描。

Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the "profiler" tool for SQLServer, but I'm not aware of a similar tool included with Postgres.

解决方案

I like this to find missing indexes:

SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
 FROM pg_stat_user_tables
 WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000 ORDER BY too_much_seq DESC;

This checks if there are more sequence scans then index scans. If the table is small, it gets ignored, since Postgres seems to prefer sequence scans for them.

这篇关于PostgreSQL索引使用分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！