前言
KingbaseES具有丰富的索引功能，对于运行一段时间的数据库，经常需要查看索引的使用大小，使用状态等。
尤其重复索引的存在，有时会因为索引过多而造成维护成本加大和减慢数据库的运行速度。
下面是经常使用的查看索引的sql。
1.查看表上索引个数,是否唯一,表与索引大小。
SELECT CONCAT(n.nspname,'.', c.relname) AS table,
i.relname AS index_name
,indisunique is_unique
,sys_size_pretty(sys_relation_size(x.indrelid)) AS table_size,
sys_size_pretty(sys_relation_size(x.indexrelid)) AS index_size,
sys_size_pretty(sys_total_relation_size(x.indrelid)) AS total_size
FROM sys_class c
JOIN sys_index x ON c.oid = x.indrelid
JOIN sys_class i ON i.oid = x.indexrelid
LEFT JOIN sys_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'test1';
table | index_name | is_unique | table_size | index_size | total_size
--------------+------------+-----------+------------+------------+------------
public.test1 | idx_id1 | f | 8192 bytes | 16 kB | 40 kB
public.test1 | idx_id2 | f | 8192 bytes | 16 kB | 40 kB
(2 行记录)
2.索引的创建语句。
SELECT sys_get_indexdef(indexrelid) AS index_query
FROM sys_index
WHERE indrelid = 'test1'::regclass;
index_query
-------------------------------------------------------
CREATE INDEX idx_id1 ON public.test1 USING btree (id)
CREATE INDEX idx_id2 ON public.test1 USING btree (id)
(2 行记录)
3.获取支持的索引方法。
KingbaseES有许多索引方法，如BTree，Hash，GIST和GIN等。
TEST=# select distinct amname from sys_am;
amname
--------
bitmap
btree
brin
heap
spgist
gist
gin
hash
(8 rows)
4.查询未使用的索引。
如果index_scans始终为0 或接近0，可以理解为该索引未使用unsed。
如果有些索引长期未被使用，这些索引不会发挥任何作用，而且会占用不必要的空间，让数据增删改的成本变大，增加备份的时间开销。考虑将其删除。
SELECT s.relname AS table_name,
indexrelname AS index_name,
i.indisunique,
idx_scan AS index_scans
FROM sys_catalog.pg_stat_user_indexes s,
sys_index i
WHERE i.indexrelid = s.indexrelid and idx_scan=0;
table_name | index_name | indisunique | index_scans
------------+--------------+-------------+-------------
company | company_pkey | t | 0
brand | brand_pkey | t | 0
t | t_pkey | t | 0
test1 | idx_id1 | f | 0
test1 | idx_id2 | f | 0
4.1查询没有使用过的大于1MB的索引 top 10 (注意, PK、UK如果只是用于约束, 可能不会被统计计数,但不能删掉)。
select sys_size_pretty(sys_relation_size(indexrelid)),* from sys_stat_all_indexes where sys_relation_size(indexrelid)>=1024000 and (idx_scan=0 or idx_tup_read=0 or idx_tup_fetch=0)
and schemaname not in ('sys_toast','sys_catalog') order by sys_relation_size(indexrelid) desc limit 10;
5.查询重复的索引。
在KingbaseES中同一列可以重复创建索引，然而没有必要在表上有多个具有不同名称的相同索引，同样浪费空间。
SELECT indrelid::regclass table_name,
att.attname column_name,
amname index_method,
indkey
FROM sys_index i,
sys_class c,
sys_opclass o,
sys_am a,
sys_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;
table_name | column_name | index_method | indkey
------------+-------------+--------------+--------
tmp | a | btree | 1
(1 row)
SELECT relname,(array_agg(idx))[1] idx1,
sys_get_indexdef((array_agg(idx))[1]) idx1_def,
(array_agg(idx))[2] idx2,
sys_get_indexdef((array_agg(idx))[2]) idx2_def,
(array_agg(idx))[3] idx3,
sys_get_indexdef((array_agg(idx))[3]) idx3_def
FROM (
SELECT indrelid::regclass AS relname,
indexrelid::regclass AS idx,
(indrelid::text || indclass::text || indkey::text || COALESCE(indexprs::text,'') || COALESCE(indpred::text,'')) AS KEY
FROM sys_index) sub
GROUP BY relname, KEY
HAVING count(*) > 1 \gx
-[ RECORD 1 ]-----------------------------------------------
relname | tmp
idx1 | ind_01
idx1_def | CREATE INDEX ind_01 ON public.tmp USING btree (a)
idx2 | ind_02
idx2_def | CREATE INDEX ind_02 ON public.tmp USING btree (a)
idx3 |
idx3_def |
6.查看无效的索引。
如果 create concurrently index创建索引失败, 索引将处于invalid状态, 需要drop索引重建。
select indisvalid, indexrelid::regclass, indrelid::regclass, sys_get_indexdef(indexrelid) from sys_index where not indisvalid;
7.占用空间top 10的索引。
select schemaname,tablename,indexname,sys_size_pretty(sys_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass)) from sys_indexes
order by sys_relation_size((quote_ident(schemaname)||'.'||quote_ident(indexname))::regclass) desc limit 10;