一、为什么 PostgreSQL 有这么多索引类型

很多人刚接触 PostgreSQL 索引时，会自然地用 MySQL B+Tree 的思路去理解所有索引：索引就是按字段值排序，然后通过树结构快速定位行。

这个理解对 B-tree 索引基本成立，但不能覆盖 PostgreSQL 的全部索引体系。PostgreSQL 支持多种索引访问方法，常见的有：

  • B-tree：默认索引，适合等值、范围、排序。
  • Hash：适合等值查询，实际使用频率低于 B-tree。
  • GIN：通用倒排索引，适合数组、JSONB、全文搜索。
  • GiST：通用搜索树，适合范围、空间、相似、最近邻。
  • SP-GiST：空间分区搜索树，适合某些非平衡分布的数据结构。
  • BRIN：块区域索引，适合超大表且数据物理顺序相关的场景。

本文重点解释最常被问到、也最容易混淆的四类：

  • B-tree
  • GIN
  • GiST
  • BRIN

尤其会重点讲清楚：

  • GIN 的 posting list 和 posting tree 是什么。
  • GIN 找到 TID 后如何回表。
  • 多个高频 key 的 posting tree 是否会占用很大空间。
  • GiST 和 BRIN 都像“摘要索引”，但区别在哪里。
  • 千万级、亿级表上应该如何选择索引。

二、先理解 PostgreSQL 的行位置：TID / CTID

在理解 GIN、GiST、BRIN 之前，要先理解 PostgreSQL 索引里常见的一个概念：TID。

TID 可以理解为一行数据在 heap 表里的物理位置，形式类似：

1

(block number, offset number)

也就是：

1

第几个数据页 + 这个页里的第几条记录

在 PostgreSQL 里可以通过 ctid 看到一行的物理位置：

1
2

SELECT ctid, *
FROM users;

结果可能类似：

1
2
3
4
5

ctid  | id | name
------+----+------
(0,1) | 1  | Tom
(0,2) | 2  | Jack
(1,1) | 3  | Lucy

这里的 (0,1) 就表示这条记录位于第 0 个 heap block 的第 1 个 offset。

PostgreSQL 大多数索引最终并不直接保存完整行，而是保存能定位 heap tuple 的 TID。查询时通过索引找到 TID，再根据 TID 去 heap 表中取完整行，这个动作通常叫“回表”。

三、B-tree 索引：按值有序查找

B-tree 是 PostgreSQL 的默认索引类型。

如果不指定索引方法：

1

CREATE INDEX idx_users_name ON users(name);

等价于：

1

CREATE INDEX idx_users_name ON users USING btree(name);

B-tree 适合：

  • =
  • <
  • <=
  • >
  • >=
  • BETWEEN
  • ORDER BY
  • MIN / MAX
  • 有序分页

例如：

1
2
3
4
5

SELECT *
FROM orders
WHERE created_at >= '2026-05-01'
  AND created_at <  '2026-06-01'
ORDER BY created_at;

B-tree 的核心思想是：把索引键按顺序组织起来，查询时根据大小关系逐层缩小范围。

简化图：

1
2
3
4
5

                  [50]
                 /    \
            <= 50      > 50
             /            \
       [10,20,30]      [60,70,80]

如果查 age = 70，就沿着有序路径向下定位，最终找到对应的 TID，再回表取完整行。

可以简单理解为：

1

字段值 -> TID

例如：

1
2
3

18 -> [(0,1), (2,5)]
25 -> [(1,3)]
30 -> [(4,2)]

B-tree 的强项是“值本身可排序”。但如果一个字段里包含多个元素，比如数组、JSONB、全文分词，B-tree 就不适合直接处理“包含某个元素”的查询。

四、GIN 索引：通用倒排索引

GIN 全称是：

1

Generalized Inverted Index

中文通常叫“通用倒排索引”。

它的核心思想是：

1

值 / token -> 哪些行包含这个值

普通表是：

1

行 -> 这一行里有哪些值

GIN 反过来：

1

值 -> 这个值出现在哪些行

这就是“倒排”的含义。

1. 用数组理解 GIN

假设有文章表：

1
2
3
4
5
6

id | tags
---+-------------------------
1  | {postgres,index,gin}
2  | {mysql,index}
3  | {postgres,jsonb}
4  | {gin,jsonb,search}

给 tags 建 GIN 索引：

1
2

CREATE INDEX idx_article_tags
ON article USING gin(tags);

GIN 会把数组元素拆开：

1
2
3
4
5
6

postgres -> Row 1, Row 3
index    -> Row 1, Row 2
gin      -> Row 1, Row 4
mysql    -> Row 2
jsonb    -> Row 3, Row 4
search   -> Row 4

图示：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

原始数据：

Row 1: [postgres, index, gin]
Row 2: [mysql, index]
Row 3: [postgres, jsonb]
Row 4: [gin, jsonb, search]


GIN 倒排结构：

postgres ──> Row 1, Row 3
index    ──> Row 1, Row 2
gin      ──> Row 1, Row 4
mysql    ──> Row 2
jsonb    ──> Row 3, Row 4
search   ──> Row 4

当执行：

1
2
3

SELECT *
FROM article
WHERE tags @> ARRAY['postgres'];

PostgreSQL 可以直接从 GIN 索引里找到：

1

postgres -> Row 1, Row 3

再根据这些行位置回表取完整数据。

2. GIN 底层两层结构

GIN 不是简单的一个大哈希表，它大致可以理解为两层：

1
2

第一层：entry tree，保存 key / token
第二层：posting list 或 posting tree，保存 TID 集合

简化结构：

1
2
3
4
5
6
7
8
9

                 GIN Index
                    │
        ┌───────────┴───────────┐
        │                       │
     entry tree             posting data
        │                       │
   "postgres" ─────────> TID 集合
   "jsonb"    ─────────> TID 集合
   "gin"      ─────────> TID 集合

这里的 TID 仍然是 heap 表中的行位置：

1

(block number, offset number)

例如：

1

postgres -> [(12,3), (12,9), (20,1), (81,4)]

含义是：

1
2
3
4
5

postgres 这个 token 出现在：
第 12 个数据页第 3 条记录
第 12 个数据页第 9 条记录
第 20 个数据页第 1 条记录
第 81 个数据页第 4 条记录

3. posting list 和 posting tree

如果某个 key 命中的行比较少，GIN 可以直接在索引项后面保存一段 TID 列表：

1

postgres -> [(12,3), (12,9), (20,1)]

这叫 posting list。

如果某个 key 命中的行特别多，TID 列表太长，无法放进一个索引 tuple 或一个索引页的限制里，就会拆成 posting tree。

posting tree 可以理解为：某个 key 专属的一棵用来管理大量 TID 的树。

1
2
3
4
5
6
7
8
9

postgres
   │
   v
posting tree root
   │
   ├── leaf page A: [(1,2), (1,5), ..., (100,8)]
   ├── leaf page B: [(101,1), ..., (250,7)]
   ├── leaf page C: [(251,2), ..., (500,9)]
   └── leaf page D: [(501,1), ..., (1000,9)]

拆分规则可以简化理解为：

1

按 TID 的有序范围分段存放，页满时分裂。

TID 本身可以排序：

1

(1,2) < (1,5) < (2,1) < (20,3) < (100,1)

所以 posting tree 的叶子页里仍然保存 TID，只是把一个超长 TID 列表拆到了多个索引页中。

需要注意：

1
2

posting tree 不是为每个 key 新建一个独立的 PostgreSQL B-tree 索引对象，
而是在同一个 GIN 索引文件内部，为某个高频 key 挂出的一棵 TID 管理树。

4. 多个高频 key 是否会占用很大空间

会。

如果千万级别的表中，很多 key 都对应大量行，那么这些 key 可能分别拥有很大的 posting tree。

例如：

1
2
3
4

active   -> 900 万个 TID
app      -> 800 万个 TID
normal   -> 700 万个 TID
default  -> 600 万个 TID

GIN 内部可能变成：

1
2
3
4
5
6
7

GIN entry tree
   │
   ├── active  -> huge posting tree -> 900 万 TID
   ├── app     -> huge posting tree -> 800 万 TID
   ├── normal  -> huge posting tree -> 700 万 TID
   ├── default -> huge posting tree -> 600 万 TID
   └── rare_x  -> small posting list

不同 key 之间相同的 TID 通常不会全局去重。

例如一行数据：

1

Row 10: tags = {postgres, jsonb, index}

那么 Row 10 的 TID 会分别出现在：

1
2
3

postgres -> Row 10
jsonb    -> Row 10
index    -> Row 10

这是倒排索引的正常代价。它换来的好处是：给定某个 key，可以直接取出这个 key 的 TID 集合。

因此 GIN 索引空间不只取决于表行数，而更取决于：

1

总倒排记录数 = 行数 × 每行拆出的 token 数

如果 1000 万行，每行平均拆出 10 个 token：

1

倒排记录规模约为 1 亿个 TID 记录

再加上 key、posting tree、页面、压缩与维护开销，GIN 索引可能非常大，甚至可能比原表还大。

5. GIN 如何回表

假设查询：

1
2
3

SELECT id, title, content
FROM article
WHERE tags @> ARRAY['postgres'];

GIN 索引只能告诉 PostgreSQL：

1
2

包含 postgres 的行位置是：
(12,3), (12,9), (20,1), (81,4)

但 id、title、content 不在 GIN 索引里，所以需要根据 TID 去 heap 表中取整行。

流程：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

查询条件：tags @> '{postgres}'

          GIN Index
              │
              v
   postgres -> TID 列表 / posting tree
              │
              v
  [(12,3), (12,9), (20,1)]
      │       │       │
      v       v       v
   表页12   表页12   表页20
   第3行    第9行    第1行
      │       │       │
      v       v       v
   取出完整行：id、title、content、tags...

执行计划中常见：

1
2

Bitmap Index Scan
Bitmap Heap Scan

例如：

1
2
3
4
5

Bitmap Index Scan on idx_article_tags
  Index Cond: (tags @> '{postgres}'::text[])

Bitmap Heap Scan on article
  Recheck Cond: (tags @> '{postgres}'::text[])

含义是：

  • Bitmap Index Scan：从 GIN 索引中找出候选 TID。
  • Bitmap Heap Scan：把 TID 组织成 bitmap，按 heap block 批量回表。
  • Recheck Cond：回表后重新检查条件是否真的满足。

为什么要用 bitmap？

如果命中很多行，一条一条随机回表会很慢：

1
2
3
4

先读 block 100
再读 block 2
再读 block 900
再读 block 3

bitmap 会先把 TID 按 block 组织起来：

1
2
3
4

block 2: offset 1, offset 5
block 3: offset 2
block 100: offset 7
block 900: offset 1

然后尽量按数据页顺序访问 heap，减少随机 I/O。

6. GIN 是否可以不回表

大多数 GIN 查询都需要回表。

原因主要有两个：

第一，GIN 索引里保存的是拆开的 token 和 TID，不保存完整行，也通常不能直接还原完整列。

第二，PostgreSQL 有 MVCC。索引项指向的 heap tuple 对当前事务是否可见，需要访问 heap tuple 或依赖可见性信息判断。

B-tree 在某些场景可以做 Index Only Scan，但 GIN 更常见的是 Bitmap Index Scan + Bitmap Heap Scan。即使查询只判断是否存在，也常常需要 heap 参与可见性检查和 recheck。

7. GIN 适合和不适合的场景

适合：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 数组包含
SELECT * FROM article WHERE tags @> ARRAY['postgres'];

-- JSONB 包含
SELECT * FROM users WHERE profile @> '{"city":"BJ"}';

-- JSONB key 是否存在
SELECT * FROM users WHERE profile ? 'city';

-- 全文搜索
SELECT * FROM docs
WHERE to_tsvector('simple', body) @@ plainto_tsquery('postgres');

不适合：

  • 普通单值等值查询优先考虑 B-tree。
  • 范围查询优先考虑 B-tree 或 BRIN。
  • 高频低选择性 key 查询收益很低。
  • 写入、更新非常频繁且字段 token 很多时，GIN 维护成本较高。

如果只是查 JSONB 中某个固定字段的等值条件：

1
2
3

SELECT *
FROM users
WHERE profile->>'city' = 'BJ';

更推荐表达式 B-tree 索引：

1
2

CREATE INDEX idx_users_profile_city
ON users ((profile->>'city'));

注意：不写 USING btree 时，PostgreSQL 默认就是 B-tree。

这句等价于：

1
2

CREATE INDEX idx_users_profile_city
ON users USING btree ((profile->>'city'));

而如果主要查询 JSONB 包含关系：

1

WHERE profile @> '{"city":"BJ"}'

可以考虑 GIN：

1
2

CREATE INDEX idx_users_profile_gin
ON users USING gin(profile);

如果主要使用 @> 包含查询，还可以考虑更小、更专门的 operator class：

1
2

CREATE INDEX idx_users_profile_gin_path
ON users USING gin(profile jsonb_path_ops);

jsonb_path_ops 通常比默认的 jsonb_ops 更小，适合 @>，但支持的操作范围更窄。

五、GiST 索引：通用搜索树

GiST 全称：

1

Generalized Search Tree

中文通常叫“通用搜索树索引”。

GiST 不是某一种固定规则的索引，而是一个索引框架。不同数据类型可以定义自己的：

  • 如何压缩索引键。
  • 如何判断查询条件与索引项是否一致。
  • 如何计算代价。
  • 如何选择插入分支。
  • 如何分裂页面。
  • 如何判断距离。

所以 GiST 常用于：

  • 几何类型。
  • PostGIS 空间索引。
  • 范围类型：int4range、tsrange、daterange。
  • 最近邻查询。
  • 排除约束。

1. 用时间范围理解 GiST

假设有会议室预订表：

1
2
3
4
5

CREATE TABLE booking (
  id serial PRIMARY KEY,
  room_id int,
  during tsrange
);

数据：

1
2
3
4
5

id | room_id | during
---+---------+-----------------------------------------
1  | 101     | [2026-05-13 10:00, 2026-05-13 11:00)
2  | 101     | [2026-05-13 13:00, 2026-05-13 14:00)
3  | 102     | [2026-05-13 10:30, 2026-05-13 11:30)

创建 GiST 索引：

1
2

CREATE INDEX idx_booking_during
ON booking USING gist(during);

查询某个时间段是否重叠：

1
2
3

SELECT *
FROM booking
WHERE during && tsrange('2026-05-13 10:30', '2026-05-13 10:45');

&& 表示范围是否重叠。

GiST 内部可以把范围组织成类似这样的树：

1
2
3
4
5
6
7
8

Root
 ├── 子树 A: 覆盖 [10:00, 11:30)
 │    ├── Row 1: [10:00, 11:00)
 │    └── Row 3: [10:30, 11:30)
 │
 └── 子树 B: 覆盖 [13:00, 16:00)
      ├── Row 2: [13:00, 14:00)
      └── Row 4: [15:00, 16:00)

查询：

1

[10:40, 10:50)

判断过程：

1
2

子树 A 覆盖 [10:00, 11:30)，可能重叠，继续查。
子树 B 覆盖 [13:00, 16:00)，不可能重叠，跳过。

图示：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

查询范围: [10:40, 10:50)

                  Root
                   │
        ┌──────────┴──────────┐
        │                     │
 [10:00,11:30)           [13:00,16:00)
        │                     │
     继续查                 跳过
        │
 ┌──────┴──────┐
[10,11)    [10:30,11:30)
 命中          命中

2. GiST 的核心是“摘要范围”

B-tree 节点保存的是有序键，可以明确判断：

1

小于 50 去左边，大于 50 去右边。

GiST 节点保存的是某种摘要信息。例如：

  • 范围类型：子树中所有范围的总覆盖范围。
  • 空间类型：子树中所有图形的最小外接矩形。
  • 点类型：子树中点的大致区域。

因此 GiST 的判断逻辑更像：

1

这个子树有没有可能包含我要找的数据？

如果可能，就继续向下查；如果不可能，就跳过整个子树。

3. GiST 为什么也可能需要 recheck

GiST 很多时候保存的是近似摘要，不一定精确。

例如空间索引中，真实图形可能是一个三角形，但索引节点保存的是最小外接矩形：

1
2

真实对象：三角形
索引摘要：包住三角形的矩形

查询时两个矩形相交，并不代表真实图形一定相交。

所以执行计划里也可能看到：

1

Recheck Cond

含义是：

1

索引先判断“可能命中”，回表取真实数据后再精确判断。

4. GiST 支持最近邻查询

GiST 一个很重要的能力是 KNN，也就是最近邻查询。

例如：

1
2
3
4
5
6
7
8

CREATE TABLE places (
  id serial PRIMARY KEY,
  name text,
  location point
);

CREATE INDEX idx_places_location
ON places USING gist(location);

查询离某个点最近的 5 个地方：

1
2
3
4

SELECT *
FROM places
ORDER BY location <-> point '(10,20)'
LIMIT 5;

<-> 表示距离。B-tree 不擅长这种“离某个对象最近”的查询，而 GiST 可以利用树结构按距离逐步搜索。

5. GiST 的典型用途：排除约束

例如同一个会议室，预订时间不能重叠：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
  id serial PRIMARY KEY,
  room_id int,
  during tsrange,
  EXCLUDE USING gist (
    room_id WITH =,
    during WITH &&
  )
);

含义是：

1

不能存在 room_id 相等，并且 during 重叠的两行。

如果插入同一个会议室且时间冲突的记录，PostgreSQL 会直接拒绝。

六、BRIN 索引：块区域索引

BRIN 全称：

1

Block Range Index

中文可以叫“块区域索引”。

BRIN 的核心思想是：

1

不给每一行建索引，而是给一段连续 heap block 建摘要。

PostgreSQL 表数据按 block/page 存储，默认 page size 通常是 8KB。

可以理解为：

1
2
3
4
5

orders 表
  block 1  -> 一批行
  block 2  -> 一批行
  block 3  -> 一批行
  ...

BRIN 会按 block range 记录摘要，例如每 128 个 block 记录一条：

1
2
3

block 1~128     -> min(created_at)=2026-01-01, max(created_at)=2026-01-01 23:59
block 129~256   -> min(created_at)=2026-01-02, max(created_at)=2026-01-02 23:59
block 257~384   -> min(created_at)=2026-01-03, max(created_at)=2026-01-03 23:59

1. 用订单时间理解 BRIN

假设订单表按时间持续写入：

1
2
3
4
5

CREATE TABLE orders (
  id bigint,
  created_at timestamp,
  amount numeric(10,2)
);

创建 BRIN：

1
2

CREATE INDEX idx_orders_created_at_brin
ON orders USING brin(created_at);

查询某一天订单：

1
2
3
4

SELECT *
FROM orders
WHERE created_at >= '2026-01-02'
  AND created_at <  '2026-01-03';

BRIN 判断：

1
2
3

block 1~128：时间范围是 2026-01-01，不可能命中，跳过。
block 129~256：时间范围是 2026-01-02，可能命中，扫描。
block 257~384：时间范围是 2026-01-03，不满足当前范围，跳过。

图示：

1
2
3
4
5
6
7

查询：2026-01-02

BRIN 摘要：

[block 1~128]    2026-01-01 ~ 2026-01-01   跳过
[block 129~256]  2026-01-02 ~ 2026-01-02   扫描这段 block
[block 257~384]  2026-01-03 ~ 2026-01-03   跳过

BRIN 找到的是“可能包含目标数据的 block range”，不是精确行。因此它一定需要扫描这些 block，并逐行 recheck 条件。

2. BRIN 的优势：索引极小

B-tree 和 GIN 往往需要记录大量索引项。

BRIN 只记录 block range 摘要，所以非常小。

例如亿级日志表，如果数据按时间顺序写入，对 created_at 建 BRIN 索引，索引可能只有几十 MB，甚至更小。

BRIN 的空间优势来自：

1

一段 block -> 一条摘要

而不是：

1

一行 -> 一个或多个索引项

3. BRIN 的前提：物理顺序相关

BRIN 是否有效，关键看字段值和表的物理存储顺序是否相关。

非常适合：

  • 自增 ID。
  • 按时间插入的 created_at。
  • 日志表。
  • 事件流水表。
  • 监控指标表。
  • 传感器时序数据。
  • append-only 大表。

不适合：

1

每个 block 里都混杂大量不同时间、不同范围的数据。

如果数据分布很乱：

1
2

block 1~128: 2024、2025、2026 的数据都有
block 129~256: 2024、2025、2026 的数据都有

BRIN 摘要可能变成：

1

min=2024-01-01, max=2026-12-31

这时查询任何时间段，它都觉得每段 block range 可能命中，最后接近全表扫描。

4. pages_per_range 参数

BRIN 默认按一定数量的 page 作为一个 range。可以通过 pages_per_range 调整粒度：

1
2
3

CREATE INDEX idx_orders_created_at_brin
ON orders USING brin(created_at)
WITH (pages_per_range = 64);

pages_per_range 越小：

  • 摘要更细。
  • 误扫 block 更少。
  • 索引稍大。

pages_per_range 越大：

  • 摘要更粗。
  • 索引更小。
  • 误扫 block 可能更多。

所以 BRIN 是典型的空间和精度之间做权衡。

七、GIN、GiST、BRIN 的核心区别

1. B-tree vs GIN

B-tree：

1

字段值按顺序组织 -> 定位 TID

适合：

1
2
3

WHERE age = 18
WHERE created_at >= '2026-01-01'
ORDER BY created_at

GIN：

1

token -> TID 集合

适合：

1
2
3

WHERE tags @> ARRAY['postgres']
WHERE profile @> '{"city":"BJ"}'
WHERE body_tsv @@ plainto_tsquery('postgres')

B-tree 像字典目录，按词条顺序查找；GIN 像搜索引擎的倒排表，先找到词，再找到包含这个词的文档。

2. GIN vs GiST

GIN 是倒排：

1
2

postgres -> [(0,1), (2,5), (8,3)]
jsonb    -> [(1,2), (2,5), (9,1)]

GiST 是搜索树：

1
2
3

Root
 ├── 子树 A：覆盖某个范围/区域
 └── 子树 B：覆盖另一个范围/区域

GIN 适合“包含某个 token 的行有哪些”。

GiST 适合“这个范围、空间对象、距离关系有没有可能匹配”。

3. GiST vs BRIN

两者都可能保存“摘要”，但摘要对象完全不同。

GiST：

1

按数据值、空间关系、范围关系组织成搜索树。

BRIN：

1

按 heap 物理 block range 记录摘要。

GiST 关心的是：

1

这个查询条件和哪棵子树的数据范围可能相交？

BRIN 关心的是：

1

这一段物理数据块有没有可能包含目标数据？

图示对比：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

GiST:

                  Root
                   │
        ┌──────────┴──────────┐
        │                     │
 [10:00,11:30)           [13:00,16:00)
        │                     │
     继续查                 跳过


BRIN:

[block 1~128]    min=01-01, max=01-01   跳过
[block 129~256]  min=01-02, max=01-02   扫描
[block 257~384]  min=01-03, max=01-03   跳过

八、常见索引选择建议

1. 普通等值、范围、排序

优先 B-tree：

1
2

CREATE INDEX idx_orders_created_at
ON orders(created_at);

适合：

1
2

WHERE created_at >= '2026-01-01'
ORDER BY created_at

2. JSONB 某个固定字段等值查询

优先表达式 B-tree：

1
2

CREATE INDEX idx_users_city
ON users ((profile->>'city'));

适合：

1
2
3

SELECT *
FROM users
WHERE profile->>'city' = 'BJ';

3. JSONB 包含查询

考虑 GIN：

1
2

CREATE INDEX idx_users_profile_gin
ON users USING gin(profile);

适合：

1
2
3

SELECT *
FROM users
WHERE profile @> '{"city":"BJ"}';

如果主要是 @>：

1
2

CREATE INDEX idx_users_profile_gin_path
ON users USING gin(profile jsonb_path_ops);

4. 数组包含查询

使用 GIN：

1
2

CREATE INDEX idx_article_tags
ON article USING gin(tags);

适合：

1
2
3

SELECT *
FROM article
WHERE tags @> ARRAY['postgres'];

5. 全文搜索

使用 GIN：

1
2

CREATE INDEX idx_docs_body_tsv
ON docs USING gin(to_tsvector('simple', body));

查询：

1
2
3

SELECT *
FROM docs
WHERE to_tsvector('simple', body) @@ plainto_tsquery('postgres');

生产中更常见的做法是把 tsvector 做成生成列或单独字段，再建 GIN。

6. 时间范围重叠、空间、最近邻

考虑 GiST：

1
2

CREATE INDEX idx_booking_during
ON booking USING gist(during);

适合：

1

WHERE during && tsrange('2026-05-13 10:00', '2026-05-13 11:00')

7. 超大流水表按时间查询

如果数据按时间插入，考虑 BRIN：

1
2

CREATE INDEX idx_logs_created_at_brin
ON logs USING brin(created_at);

适合：

1
2

WHERE created_at >= '2026-05-01'
  AND created_at <  '2026-05-02'

九、千万级表上的注意事项

1. 不要无脑给整个 JSONB 建 GIN

如果 JSONB 字段很大、key 很多、写入更新频繁，GIN 索引可能非常大。

尤其是：

1

行数很大 × 每行 token 很多 × 高频 key 很多

会导致大量 posting tree 和 TID 重复存储。

如果业务只查某几个固定字段，优先考虑表达式索引：

1
2
3
4
5

CREATE INDEX idx_users_city
ON users ((profile->>'city'));

CREATE INDEX idx_users_level
ON users ((profile->>'level'));

2. 高频低选择性条件未必适合索引

例如：

1

status = active 命中 90% 行

即使用索引找到 90% 的 TID，仍然需要大量回表。优化器可能认为顺序扫描更划算。

所以索引不是“建了就一定用”，PostgreSQL 会根据统计信息估算成本。

3. GIN 写入成本较高

插入一行：

1

tags = {postgres, index, gin, jsonb, search}

B-tree 对单列通常插入一个索引项。

GIN 需要插入多个 token：

1
2
3
4
5

postgres -> 当前 TID
index    -> 当前 TID
gin      -> 当前 TID
jsonb    -> 当前 TID
search   -> 当前 TID

因此 GIN 的写入、更新、VACUUM 维护成本通常更高。

PostgreSQL 为 GIN 提供了 fastupdate 机制，新写入的索引项可以先进入 pending list，再批量合并到主索引。

简化流程：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

新写入数据
   │
   v
pending list
   │
   v
后台或查询触发合并
   │
   v
GIN main index

这可以提高写入吞吐，但 pending list 过大时，某次查询或维护可能突然变慢。

4. 用实际执行计划验证

建索引前后要看执行计划：

1
2
3
4

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM users
WHERE profile @> '{"city":"BJ"}';

重点关注：

  • 是否使用了目标索引。
  • 是 Index Scan、Bitmap Index Scan 还是 Seq Scan。
  • Rows Removed by Index Recheck 是否很多。
  • Heap Blocks 访问量是否很大。
  • 实际返回行数和估算行数是否差距很大。
  • shared hit/read block 是否符合预期。

5. 查看索引大小

可以通过以下 SQL 查看表和索引大小：

1
2
3
4
5

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('users', 'idx_users_profile_gin');

查看某张表的所有索引大小：

1
2
3
4
5
6

SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_index
WHERE indrelid = 'users'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

十、总结

B-tree、GIN、GiST、BRIN 解决的是不同问题。

简表如下：

1
2
3
4
5
6

索引类型   核心思想                         适合场景
----------------------------------------------------------------------
B-tree     按字段值有序组织                 等值、范围、排序、唯一约束
GIN        token -> TID 集合                数组、JSONB、全文搜索
GiST       搜索树节点保存范围/空间摘要       范围、空间、相似、最近邻
BRIN       block range -> 摘要              超大表、时间序、物理顺序相关

一句话记忆：

1
2
3
4

B-tree 像字典目录，按值排序查找。
GIN 像搜索引擎倒排表，按 token 找行。
GiST 像地图区域树，按可能相交的区域逐层搜索。
BRIN 像给厚账本每 100 页贴一个摘要标签，先判断哪几段页可能有数据。

生产中最重要的不是记住“哪个索引高级”，而是判断：

  • 查询条件是什么。
  • 字段值是否可排序。
  • 是否是数组、JSONB、全文搜索。
  • 是否需要空间、范围、相似、最近邻。
  • 数据物理顺序是否和查询字段相关。
  • 命中行比例是否足够低。
  • 写入和维护成本是否能接受。
  • 索引大小是否可控。

索引的本质不是越多越好，而是让 PostgreSQL 用更低成本找到更少、更准确的数据。