pgsql索引冗余分析

查询重复索引

SELECT 
    tablename,
    indexname,
    indexdef
FROM 
    pg_indexes
WHERE 
    schemaname = 'public'  -- 通常默认schema为public，可根据实际修改
AND 
    (tablename, substring(indexdef FROM 'USING .*? \(.*\)'))  -- 提取索引类型+字段作为去重键
IN (
    SELECT 
        tablename,
        substring(indexdef FROM 'USING .*? \(.*\)')
    FROM 
        pg_indexes
    WHERE 
        schemaname = 'public'
    GROUP BY 
        tablename, substring(indexdef FROM 'USING .*? \(.*\)')
    HAVING 
        COUNT(*) > 1  -- 筛选出现次数>1的重复组合
)
ORDER BY 
    tablename, substring(indexdef FROM 'USING .*? \(.*\)');

前缀重复索引

-- 精确的索引冗余分析
WITH index_analysis AS (
  SELECT 
    n.nspname AS schema_name,
    t.relname AS table_name,
    idx.relname AS index_name,
    ix.indexrelid AS index_oid,
    -- 只获取列名（不包含顺序）
    (
      SELECT array_agg(attname ORDER BY pos)
      FROM unnest(ix.indkey) WITH ORDINALITY AS k(attnum, pos)
      JOIN pg_attribute a ON a.attrelid = ix.indrelid AND a.attnum = k.attnum
      WHERE k.attnum > 0
    ) AS index_columns,
    -- 获取列名和顺序的组合
    (
      SELECT array_agg(
        attname || 
        CASE 
          WHEN (ix.indoption[k.pos-1] & 1) = 1 THEN ' DESC'
          ELSE ' ASC'
        END
        ORDER BY k.pos
      )
      FROM unnest(ix.indkey) WITH ORDINALITY AS k(attnum, pos)
      JOIN pg_attribute a ON a.attrelid = ix.indrelid AND a.attnum = k.attnum
      WHERE k.attnum > 0
    ) AS index_columns_with_order,
    ix.indisunique AS is_unique,
    pg_relation_size(ix.indexrelid) AS index_size,
    pg_get_indexdef(ix.indexrelid) AS index_def
  FROM 
    pg_index ix
    JOIN pg_class t ON t.oid = ix.indrelid
    JOIN pg_class idx ON idx.oid = ix.indexrelid
    JOIN pg_namespace n ON n.oid = t.relnamespace
  WHERE 
    n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    AND t.relkind = 'r'
)

-- 一次性识别所有类型
SELECT 
  CASE 
    -- 类型1：列和顺序都完全相同
    WHEN ia1.index_columns_with_order = ia2.index_columns_with_order 
         AND ia1.is_unique = ia2.is_unique
    THEN '类型1: 完全重复'

    -- 类型2：列相同但顺序不同
    WHEN ia1.index_columns = ia2.index_columns 
         AND ia1.index_columns_with_order <> ia2.index_columns_with_order
    THEN '类型2: 列相同但顺序不同'

    -- 类型3：前缀重复
    WHEN ia1.index_columns[1:array_length(ia2.index_columns, 1)] = ia2.index_columns
         AND array_length(ia1.index_columns, 1) > array_length(ia2.index_columns, 1)
    THEN '类型3: 前缀重复'

    WHEN ia2.index_columns[1:array_length(ia1.index_columns, 1)] = ia1.index_columns
         AND array_length(ia2.index_columns, 1) > array_length(ia1.index_columns, 1)
    THEN '类型3: 前缀重复'

    ELSE '其他'
  END AS issue_type,

  ia1.schema_name,
  ia1.table_name,
  ARRAY[ia1.index_name, ia2.index_name] AS affected_indexes,
  ia1.index_columns AS index1_columns,
  ia2.index_columns AS index2_columns,
  array_to_string(ia1.index_columns_with_order, ', ') AS index1_order,
  array_to_string(ia2.index_columns_with_order, ', ') AS index2_order,
  ARRAY[pg_size_pretty(ia1.index_size), pg_size_pretty(ia2.index_size)] AS sizes,

  CASE 
    WHEN ia1.index_columns_with_order = ia2.index_columns_with_order THEN
      '完全重复，删除其中一个索引'
    WHEN ia1.index_columns = ia2.index_columns THEN
      '列相同但顺序不同，分析排序查询需求'
    ELSE
      '前缀重复，考虑删除较短的索引'
  END AS recommendation

FROM index_analysis ia1
JOIN index_analysis ia2 ON (
  ia1.schema_name = ia2.schema_name 
  AND ia1.table_name = ia2.table_name 
  AND ia1.index_oid < ia2.index_oid
)
WHERE ia1.index_columns IS NOT NULL 
  AND ia2.index_columns IS NOT NULL
  AND (
    -- 类型1条件
    (ia1.index_columns_with_order = ia2.index_columns_with_order AND ia1.is_unique = ia2.is_unique)
    OR
    -- 类型2条件  
    (ia1.index_columns = ia2.index_columns AND ia1.index_columns_with_order <> ia2.index_columns_with_order)
    OR
    -- 类型3条件
    (ia1.index_columns[1:array_length(ia2.index_columns, 1)] = ia2.index_columns
     AND array_length(ia1.index_columns, 1) > array_length(ia2.index_columns, 1))
    OR
    (ia2.index_columns[1:array_length(ia1.index_columns, 1)] = ia1.index_columns
     AND array_length(ia2.index_columns, 1) > array_length(ia1.index_columns, 1))
  )
ORDER BY issue_type, schema_name, table_name;