How Postgres chooses which index to use

好奇的话：这里有一份 Postgres 所有内置索引的列表

Postgres 内部原理 #

🌐 Postgres internals

指数是怎么选的 #

🌐 How an index is chosen

Postgres 内部包含一些管理查询执行的组件：

🌐 Postgres, internally, contains a few components that manage query execution:

模块	描述
解析器	将 SQL 转换为可遍历的查询树
规划器/优化器	接收查询树并使用规则和数据库统计信息找到获取数据的最佳策略
执行器	执行规划器创建的计划

当筛选语句中存在索引列时，规划器会考虑使用索引，例如：

🌐 The planner will consider using an index when an indexed column is present in a filter statement, such as:

WHERE
LIKE
ILIKE
DISTINCT
SIMILAR TO
JOIN
ORDER BY

否则，它很可能会执行全表扫描（顺序扫描）。

🌐 Otherwise, it will likely perform a full table scan (sequential scan).

在大多数情况下，被索引的列不仅必须存在，还必须通过与索引兼容的比较运算符（=、>、<>）进行过滤。

🌐 In the majority of cases, the indexed column must not only be present but also must be filtered by a comparison operator (=, >, <>) that is compatible with the index.

举个例子，可以创建如下表格：

🌐 As an example, one can create the following table:

列名	数据类型
id	整数
data	JSONB

在数据列上，可以应用 GIN 索引，这对于筛选 JSONB 数据类型非常棒：

🌐 On the data column, a GIN index can be applied, which is excellent for filtering JSONB datatypes:

1
CREATE INDEX some_arbitary_index_name ON some_table USING gin (data);

这里有一个链接到GIN索引支持的列表操作符；值得注意的是，它不支持大于 >：

🌐 Here's a link to the list operators supported by the GIN index; notably, it does not support greater than >:

1
-- GIN index will never be used
2
select *
3
from some_table
4
where data -> val > 5;

GIN 确实支持 @> 操作符：

🌐 GIN does support the @> operator:

1
--GIN will be considered
2
SELECT id FROM some_table
3
WHERE data @> '[ { "itemId": "p11" } ]';

在大多数情况下，开发者使用默认的 BTREE 索引。它在大多数情况下是最实用和高效的，并且兼容以下过滤器操作符：

🌐 In most cases, developers work with the default BTREE index. It is the most practical and performant in the majority of cases and is compatible with the following filter operators:

比较运算符
`<`
`<=`
`=`
`>=`
`>`

操作符的功能等效项，比如 IN、BETWEEN 和 ANY，也是有效的。

🌐 An operator's functional equivalents, such as IN, BETWEEN, and ANY, are also valid.

不过，即使满足了基本要求（相关列、筛选条件和操作符），也不能保证一定会使用索引。

🌐 However, an index isn't guaranteed to be used, even when the base requirements (relevant column, filter, and operators) are present.

索引有启动成本，所以对于小表，Postgres 可能会使用顺序扫描，如果它认为这样会更快。数据库会保留每个表的统计信息，用来帮助做这些选择。

🌐 Indexes have a startup cost, so for small tables, Postgres might use a sequential scan if it believes that it will take less time. The database keeps statistics about each table that it uses to inform these choices.

在极少数情况下，这些统计数据可能会过时，Postgres 可能会选择使用更慢的索引或顺序扫描，即使有更好的选项可用。

🌐 In very rare cases, these statistics can become stale, and Postgres may opt to use a slower index or sequential scan when a better option is available.

你可以使用 EXPLAIN 关键词查看查询计划：

🌐 You can see the query plan with the EXPLAIN keyword:

1
EXPLAIN <your query>

要了解如何解读它的输出，你可以看看这个解释。

🌐 To understand how to interpret its output, you can check out this explainer.

要在数据库中重置统计信息，你可以使用以下查询：

🌐 To reset statistics within the database, you can use the following query:

1
-- use judiciously
2
select pg_stat_reset();

复合索引 #

🌐 Complex or composite indexes

想要更完整的介绍，可以查看 Postgres 官方文档

多列索引 #

🌐 Multi-column indexes

如果你在多列上创建独立索引，Postgres 很可能会分别使用每个索引来查找相关行，然后再把结果合并起来。

🌐 If you make independent indexes on multiple columns, Postgres will likely use each of them independently to find the relevant rows and then combine the results together.

可以创建多列索引。如果你经常针对多列进行筛选，使用它们而不是几个独立的索引可能会有性能上的好处。

🌐 It is possible to make multi-column indexes. If you are regularly filtering against multiple columns, there can be performance benefits using them instead of several independent indexes.

1
-- multi-column index
2
create index test2_mm_idx on test2 (major, minor);
3
4
-- multi-column comparison:
5
select name
6
from test2
7
where major = constant and minor = constant;

有序索引 #

🌐 Ordered indexes

如果你使用 ORDER BY 子句，索引也可以预先按 DESC/ASC 排序来获得更好的性能。

🌐 If you're using an ORDER BY clause, indexes can also be pre-sorted by DESC/ASC for better performance.

1
-- organizes the index in a DESC order, places NULL values at the end
2
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

功能索引 #

🌐 Functional indexes

虽然不太常见，但索引也可以用于修改过的值，例如在使用 LOWER 函数时：

🌐 Although not as common, indexes can also be leveraged against modified values, such as when using a LOWER function:

1
-- Index on modified column through function
2
create index test1_lower_col1_idx on test1 (lower(col1));
3
4
-- Index will be considered for the following query:
5
select * from test1 where lower(col1) = 'value';

覆盖索引 #

🌐 Covering indexes

索引包含指向特定行的指针，但你可以让索引保存一列值的副本，以便更快地检索。这些被称为 covering 索引。因为维护副本会占用大量存储，所以对于数据量大的值，最好避免使用。观看完整视频

🌐 Indexes contain pointers to a specific row, but you could instruct an index to hold a copy of a column's value for even faster retrieval. These are known as covering indexes. Because maintaining a copy is storage intensive, you should avoid using it for values with large data footprints. FULL VIDEO ON TOPIC

1
CREATE INDEX a_b_idx ON x (a,b) INCLUDE (c);

JSONB 的索引 #

🌐 Indexes on JSONB

虽然可以使用 GIN/GIST 索引来索引整个 JSONB 内容，但你也可以只针对特定的键值使用标准的 BTREE 索引：

🌐 Although a GIN/GIST index can be used to index entire JSONB bodies, you can also target only specific Key-values with standard BTREE indexes:

1
-- Example table
2
create table person (
3
  id serial primary key,
4
  data jsonb
5
);
6
7
create index index_name on person ((data ->> 'name'));