PostgreSQL查看索引的使用情况

PostgreSQL查看索引的使⽤情况
--==========================================
--查看索引的使⽤情况
--索引在重建或删除新建时sys.dm_db_index_usage_stats中相关的数据会被清除
--索引在重整是不会清除sys.dm_db_index_usage_stats的数据
SELECT DB_NAME(ixu.database_id) DataBase_Name ,
OBJECT_NAME(ixu.object_id) Table_Name ,
Index_Name ,
( SELECT MAX(s.rows)
FROM sysindexes s
WHERE s.id = ixu.object_id
) AS Table_Rows ,
STATS_DATE(ixu.object_id, ixu.index_id) AS statistic_time ,
er_updates ,
st_user_update ,
st_user_seek ,
er_seeks ,
st_user_scan ,
er_scans ,
st_user_lookup ,
er_lookups ,
er_updates / ( ISNULL(er_seeks, 0) + ISNULL(er_scans,
0) + 1 ) AS UseRate
FROM sys.dm_db_index_usage_stats ixu
INNER JOIN sys.indexes ix
ON ixu.object_id = ix.object_id
AND ixu.index_id = ix.index_id
INNER JOIN sys.objects ob
ON ixu.object_id = ob.object_id
WHERE ob.type = 'U'
AND ob.is_ms_shipped = 0
AND ixu.database_id = DB_ID()
--AND ix.object_id=OBJECT_ID('TableName')
存放索引详细信息的表
data_name=# \d+ pg_stat_user_indexes;
View "pg_catalog.pg_stat_user_indexes"
Column | Type | Modifiers | Storage | Description
---------------+--------+-----------+---------+-------------
relid | oid | | plain |
indexrelid | oid | | plain |
schemaname | name | | plain |
relname | name | | plain |
indexrelname | name | | plain |
idx_scan | bigint | | plain |
idx_tup_read | bigint | | plain |
idx_tup_fetch | bigint | | plain |
View definition:
SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid,
pg_stat_all_indexes.schemaname,
pg_stat_all_indexes.relname,
pg_stat_all_indexes.indexrelname,
pg_stat_all_indexes.idx_scan,
pg_stat_all_indexes.idx_tup_read,
pg_stat_all_indexes.idx_tup_fetch
FROM pg_stat_all_indexes
WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_stat_all_indexes.schemaname !~ '^pg_toast'::text;
通过这个表查看某个表的索引使⽤情况易如反掌了。

⽰例⼀、查看所有表的索引的使⽤情况
select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes order by idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
⽰例⼆、查看某个表的索引使⽤情况
select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = table_name order by idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;。