# 索引方法与操作符类

LLMS 索引： [llms.txt](/llms.txt)

---

判断一个 clause 能否使用索引，要同时回答：

```text
index access method
  + indexed data type/expression
  + operator class/family
  + query operator
  + collation/order/predicate
```

“这个列有索引”不够。`payload @> ...`、`name LIKE ...`、`point <-> ...` 分别需要与其操作符语义匹配的 operator class；同一数据类型也可能有不止一种索引语义。

可查询当前环境的 operator class：

```sql
SELECT
    am.amname AS index_method,
    opc.opcname AS opclass_name,
    opc.opcintype::regtype AS indexed_type,
    opc.opcdefault AS is_default
FROM pg_am AS am
JOIN pg_opclass AS opc
  ON opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;
```

扩展可新增 type、operator 与 opclass，所以最终答案来自目标环境 catalog 和扩展文档，而不是一张静态“索引类型速查表”。

## 9.1.1 B-tree 与 Hash 的适用查询 {#item-9-1-1}

### B-tree：默认不是偶然

B-tree 支持有全序关系的数据，核心 operator 为：

```text
<  <=  =  >=  >
```

`BETWEEN`、`IN`、`IS NULL`/`IS NOT NULL` 等可以转成相应搜索；它还可以按索引顺序输出，支持 uniqueness、多列、expression、partial、`INCLUDE` 与 index-only scan。因此以下 workload 通常先考虑 B-tree：

```sql
WHERE customer_id = $1
WHERE placed_at >= $1 AND placed_at < $2
WHERE customer_id = $1 ORDER BY placed_at DESC LIMIT 20
WHERE lower(email) = lower($1)  -- 前提是表达式/语义匹配
```

单列 B-tree 能正向或反向扫描，所以仅为了 `ORDER BY occurred_at DESC` 通常不必再建一个 DESC 单列索引。多列混合顺序才有区别：

```sql
CREATE INDEX event_tenant_time_idx
ON event (tenant_id ASC, occurred_at DESC);
```

它可以直接提供 `ORDER BY tenant_id ASC, occurred_at DESC`；普通 `(tenant_id, occurred_at)` 的整体反向扫描会得到两列同时反向，不能产生“一升一降”。

前缀 pattern search 要特别看 collation/operator class。`LIKE 'foo%'` 有机会变成范围扫描，`LIKE '%foo'` 不能靠普通 B-tree 从左定位。非 `C` locale 下，可能需要 `text_pattern_ops`/`varchar_pattern_ops`；但 pattern opclass 不替代普通 locale ordering，需要范围比较时可能要保留默认 opclass 索引。不要看到 `LIKE` 就盲目加普通 B-tree。

### Hash：只有等值

Hash index 保存值的 32-bit hash code，只处理简单等值：

```sql
CREATE INDEX session_token_hash_idx
ON session USING hash (token_hash);
```

它不能提供范围、排序、unique、多 key 组合或 `INCLUDE`。PostgreSQL 14–18 的 Hash index 已是 WAL-logged、crash-safe 的正式能力，不应继续引用早期版本“hash index 不可靠”的旧结论；但 B-tree 也能处理 equality，并有更广能力，所以 Hash 需要实测证明 size/cache/lookup 收益，而不是因字段名叫 hash 就选 Hash。

Hash 适合候选的条件通常很窄：

- 只有一个宽值的 equality lookup；
- 不需要排序、范围、unique 或 covering；
- 真实数据与 cache 下比 B-tree 有明确收益；
- hash collision recheck 与额外 heap access 可接受；
- write、WAL、build、backup 与维护成本已比较。

如果 equality 本身已经由 PK/unique B-tree 支持，再建 Hash 多半只是重复成本。

## 9.1.2 GiST、SP-GiST 与空间、范围、近邻问题 {#item-9-1-2}

GiST 和 SP-GiST 都是扩展索引策略的基础设施，不是“一种固定的空间树”。是否可用取决于 operator class。

### GiST

GiST 可承载平衡树式的广义搜索。核心与扩展生态常见：

- 几何/空间 overlap、containment；
- range/multirange overlap 与 containment；
- `btree_gist` 提供 B-tree-like GiST opclass；
- `pg_trgm` 的相似/模糊匹配；
- PostGIS geometry/geography 空间 operator；
- 支持的 opclass 上做 K-nearest-neighbor ordering。

核心 point 示例：

```sql
CREATE INDEX place_location_gist_idx
ON place USING gist (location);

SELECT place_id
FROM place
ORDER BY location <-> point '(101,456)'
LIMIT 10;
```

这里 `<->` 是该 operator class 的 distance ordering operator。把表达式改成未经索引支持的自定义距离函数，GiST 不会因“语义看起来一样”自动使用。

GiST 也用于 exclusion constraint：

```sql
EXCLUDE USING gist (
    room_id WITH =,
    occupied_during WITH &&
);
```

它表达“同一房间的时间范围不得重叠”。这是约束语义，不只是性能；删除此类索引可能破坏 constraint，不能按 `idx_scan` 清理。

### SP-GiST

SP-GiST 支持非平衡、space-partitioned 结构，例如 radix tree、quadtree、k-d tree。常见候选：

- 有前缀/层次分割特征的数据；
- core point/quadtree；
- inet prefix；
- text prefix 与特定 operator class；
- 支持 distance ordering 的 opclass 上做 KNN。

GiST 与 SP-GiST 谁更好不能由“空间数据”四字决定。要对照：

```text
operator/operator class support
data clustering and skew
query predicate and KNN shape
index size/build/update
lossy recheck
concurrency and vacuum
```

第 17 章会用 PostGIS 具体讨论 bounding box、distance、SRID 与 exact recheck；本章只固定索引方法的选择方式。

## 9.1.3 GIN 与数组、JSONB、文本检索 {#item-9-1-3}

GIN 是 inverted index：把一个值拆成多个 component/token，再从 token 找到包含它的行。典型数据：

- array elements；
- `tsvector` lexemes；
- JSONB keys/values/path tokens；
- `pg_trgm` trigrams；
- 扩展定义的可分解值。

数组：

```sql
CREATE INDEX article_tags_gin_idx
ON article USING gin (tags);

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

全文检索：

```sql
CREATE INDEX product_search_gin_idx
ON product USING gin (search_document);

SELECT product_id
FROM product
WHERE search_document @@
      websearch_to_tsquery('simple', 'postgresql observability');
```

查询必须沿用同一 text search configuration 和 document 构造。索引 `to_tsvector('english', title)`、查询 `to_tsvector('simple', title)` 并非同一语义。生产常把 document 做成 stored generated column，使构造、统计与索引合同可见。

JSONB 有两个常用 core GIN opclass：

```sql
-- 默认，支持更广的 key/value/existence 类 operator
CREATE INDEX doc_ops_idx ON doc USING gin (payload);

-- 更紧凑、常适合 @> 与 jsonpath，但能力边界不同
CREATE INDEX doc_path_idx
ON doc USING gin (payload jsonb_path_ops);
```

哪一个更好由实际 operator 与数据决定。给任意大 JSONB 建默认 GIN，可能索引大量从不查询的 token，增加 size、pending list、写入和 vacuum 成本。

GIN 不提供有序输出，也不能 index-only 返回原值，因为 entry 通常只保存 component。结果经常是 Bitmap Index Scan → Bitmap Heap Scan，并对 lossy/候选项 recheck。出现 `Recheck Cond` 是实现证据，不自动表示索引坏。

GIN 的 `fastupdate` 默认把更新先放 pending list，以批量合并摊薄写成本；这可能让个别读或清理出现尖峰。评审需观察 pending-list 行为、autovacuum、写入 burst、index size 和 WAL，而不是只测静态查询。

## 9.1.4 BRIN 与物理相关的大表；Bloom 的扩展边界 {#item-9-1-4}

### BRIN：索引 block range summary

BRIN 不为每行保存精确 key，而为连续 heap block range 保存 min/max 等 summary。因此它依赖“值与物理行顺序相关”：

```sql
CREATE INDEX event_occurred_brin_idx
ON event USING brin (occurred_at)
WITH (pages_per_range = 32, autosummarize = on);
```

适合：

- append-only/mostly append 时间序列；
- 单调增长 ID；
- 极大关系、宽范围查询；
- 能接受 lossy bitmap + heap recheck；
- B-tree 空间/cache/write 成本不划算。

不适合：

- 物理顺序已与值随机化；
- 每次只查极少行且需要精确 point latency；
- range 内大量无关行的 recheck 不可接受；
- 误以为 BRIN 能提供 `ORDER BY`——有序输出仍只有 B-tree。

`pages_per_range` 越大，索引通常越小但 summary 越粗；越小则更精确、索引和维护更大。新页范围还需要 summarization，可用 autosummarize、vacuum 或 BRIN 函数管理。

本章 400000 行按 occurred_at 物理写入。相同 600 行范围：

```text
BRIN bytes=24576
B-tree bytes=9003008
fraction≈0.00273
```

具体字节不是通用阈值；稳定结论是这种分布下 BRIN 以显著更小的空间支持范围，B-tree 的额外精度对声明 workload 不值成本。

BRIN 不是 partitioning。它不会改变 retention、约束、每分区索引或 drop lifecycle；partition pruning 与 BRIN filtering 可以并用，但解决不同层次问题。

### Bloom：contrib extension，不是 core 默认方法

`bloom` 是随 PostgreSQL 提供的 extension access method，需安装扩展后使用。它把多列 equality 特征编码成 lossy signature：

```sql
CREATE EXTENSION bloom;

CREATE INDEX asset_bloom_idx
ON asset USING bloom (tenant_id, region, kind, state);
```

适合“很多列、查询任意 equality 组合、维护所有 B-tree 组合过贵”的特定问题。false positive 必须回 heap recheck；signature 越大，误报少但索引更大。其限制包括：

- core module 只带有限类型 opclass；
- 只支持 equality；
- 不支持 unique；
- 不支持 NULL lookup；
- 不能排序、范围或替代约束。

Bloom 与 BRIN 都可能很小且 lossy，但机制不同：BRIN 按物理 block range summary，Bloom 为每行/索引项保存 signature。选择前先写 query operators 和数据布局。

---

[返回本章目录](../) · [下一节：从谓词、连接与排序推导索引](../02/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)