1. 冗余索引

    参见《GaussDB(DWS)运维 -- SQL操作 -- 查找冗余索引》。根据提示对冗余索引做对应的处理

2. 未访问索引

    索引热度查询，如果idxscan_num=0，表示索引没有被访问过；如果is_colstore= true，表是索引对应的表是行存表。

    支持9.1.0以下的行存表索引检测，和9.1.0及以上版本的行存+列存索引检测。

     访问次数为0表是次索引从来没有被访问过，当此索引对应记录的is_pk_or_uniq=false时，表是此索引不是约束对应的索引，一般可以删除

SELECT 
    schemaname, tablename, indexname,
    is_colstore,/*true when tablename is column table*/
    is_pk_or_uniq, /*true when the index is primary key/unique key/unique index */
    isprimary, /*true for primary key*/
    isunique, /*true for unique key or unique index*/
    isvisible, /*true when the index is isvisible*/
    ctime, /*timestamp when then index is created*/
    mtime, /*timestamp when then index is modified*/
    max(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),
    c.reloptions::text LIKE ''%orientation=column%'' AS is_colstore,
    (indisprimary OR 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 -- 返回结果是0，表示索引在执行器层面从未被访问过
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)
AND (replace(split_part((substring(version() from ''\((.*)\)'')), '' '', 2), ''.'', NULL)::int >=910 OR is_colstore = false)
')
AS (schemaname name, tablename name, indexname name, ctime timestamptz, mtime timestamptz, is_colstore bool, is_pk_or_uniq bool, isprimary bool, isunique bool, isvisible bool, idx_scan bigint)
GROUP BY schemaname, tablename, indexname, is_colstore, is_pk_or_uniq, isprimary, isunique, isvisible, ctime, mtime
HAVING idxscan_num = 0
ORDER BY 1, 2, 3
;