查询重复索引
`
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_columnsAND 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_columnsAND 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;
`
