GaussDB(DWS)如何统计无效索引

1. 背景介绍

在实时查询场景中，使用行存表+btree索引是常见的优化手段。单由于索引本身也需要占用一定的空间，且当表数据量较大，表比较宽，且索引数量也较多的情况下，业务侧需要统计出哪些索引未被使用过，从而删除这些无效的索引。本文提供了一种手段来识别无用索引。注：该方法只支持行存表索引，不支持列存表（列存表上除主键外，不建议使用索引）。

2. 查询方法

可以通过如下sql识别无用索引：

-- Schemaname：模式名
-- Tablename：表名
-- Indexname：索引名
-- Isprimary：是否是主键
-- Isunique：是否是唯一索引
-- Isvisible：索引是否可见
-- Ctime：索引创建时间
-- Mtime：索引上一次修改时间
-- Idxscan：使用该索引返回的索引行数目（非使用次数），为0表示该索引未被访问过

SELECT 
    schemaname, tablename, indexname,
    isprimary, isunique, isvisible, isvisible,
    ctime, mtime,
    sum(idx_scan) AS idxscan_num
FROM pgxc_parallel_query('dn', 
'SELECT
    n.nspname AS schemaname,
    c.relname AS tablename,
    i.relname AS indexname,
    o.ctime::timestamptz(0),
    o.mtime::timestamptz(0),
    indisprimary | indisunique  AS is_pk_or_uniq,
    indisprimary AS isprimary,
    indisunique AS isunique,
    CASE WHEN i.reloptions IS NOT NULL AND i.reloptions::text like ''%invisible=%'' THEN false ELSE true END AS isvisible, 
    pg_stat_get_numscans(i.oid) AS idx_scan 
FROM pg_class c
INNER JOIN pg_index x ON c.oid = x.indrelid
INNER JOIN pg_class i ON i.oid = x.indexrelid AND c.relnamespace = i.relnamespace
INNER JOIN pg_object o ON o.object_oid = i.oid AND object_type = ''i''
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE 1 = 1
AND c.relname NOT LIKE ''pg_toast_%''
AND c.relname NOT LIKE ''_pg_cudesc_%''
AND i.relname NOT LIKE ''pg_toast_%''
AND i.relname NOT LIKE ''_pg_cudesc_%''
AND c.relkind = ANY (ARRAY[''r'', ''t'', ''m''])
--AND idx_scan = 0 加上这个条件可以刷选出scan次数为0的索引
AND n.oid NOT IN (99,100,3989,11,3987)')
AS (schemaname name, tablename name, indexname name, ctime timestamptz, mtime timestamptz,is_pk_or_uniq bigint, isprimary bool, isunique bool, isvisible bool, idx_scan bigint)
GROUP BY schemaname, tablename, indexname, isprimary, isunique, isvisible, ctime, mtime
ORDER BY 1, 2, 3

3. 参考链接

https://bbs.huaweicloud.com/blogs/249248