在 Postgres 中管理索引

索引可以让你的 Postgres 查询更快。索引就像你数据的“目录”——一个参考列表，允许查询在不扫描整个表的情况下找到表中的某一行（在大表中扫描整个表可能会花很长时间）。

🌐 An index makes your Postgres queries faster. The index is like a "table of contents" for your data - a reference list which allows queries to locate a row in a given table without needing to scan the entire table (which in large tables can take a long time).

索引可以有几种不同的结构方式。选择哪种索引类型取决于你要索引的值。目前最常见的索引类型，也是 Postgres 的默认类型，是 B 树。B 树是二叉搜索树的推广形式，节点可以有超过两个子节点。

🌐 Indexes can be structured in a few different ways. The type of index chosen depends on the values you are indexing. By far the most common index type, and the default in Postgres, is the B-Tree. A B-Tree is the generalized form of a binary search tree, where nodes can have more than two children.

虽然索引可以提高查询性能，但 Postgres 查询规划器在选择优化方法时可能并不总是会使用某个索引。此外，索引也有一些开销——额外的写入和增加的存储——所以了解如何以及何时使用索引（如果需要的话）还是很有用的。

🌐 Even though indexes improve query performance, the Postgres query planner may not always make use of a given index when choosing which optimizations to make. Additionally indexes come with some overhead - additional writes and increased storage - so it's useful to understand how and when to use indexes, if at all.

创建一个索引 #

🌐 Create an index

从一个示例表开始：

🌐 Start with an example table:

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
);

我们可能想要经常根据用户的年龄进行查询：

🌐 We might want to frequently query users based on their age:

1
select name from persons where age = 32;

没有索引的话，Postgres 会扫描表中的每一行来查找 age 的相等匹配。

🌐 Without an index, Postgres will scan every row in the table to find equality matches on age.

你可以通过对查询执行解释来验证这一点：

🌐 You can verify this by doing an explain on the query:

1
explain select name from persons where age = 32;

输出:

🌐 Outputs:

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

要添加一个基本的 B 树索引，你可以运行：

🌐 To add a basic B-Tree index you can run:

1
create index idx_persons_age on persons (age);

这是我们创建的索引的简化图（注意实际上，节点有不止两个子节点）。

🌐 Here is a simplified diagram of the index we created (note that in practice, nodes have more than two children).

你可以看到，在任何大型数据集中，遍历索引以定位特定值所需的操作要远少于从上到下逐个扫描表中的每个值。前者是 O(log n)，而后者是 O(n)。

🌐 You can see that in any large data set, traversing the index to locate a given value can be done in much less operations (O(log n)) than compared to scanning the table one value at a time from top to bottom (O(n)).

部分索引 #

🌐 Partial indexes

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

🌐 If you are frequently querying a subset of rows then it may be more efficient to build a partial index. In our example, perhaps we only want to match on age where deceased is false. We could build a partial index:

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

排序索引 #

🌐 Ordering indexes

默认情况下，B 树索引是按升序排序的，但有时你可能想要提供不同的排序方式。也许我们的应用有一个页面展示年龄最大的前 10 个人。在这里我们希望按降序排序，并把 NULL 值放在最后。为此我们可以使用：

🌐 By default B-Tree indexes are sorted in ascending order, but sometimes you may want to provide a different ordering. Perhaps our application has a page featuring the top 10 oldest people. Here we would want to sort in descending order, and include NULL values last. For this we can use:

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

重新建立索引 #

🌐 Reindexing

过一段时间，索引可能会变得陈旧，需要重建。Postgres 提供了一个 reindex 命令用于此，不过由于在这个过程中 Postgres 会对索引加锁，你可能想要使用 concurrent 关键字。

🌐 After a while indexes can become stale and may need rebuilding. Postgres provides a reindex command for this, but due to Postgres locks being placed on the index during this process, you may want to make use of the concurrent keyword.

1
reindex index concurrently idx_persons_age;

或者你可以重新索引某个表上的所有索引：

🌐 Alternatively you can reindex all indexes on a particular table:

1
reindex table concurrently persons;

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

🌐 Take note that reindex can be used inside a transaction, but reindex [index/table] concurrently cannot.

索引顾问 #

🌐 Index Advisor

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

🌐 Indexes can improve query performance of your tables as they grow. The Supabase Dashboard offers an Index Advisor, which suggests potential indexes to add to your tables.

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

🌐 For more information on the Index Advisor and its suggestions, see the index_advisor extension.

要使用仪表板索引顾问：

🌐 To use the Dashboard Index Advisor:

1. 去查询性能页面。
2. 点击一个查询以打开详细信息侧边栏。
3. 选择索引标签。
4. 如果出现提示，请启用索引顾问。

理解索引顾问的结果 #

🌐 Understanding Index Advisor results

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

🌐 The Indexes tab shows the existing indexes used in the selected query. Note that indexes suggested in the "New Index Recommendations" section may not be used when you create them. Postgres' query planner may intentionally ignore an available index if it determines that the query will be faster without. For example, on a small table, a sequential scan might be faster than an index scan. In that case, the planner will switch to using the index as the table size grows, helping to future proof the query.

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

🌐 If additional indexes might improve your query, the Index Advisor shows the suggested indexes with the estimated improvement in startup and total costs:

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

成本是任意单位，其中一次顺序页面读取的费用为1.0单位。

🌐 Costs are in arbitrary units, where a single sequential page read costs 1.0 units.