这篇文章主要讲解了“分析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.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)感谢各位的阅读，以上就是“分析PostgreSQL中用于索引维护的查询”的内容了，经过本文的学习后，相信大家对分析PostgreSQL中用于索引维护的查询这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
