PostgreSQL 中的索引管理

索引可以使你的 Postgres 查询更快。索引就像你数据的“目录”——一个参考列表，允许查询快速定位给定表中的一行，而无需扫描整个表（在大型表中可能需要很长时间）。

索引可以以几种不同的方式构建。选择的索引类型取决于你正在索引的值。迄今为止最常见的索引类型，也是 Postgres 中的默认类型，是 B 树。B 树是二叉搜索树的一种广义形式，其中节点可以有多个子节点。

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

创建索引#

让我们以一个示例表为例

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

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

1
select name from persons where age = 32;

如果没有索引，Postgres 将扫描表中的每一行以查找年龄的相等匹配项。

你可以通过对查询进行 explain 来验证这一点

1
explain select name from persons where age = 32;

输出

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

要添加一个简单的 B 树索引，你可以运行

1
create index idx_persons_age on persons (age);

这是我们刚刚创建的索引的简化图（请注意，在实践中，节点实际上有多个子节点）。

你可以看到，在任何大型数据集中，遍历索引以定位给定值所执行的操作次数（O(log n)）比逐个扫描表从上到下（O(n)）要少得多。

部分索引#

如果你经常查询行的一个子集，那么构建部分索引可能更有效。例如，我们可能只想在 deceased is false 的情况下匹配 age。我们可以构建一个部分索引

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

排序索引#

默认情况下，B 树索引按升序排序，但有时你可能希望提供不同的排序。也许我们的应用程序有一个页面，其中显示了年龄最大的 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 Dashboard 提供了一个索引顾问，它会建议可以添加到表中的潜在索引。

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

要使用 Dashboard 索引顾问

1. 转到 查询性能页面。
2. 单击查询以调出“详细信息”侧面板。
3. 选择“索引”选项卡。
4. 如果提示，请启用索引顾问。

了解索引顾问结果#

“索引”选项卡显示所选查询中使用的现有索引。请注意，在创建它们时，在“新索引建议”部分中建议的索引可能不会被使用。Postgres 的查询计划器可能会故意忽略可用的索引，如果它确定查询在没有索引的情况下会更快。例如，对于一个小表，顺序扫描可能比索引扫描更快。在这种情况下，计划器将在表的大小增长时切换到使用索引，从而帮助为查询做好未来准备。

如果其他索引可以改进你的查询，索引顾问会显示建议的索引以及启动成本和总成本的估计改进

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

成本以任意单位计算，单个顺序页面读取的成本为 1.0 个单位。