PostgreSQL索引管理

索引可以加速您的Postgres查询。索引就像是数据的"目录"——它是一个参考列表，允许查询快速定位给定表中的某一行，而无需扫描整个表（对于大型表来说，扫描整个表可能非常耗时）。

索引可以通过几种不同的方式构建。选择哪种索引类型取决于您要索引的值。目前最常见的索引类型（也是Postgres中的默认类型）是B-Tree（B树）。B树是二叉搜索树的广义形式，其中节点可以拥有超过两个子节点。

尽管索引能提升查询性能，但Postgres查询规划器在选择优化方案时，并不总是会使用给定的索引。此外，索引还会带来一些开销——额外的写入操作和增加的存储空间——因此了解如何以及何时使用索引（如果需要使用的话）非常有用。

创建索引

让我们以一个示例表为例：

1
2
3
4
5
6
7
8
create table persons (  id bigint generated by default as identity primary key,  age int,  height int,  weight int,  name text,  deceased boolean);

我们可能经常需要根据年龄查询用户：

1
select name from persons where age = 32;

在没有索引的情况下，Postgres 将扫描表中的每一行来查找年龄匹配的记录。

您可以通过对查询执行 explain 来验证这一点：

1
explain select name from persons where age = 32;

输出结果：

1
2
Seq Scan on persons  (cost=0.00..22.75 rows=x width=y)Filter: (age = 32)

要添加一个简单的 B-Tree 索引，可以运行：

1
create index idx_persons_age on persons (age);

以下是我们刚创建索引的简化示意图（实际应用中，节点通常会有多于两个子节点）。

可以看出，在任何大型数据集中，通过索引遍历定位特定值所需操作次数（O(log n)）远少于从上至下逐行扫描表（O(n)）的方式。

部分索引

如果您经常查询行的子集，那么构建部分索引可能更高效。在我们的示例中，也许我们只想匹配 deceased is false 时的 age。我们可以构建一个部分索引：

1
2
create index idx_living_persons_age on persons (age)where deceased is false;

索引排序

默认情况下，B-Tree 索引是按升序排序的，但有时您可能需要提供不同的排序方式。也许我们的应用程序有一个展示前10位最长寿人士的页面。这时我们会希望按降序排序，并将 NULL 值放在最后。为此我们可以使用：

1
create index idx_persons_age_desc on persons (age desc nulls last);

重建索引

一段时间后，索引可能会变得陈旧，可能需要重建。Postgres 提供了 reindex 命令来实现这一点，但由于在此过程中 Postgres 会对索引加锁，您可能需要使用 concurrent 关键字。

1
reindex index concurrently idx_persons_age;

或者您可以重建特定表上的所有索引：

1
reindex table concurrently persons;

请注意，reindex 可以在事务中使用，但 reindex [index/table] concurrently 不能。

索引顾问

随着表的增长，索引可以提高查询性能。Supabase 仪表板提供了索引顾问功能，它会建议您可以为表添加的潜在索引。

有关索引顾问及其建议的更多信息，请参阅 index_advisor 扩展。

使用仪表板索引顾问的步骤：

1. 前往查询性能页面
2. 点击查询以打开详情侧边面板
3. 选择"索引"标签页
4. 如果提示，请启用索引顾问

理解索引顾问结果

索引选项卡显示所选查询中使用的现有索引。请注意，"新建索引建议"部分推荐的索引在创建后可能不会被使用。Postgres的查询规划器可能会故意忽略可用的索引，如果它判断不使用索引查询会更快。例如，在小表上，顺序扫描可能比索引扫描更快。在这种情况下，随着表大小的增长，规划器会转而使用索引，这有助于使查询具有前瞻性。

如果额外的索引可能改善您的查询，索引顾问会显示建议的索引以及预估的启动成本和总成本改进：

• 启动成本是获取第一行的成本
• 总成本是获取所有行的成本

成本以任意单位表示，其中一次顺序页读取的成本为1.0单位。