分析PostgreSQL中用于索引维护的查询

这篇文章主要讲解了"分析PostgreSQL中用于索引维护的查询"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"分析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.indrelidJOIN      pg_class i ON i.oid = x.indexrelidLEFT JOIN pg_namespace n ON n.oid = c.relnamespaceWHERE     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.indrelidpg12@testdb-# JOIN      pg_class i ON i.oid = x.indexrelidpg12@testdb-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespacepg12@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_queryFROM   pg_index WHERE  indrelid = 'test'::regclass;[local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_get_indexdef(indexrelid) AS index_queryFROM   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_scansFROM   pg_catalog.pg_stat_user_indexes s,       pg_index iWHERE  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_scanspg12@testdb-# FROM   pg_catalog.pg_stat_user_indexes s,pg12@testdb-#        pg_index ipg12@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_methodFROM     pg_index i,         pg_class c,         pg_opclass o,         pg_am a,         pg_attribute attWHERE    o.oid = ALL (indclass) AND      att.attnum = ANY(i.indkey)AND      a.oid = o.opcmethodAND      att.attrelid = c.oidAND      c.oid = i.indrelidGROUP BY table_name,          att.attname,         indclass,         amname, indkeyHAVING 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_methodpg12@testdb-# FROM     pg_index i,pg12@testdb-#          pg_class c,pg12@testdb-#          pg_opclass o,pg12@testdb-#          pg_am a,pg12@testdb-#          pg_attribute attpg12@testdb-# WHERE    o.oid = ALL (indclass) pg12@testdb-# AND      att.attnum = ANY(i.indkey)pg12@testdb-# AND      a.oid = o.opcmethodpg12@testdb-# AND      att.attrelid = c.oidpg12@testdb-# AND      c.oid = i.indrelidpg12@testdb-# GROUP BY table_name, pg12@testdb-#          att.attname,pg12@testdb-#          indclass,pg12@testdb-#          amname, indkeypg12@testdb-# HAVING count(*) > 1; table_name | column_name | index_method ------------+-------------+-------------- test       | id          | btree(1 row)

感谢各位的阅读，以上就是"分析PostgreSQL中用于索引维护的查询"的内容了，经过本文的学习后，相信大家对分析PostgreSQL中用于索引维护的查询这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！