PostgreSQL- index - 索引使用监控

1. 索引年龄问题的本质

1.1 为什么索引年龄不影响数据库健康？

-- 索引的relfrozenxid年龄不会导致事务ID回卷
-- 只有表的relfrozenxid和数据库的datfrozenxid才影响事务ID回卷

-- 验证：检查这些索引对应的主表年龄
SELECT 
    n.nspname as schema_name,
    c.relname as index_name,
    age(c.relfrozenxid) as index_age,
    t.relname as table_name,
    age(t.relfrozenxid) as table_age,
    CASE 
        WHEN age(t.relfrozenxid) > 1500000000 THEN 'TABLE需要关注'
        ELSE 'TABLE正常'
    END as table_status
FROM pg_class c
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class t ON i.indrelid = t.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relname IN (
    'tmp_idx_app_sjts_ftrade_detail_de_p20200701',
    'idx_tenant_id_fuid_active',
    'idx_tenant_id_fuid',
    'idx_app_sjts_ftrade_detail_de_p20201013'
);

1.2 TOAST索引的问题

-- 检查TOAST索引对应的主表
SELECT 
    ti.relname as toast_index,
    t.relname as toast_table, 
    c.relname as main_table,
    n.nspname as schema_name,
    age(ti.relfrozenxid) as toast_index_age,
    age(c.relfrozenxid) as main_table_age
FROM pg_class ti
JOIN pg_class t ON ti.relname = t.relname || '_index' 
    AND t.relkind = 't'  -- TOAST表
JOIN pg_class c ON t.oid = c.reltoastrelid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE ti.relname IN (
    'pg_toast_44916771_index',
    'pg_toast_1479352_index',
    'pg_toast_2182826_index',
    'pg_toast_36115189_index',
    'pg_toast_44327274_index',
    'pg_toast_44796888_index'
);

2. 索引年龄过高的影响

2.1 索引年龄高的可能影响

虽然不会导致事务ID回卷，但可能表明：

1. 索引很少被使用 - 长时间没有更新

2. 索引可能已失效 - 需要重建

3. 查询性能可能下降 - 索引碎片化

2.2 检查索引使用情况

-- 检查索引使用统计
SELECT 
    n.nspname as schema_name,
    c.relname as index_name,
    age(c.relfrozenxid) as index_age,
    idx_scan as scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched,
    pg_size_pretty(pg_relation_size(c.oid)) as index_size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_all_indexes s ON c.oid = s.indexrelid
WHERE c.relname IN (
    'tmp_idx_app_sjts_ftrade_detail_de_p20200701',
    'idx_tenant_id_fuid_active',
    'idx_tenant_id_fuid',
    'idx_app_sjts_ftrade_detail_de_p20201013'
);

3. 处理建议

3.1 优先级分析

根据索引的使用情况决定处理优先级：

-- 综合评估索引健康状况
SELECT 
    n.nspname as schema_name,
    c.relname as index_name,
    age(c.relfrozenxid) as index_age,
    s.idx_scan as scan_count,
    s.idx_tup_read as tuples_read,
    pg_size_pretty(pg_relation_size(c.oid)) as size,
    CASE 
        WHEN s.idx_scan = 0 AND age(c.relfrozenxid) > 1000000000 THEN '可能无用索引-考虑删除'
        WHEN s.idx_scan > 0 AND age(c.relfrozenxid) > 1000000000 THEN '活跃但老旧索引-考虑重建'
        ELSE '正常'
    END as recommendation
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_stat_all_indexes s ON c.oid = s.indexrelid
WHERE age(c.relfrozenxid) > 1000000000
ORDER BY s.idx_scan DESC, age(c.relfrozenxid) DESC;

3.2 处理方案

方案A：对于不使用的索引 - 删除

-- 先确认索引是否真的不使用（观察一段时间）
-- 如果确认无用，删除索引
-- DROP INDEX CONCURRENTLY skids.tmp_idx_app_sjts_ftrade_detail_de_p20200701;

方案B：对于仍在使用的索引 - 重建

-- 重建索引（在维护窗口执行）
-- 获取索引定义
SELECT 
    n.nspname,
    c.relname as index_name,
    c2.relname as table_name,
    pg_get_indexdef(c.oid) as index_definition
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class c2 ON i.indrelid = c2.oid
WHERE c.relname = 'idx_tenant_id_fuid_active';

-- 然后重建
-- REINDEX INDEX CONCURRENTLY skids.idx_tenant_id_fuid_active;

方案C：临时索引 - 根据命名判断

从命名看 tmp_idx_* 可能是临时索引：

-- 检查这些临时索引是否还在使用
SELECT 
    n.nspname,
    c.relname,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_stat_all_indexes s ON c.oid = s.indexrelid
WHERE c.relname LIKE 'tmp_idx_%'
AND age(c.relfrozenxid) > 1000000000;

4. 监控建议

4.1 创建索引年龄监控

-- 创建索引年龄监控视图
CREATE OR REPLACE VIEW index_age_monitor AS
SELECT 
    n.nspname as schema_name,
    c.relname as index_name,
    t.relname as table_name,
    age(c.relfrozenxid) as index_age,
    s.idx_scan as scan_count,
    pg_size_pretty(pg_relation_size(c.oid)) as index_size,
    CASE 
        WHEN age(c.relfrozenxid) > 2000000000 AND s.idx_scan = 0 THEN 'CRITICAL-可能无用'
        WHEN age(c.relfrozenxid) > 2000000000 AND s.idx_scan > 0 THEN 'HIGH-需要重建'
        WHEN age(c.relfrozenxid) > 1000000000 THEN 'MEDIUM-关注'
        ELSE 'LOW-正常'
    END as priority
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_index i ON c.oid = i.indexrelid
JOIN pg_class t ON i.indrelid = t.oid
LEFT JOIN pg_stat_all_indexes s ON c.oid = s.indexrelid
WHERE c.relkind = 'i'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC, s.idx_scan;

-- 查询需要关注的索引
SELECT * FROM index_age_monitor 
WHERE priority IN ('CRITICAL-可能无用', 'HIGH-需要重建')
ORDER BY index_age DESC;

5. 总结

结论：索引年龄高不会导致事务ID回卷问题！

您的实际情况：

• ✅ 数据库年龄1.9亿 - 完全安全（离21亿上限很远）

• ✅ 表年龄正常 - 没有表达到危险年龄

• ⚠️ 索引年龄21亿 - 不影响数据库健康，但可能影响性能

建议行动：

1. 立即放松 - 数据库没有回卷风险

2. 分析索引使用情况 - 确定哪些索引真正需要处理

3. 按优先级处理：

   • 不使用的索引 → 删除

   • 重要但老旧的索引 → 在维护窗口重建

   • 临时索引 → 根据业务决定是否删除

4. 建立定期监控 - 监控索引年龄和使用情况