postgreSQL查看索引的使用情况

一种追求

[权衡利弊,没有最好,只有更好]

存放索引详细信息的表

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;
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u011944141/article/details/52483977
文章标签: postgresql 索引
个人分类: postgreSQL
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭