PostgreSQL的索引监控与查看

PostgreSQL的索引监控与查看

(1) 查看表上面的索引

\d+ Your_Tablename;

(2) 查看某个索引的信息

\di+ idx_xxx_pkey;

(3) PostgreSQL 查看某个表的所有索引信息

• 可以查看到所有的创建语句

select * from pg_indexes where tablename='Your_Tablename';

(4) 查看索引的统计信息

select * from pg_statio_all_indexes where relname='Your_Tablename';

(5) 查看索引名字及类型

select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'Your_Tablename');

(6) 查看索引大小

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'Your_Tablename' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname;