跳转到主要内容

9.1 索引方法与操作符类

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

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

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

可查询当前环境的 operator class：

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 的适用查询

B-tree：默认不是偶然

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

<  <=  =  >=  >

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

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 单列索引。多列混合顺序才有区别：

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，只处理简单等值：

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 与空间、范围、近邻问题

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 示例：

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：

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 谁更好不能由“空间数据”四字决定。要对照：

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、文本检索

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

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

数组：

CREATE INDEX article_tags_gin_idx
ON article USING gin (tags);

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

全文检索：

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：

-- 默认，支持更广的 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 的扩展边界

BRIN：索引 block range summary

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

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 行范围：

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：

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 和数据布局。

返回本章目录 · 下一节：从谓词、连接与排序推导索引 · 查看全书目录 · 查看索引中心

最后修改 2026年8月28日 · chore: remove GitHub Pages workflow (83aa397)