ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(170) - pgAdmin(Queries For Index Maintenance)

PostgreSQL DBA(170) - pgAdmin(Queries For Index Maintenance)

原创 PostgreSQL 作者:husthxd 时间:2020-04-07 16:53:22 0 删除 编辑

本节简单介绍了PostgreSQL中用于索引维护的一些查询.

查看表&索引大小

SELECT    CONCAT(n.nspname,'.', c.relname) AS table,
          i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
          pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
          pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c 
JOIN      pg_index x ON c.oid = x.indrelid
JOIN      pg_class i ON i.oid = x.indexrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE     c.relkind = ANY (ARRAY['r', 't'])
AND       n.oid NOT IN (99, 11, 12375);
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT    CONCAT(n.nspname,'.', c.relname) AS table,
pg12@testdb-#           i.relname AS index_name, pg_size_pretty(pg_relation_size(x.indrelid)) AS table_size,
pg12@testdb-#           pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size,
pg12@testdb-#           pg_size_pretty(pg_total_relation_size(x.indrelid)) AS total_size FROM pg_class c 
pg12@testdb-# JOIN      pg_index x ON c.oid = x.indrelid
pg12@testdb-# JOIN      pg_class i ON i.oid = x.indexrelid
pg12@testdb-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
pg12@testdb-# WHERE     c.relkind = ANY (ARRAY['r', 't'])
pg12@testdb-# AND       n.oid NOT IN (99, 11, 12375);
      table       |    index_name    | table_size | index_size | total_size 
------------------+------------------+------------+------------+------------
 public.test      | test_pkey        | 0 bytes    | 8192 bytes | 16 kB
 public.t_pgbench | idx_t_pgbench_c1 | 425 MB     | 214 MB     | 639 MB
 public.tbl1      | tbl1_pkey        | 5096 kB    | 2208 kB    | 7312 kB
(3 rows)

索引定义

SELECT pg_get_indexdef(indexrelid) AS index_query
FROM   pg_index WHERE  indrelid = 'test'::regclass;
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_get_indexdef(indexrelid) AS index_query
FROM   pg_index WHERE  indrelid = 'test'::regclass;
                          index_query                          
---------------------------------------------------------------
 CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id)
(1 row)

识别未使用的Index

SELECT s.relname AS table_name,
       indexrelname AS index_name,
       i.indisunique,
       idx_scan AS index_scans
FROM   pg_catalog.pg_stat_user_indexes s,
       pg_index i
WHERE  i.indexrelid = s.indexrelid;
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT s.relname AS table_name,
pg12@testdb-#        indexrelname AS index_name,
pg12@testdb-#        i.indisunique,
pg12@testdb-#        idx_scan AS index_scans
pg12@testdb-# FROM   pg_catalog.pg_stat_user_indexes s,
pg12@testdb-#        pg_index i
pg12@testdb-# WHERE  i.indexrelid = s.indexrelid;
 table_name |    index_name    | indisunique | index_scans 
------------+------------------+-------------+-------------
 test       | test_pkey        | t           |           0
 t_pgbench  | idx_t_pgbench_c1 | f           |           0
 tbl1       | tbl1_pkey        | t           |           0
(3 rows)

检索重复的索引

SELECT   indrelid::regclass table_name,
         att.attname column_name,
         amname index_method
FROM     pg_index i,
         pg_class c,
         pg_opclass o,
         pg_am a,
         pg_attribute att
WHERE    o.oid = ALL (indclass) 
AND      att.attnum = ANY(i.indkey)
AND      a.oid = o.opcmethod
AND      att.attrelid = c.oid
AND      c.oid = i.indrelid
GROUP BY table_name, 
         att.attname,
         indclass,
         amname, indkey
HAVING count(*) > 1;
[local:/data/run/pg12]:5120 pg12@testdb=# CREATE UNIQUE INDEX test_pkey_dup ON public.test USING btree (id);
CREATE INDEX
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT   indrelid::regclass table_name,
pg12@testdb-#          att.attname column_name,
pg12@testdb-#          amname index_method
pg12@testdb-# FROM     pg_index i,
pg12@testdb-#          pg_class c,
pg12@testdb-#          pg_opclass o,
pg12@testdb-#          pg_am a,
pg12@testdb-#          pg_attribute att
pg12@testdb-# WHERE    o.oid = ALL (indclass) 
pg12@testdb-# AND      att.attnum = ANY(i.indkey)
pg12@testdb-# AND      a.oid = o.opcmethod
pg12@testdb-# AND      att.attrelid = c.oid
pg12@testdb-# AND      c.oid = i.indrelid
pg12@testdb-# GROUP BY table_name, 
pg12@testdb-#          att.attname,
pg12@testdb-#          indclass,
pg12@testdb-#          amname, indkey
pg12@testdb-# HAVING count(*) > 1;
 table_name | column_name | index_method 
------------+-------------+--------------
 test       | id          | btree
(1 row)

参考资料
Useful Queries For PostgreSQL Index Maintenance

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2684841/,如需转载,请注明出处,否则将追究法律责任。

全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1501
  • 访问量
    3963989