1. 索引

没有合适的索引时，数据库查找数据可能需要从第一行读到最后一行。这种方式叫作顺序扫描。表只有几十行时很快，增长到数百万行后，扫描大量无关数据就会成为明显成本。

PostgreSQL 的索引是独立于表数据的结构，其中保存经过组织的键值和对应行的位置。它类似书后的关键词索引：先找到关键词所在页，再去读取正文，而不是从第一页逐字寻找。

例如，project_members 的主键是 (project_id, user_id)，适合从项目出发查成员，却不适合从 user_id 开始查某个用户加入了哪些项目。可以为后一种查询补充索引：

1
CREATE INDEX project_members_user_id_idx
2
ON project_members (user_id);

下面的查询先利用 users.email 的唯一索引找到用户，再利用新索引定位成员关系：

1
SELECT member.project_id, member.role
2
FROM users AS u
3
JOIN project_members AS member ON member.user_id = u.id
4
WHERE u.email = 'xiaoming@example.com';

这里仍然只能说查询「可能」使用索引，因为 PostgreSQL 会比较不同执行方式的成本。表很小，或者条件会返回大部分行时，顺序扫描可能反而更快。

索引不会改变查询结果，只改变数据库可以选择的访问路径。是否真正使用，需要通过下一篇的执行计划确认。

2. B-Tree

PostgreSQL 默认创建 B-Tree 索引。它适合等值、范围和有序查询：

1
CREATE INDEX tasks_created_at_idx
2
ON tasks (created_at);

它可以帮助这些条件：

1
SELECT id, title, created_at
2
FROM tasks
3
WHERE created_at >= now() - interval '7 days';
4

5
SELECT id, title, created_at
6
FROM tasks
7
ORDER BY created_at DESC
8
LIMIT 20;

虽然索引按升序定义，B-Tree 可以反向扫描，因此同一个单列索引也能支持 ORDER BY created_at DESC。ASC 与 DESC 在单列索引中通常没有能力上的区别；联合索引需要混合不同排序方向时，方向设置才会真正影响可支持的排序。

普通 B-Tree 通常不能高效支持 title ILIKE '%接口%'，因为关键词前面存在任意内容，数据库无法从有序开头直接定位。此类搜索可以根据需求考虑 pg_trgm、全文搜索或专门的搜索系统。

主键和唯一约束会自动创建对应的唯一 B-Tree 索引，因此不需要再为 tasks.id 重复创建普通索引。

外键的被引用列通常已经是主键或唯一键，但引用一侧不会因为创建外键就自动获得索引，需要根据查询方式以及删除父行时的检查成本主动判断。前面为 project_members.user_id 创建索引就是这样的例子。

不过，不能看到外键就机械地新增单列索引。tasks 已有 (project_id, task_number) 唯一约束，其自动创建的联合索引以 project_id 开头，已经能够支持只按 project_id 查找。此时再创建 tasks (project_id) 往往属于重复索引。

除了 B-Tree，PostgreSQL 还提供 GIN、GiST、SP-GiST、BRIN 和 Hash 等索引类型。GIN 常用于数组、jsonb 和全文搜索，GiST 常用于范围、几何与最近邻查询，SP-GiST 适合 IP 地址、点等可按空间或前缀划分的数据，BRIN 则适合值与物理存储顺序高度相关的超大表。索引类型必须根据操作符和数据分布选择，基础业务查询通常先从 B-Tree 开始。