GaussDB(DWS)运维 -- SQL操作 -- 查找冗余索引

【简介】

索引是常见的查询性能优化手段，但索引本身也需要占用一定的磁盘空间，同时也会降低数据入库的性能。通过学习和了解GaussDB(DWS)的btree索引和cbtree的机制，本文提供一种依靠索引元数据来识别表上索引冗余的手段

【方案】

注： 不支持表达式索引， 不区分partial index和普通的index

-- duplicate：重复索引
--                     解释：索引定义重复
--                     建议：删除重复索引
-- redundancy：冗余索引
--                      解释：如果索引A的索引列刚好是索引B的索引列的前面一部分，那么索引A就可以被认为是冗余索引
--                      建议：删除冗余索引
-- optimizable：可优化索引
--                      解释：如果索引A和B的索引列一致，只是索引列的顺序有差异，那么索引A或者B是可优化的
--                      建议：根据业务优化索引的列字段，通常会根据重复程度，删除索引的后N个索引列
WITH info AS
(
    SELECT 
        n.nspname AS schemaname, 
        c.relname AS tablename, 
        x.indrelid AS indrelid,
        x.indexrelid AS indexrelid,
        indnatts,
        indkey,
        indexprs
    FROM pg_index x
    JOIN pg_class c 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 = 'r' AND c.oid >= 16384 AND (c.reloptions IS NULL OR c.reloptions::text NOT LIKE '%internal_mask%')
    AND i.relkind = 'i' AND i.oid >= 16384
    AND x.indpred IS NULL
)

SELECT
    i.schemaname,
    i.tablename,
    i.indexrelid::regclass::text AS baseidx,
    substring(pg_get_indexdef(i.indexrelid) from 'USING .+\)') AS baseidxdef,
    x.indexrelid::regclass::text AS optidx,
    substring(pg_get_indexdef(x.indexrelid) from 'USING .+\)') AS optidxdef,
    CASE WHEN i.indkey = x.indkey AND pg_get_expr(i.indexprs, i.indrelid) = pg_get_expr(x.indexprs, x.indrelid) THEN 'duplicate'::text
         WHEN x.indexprs IS NULL AND strpos(i.indkey::text||' ', x.indkey::text) = 1 THEN 'redundancy'::text
         WHEN x.indexprs IS NULL AND i.indkey @> x.indkey AND x.indkey @> i.indkey THEN 'optimizable'::text
         ELSE NULL
    END AS optpolicy
FROM info i
INNER JOIN pg_index x ON (i.indrelid = x.indrelid AND i.indexrelid > x.indexrelid)
WHERE x.indpred IS NULL AND optpolicy IS NOT NULL
ORDER BY 1, 2, 3
;