PostgreSQL 索引统计
介绍
在PostgreSQL中，索引是提高查询性能的重要工具。然而，仅仅创建索引并不足以确保查询的高效执行。为了优化查询，PostgreSQL需要了解索引的统计信息，以便选择最合适的执行计划。这就是索引统计的作用。
索引统计是PostgreSQL收集并存储的关于索引的数据分布和选择性的信息。这些信息帮助查询规划器决定是否使用索引以及如何使用索引。本文将详细介绍PostgreSQL索引统计的概念、如何查看这些统计信息，以及如何利用它们来优化查询。
索引统计的作用
索引统计的主要作用是帮助查询规划器做出明智的决策。例如，当查询中包含一个条件时，查询规划器需要决定是使用索引扫描还是全表扫描。索引统计信息可以帮助规划器评估使用索引的成本和收益。
选择性（Selectivity）
选择性是索引统计中的一个重要概念。它表示索引中某个值的唯一性程度。选择性越高，索引的效果越好。例如，一个唯一索引的选择性为1，因为它每个值都是唯一的。
基数（Cardinality）
基数是索引中不同值的数量。基数越高，索引的选择性越低。查询规划器会根据基数来决定是否使用索引。
查看索引统计信息
PostgreSQL提供了多种方式来查看索引统计信息。最常用的方法是使用pg_stat_all_indexes系统视图。
SELECT * FROM pg_stat_all_indexes WHERE indexrelid = 'your_index_name'::regclass;
示例
假设我们有一个名为users的表，并且在email列上创建了一个索引users_email_idx。我们可以使用以下查询来查看该索引的统计信息：
SELECT * FROM pg_stat_all_indexes WHERE indexrelid = 'users_email_idx'::regclass;
输出可能如下：
| relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch |
|---|---|---|---|---|---|---|---|
| 123456 | 987654 | public | users | users_email_idx | 100 | 5000 | 5000 |
idx_scan：索引被扫描的次数。idx_tup_read：通过索引读取的元组数量。idx_tup_fetch：通过索引获取的元组数量。
实际应用场景
场景1：优化查询性能
假设我们有一个查询，需要从users表中查找特定email的用户：
SELECT * FROM users WHERE email = 'user@example.com';
如果email列上的索引统计信息显示该列的选择性很高，查询规划器可能会选择使用索引扫描，从而提高查询性能。
场景2：评估索引效果
通过查看pg_stat_all_indexes中的idx_scan和idx_tup_fetch，我们可以评估索引的使用情况。如果idx_scan很低，而idx_tup_fetch很高，可能意味着索引没有被充分利用，或者查询条件不够选择性。
总结
PostgreSQL索引统计是优化查询性能的重要工具。通过了解索引的选择性和基数，查询规划器可以做出更明智的决策。我们可以使用pg_stat_all_indexes系统视图来查看索引的统计信息，并根据这些信息来优化查询。
附加资源
练习
- 在你的PostgreSQL数据库中创建一个表，并在某个列上创建索引。然后使用
pg_stat_all_indexes查看该索引的统计信息。 - 编写一个查询，并使用
EXPLAIN命令查看查询规划器是否使用了索引。根据索引统计信息，尝试优化查询。