收集索引和表的使用统计信息

想象一下，你的数据库就像一个超大的仓库。索引就是目录和清单，帮你快速找到想要的东西。表就是货架上的商品。如果索引没人用，就像目录被扔在角落里没人翻。如果表用得很频繁，但结构烂或者数据太多，就会让我们的仓库（数据库）很吃力，运行也会变慢。

分析的主要任务：

1. 评估索引的使用效率。 比如，你花大价钱建的索引没人用？那就扔了吧！
2. 确定读写操作的频率。 能帮你看出哪些表用得最多。
3. 优化查询。 统计信息能帮你发现哪里可以加速数据处理，比如加索引或者改索引。

视图 pg_stat_user_indexes 和 pg_stat_user_tables

PostgreSQL 里有两个超实用的统计视图：pg_stat_user_indexes 和 pg_stat_user_tables。咱们来细说下它们。

pg_stat_user_indexes：索引都怎么用的？

主要字段：

• relname — 索引对应的表名。
• indexrelname — 索引名。
• idx_scan — 索引被用来查找的次数。
• idx_tup_read — 通过索引读取的行数。
• idx_tup_fetch — 实际返回的行数（过滤后）。

查询示例：

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

这里我们：

• 按索引调用次数（idx_scan）排序，能看到哪些索引最受欢迎。
• 如果某个索引几乎没用过（idx_scan = 0），就要想想：它到底有啥用？

实用场景：

你上线了新版本的应用，刚加了个新索引。用 pg_stat_user_indexes 可以查查你的查询到底用没用上新索引，还是 PostgreSQL 还在走老路，完全无视你的优化“神作”。

pg_stat_user_tables：表的数据一览

主要字段：

• relname — 表名。
• seq_scan — 表被顺序扫描的次数（没用索引）。
• seq_tup_read — 顺序扫描时返回的行数。
• idx_scan — 表的索引扫描次数。
• n_tup_ins — 插入的行数
• n_tup_upd — 更新的行数。
• n_tup_del — 删除的行数。

查询示例：

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

我们能看到啥？

• 顺序扫描次数多的表（seq_scan）可能需要加索引了。
• 插入、更新、删除操作的数量能帮你了解表里的数据变化有多频繁。

实用场景： 你在搞 users 这个表，里面存着所有用户数据。用 pg_stat_user_tables 发现顺序扫描（seq_scan）爆表。这就是提醒你：赶紧给常用字段加索引，让查询飞起来。

例子：真实数据库里的索引和表分析

假设我们有个数据库，里面有 orders（订单）和 products（商品）两个表。我们想看看表和索引用得咋样。

索引分析：

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

你会看到 orders_customer_id_idx 这个索引被用了 5 万次，而 orders_date_idx 只用过 5 次。也许 orders_date_idx 根本没必要留着。

表分析：

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

products 这个表总是被顺序扫描。这就是提示：商品目录缺索引。

常见错误以及怎么避免

新手最常掉的坑——忽略统计信息。比如你加了个新索引，心想：“这下查询要起飞了”，结果 PostgreSQL 根本不用，因为统计信息没自动更新。表有大变动后，记得手动用 ANALYZE 命令刷新统计。

还有一个常见错法——疯狂加索引。记住，每个索引都占磁盘空间，还会拖慢插入、更新、删除操作。用 pg_stat_user_indexes 的统计，确认索引真有用，别让它白白吃资源。

这些知识有啥用？

实际开发：数据库慢了，第一步肯定是查表和索引的问题。

面试：问你怎么优化索引，这可是 SQL 面试的经典题。能讲明白 pg_stat_user_indexes？你已经过半了。

数据库运维：监控是 DBA 的日常。没有表和索引的统计，你啥都优化不了。