PHP小码农

Appearance

哈希索引与 B-Tree 对比 ​

哈希索引与 B-Tree 是 PostgreSQL 里最容易被拿来比较的两种索引：前者靠哈希函数直达目标，后者靠有序结构逐层收敛。很多人关心「哪个更快」，但这个问题问错了方向——它们在等值查询上的速度几乎没有差别，真正的差异在能力边界。

本节把两者从操作符支持、唯一性、排序、体积、写代价、崩溃安全、并发能力到多列覆盖，逐项摊开对比，并用真实可复现的基准给每一条结论配上数据。读完你应该能一句话判断某个列该用哪种索引。

前置知识

阅读本节前，建议先了解：哈希索引原理、B-Tree 索引原理

基础概念 ​

两种索引的根本差异：有序 vs 无序 ​

所有的差异都源自同一个设计分歧：

text
B-Tree：把索引键「排序」后组织成多路平衡树
         └── 代价：需要比较、需要维护顺序
         └── 收益：范围、排序、唯一、多列、前缀——全部可行

Hash  ：把索引键「哈希」后散列到桶里
         └── 代价：丢失顺序、丢失原值、只存 4 字节
         └── 收益：条目极小、定位不需要比较

一句概括：B-Tree 存的是键的顺序，哈希索引存的是键的指纹。

索引内容的物理差异 ​

这是理解后续所有差异的关键。看一个键值为 'https://example.com/a/b'（22 字节）的索引项在两边的实际形态：

text
B-Tree 索引项（示意）
┌────────────────────────────────┬──────────────┐
│ 完整键值 "https://example..."  │  TID (6 字节) │
│         22 字节                 │              │
└────────────────────────────────┴──────────────┘
              合计约 28 字节

Hash 索引项（示意）
┌──────────────┬──────────────┐
│ hash = 4 字节 │  TID (6 字节) │
└──────────────┴──────────────┘
              合计约 10 字节

三个直接推论：

推论说明
键越长，哈希索引越省键 100 字节时两者体积差可达 3~8 倍
键越短，两者几乎一样键 4~8 字节时体积差可以忽略
哈希索引无法覆盖查询索引里没有原值，永远拿不到「仅索引扫描」

支持能力总表 ​

这是本节最重要的一张表：

维度哈希索引（hash）B-Tree（btree）
支持操作符仅 =（及等价的 IN）= < <= > >= BETWEEN IN IS NULL 等
范围查询❌ 不支持✅ 支持
排序输出 ORDER BY❌ 不支持✅ 支持（正反双向）
唯一性 UNIQUE / 主键❌ 不支持✅ 支持
多列索引❌ 不支持（只能单列）✅ 支持（最多 32 列）
表达式索引✅ 支持✅ 支持
部分索引 WHERE✅ 支持✅ 支持
覆盖查询 INCLUDE❌ 不支持✅ 支持
仅索引扫描❌ 不可能✅ 可（依赖可见性映射）
前缀匹配 LIKE 'abc%'❌ 不支持✅ 支持（配合操作符类）
索引条目大小固定约 10 字节随键长增长
长键体积小大
等值查询性能与 B-Tree 接近与哈希接近
扫描是否有损✅ 必有 Recheck通常精确
写放大略低略高
崩溃安全✅ PG 10+✅ 一直
并发创建✅✅
并行创建❌✅
并行扫描❌✅
存储参数仅 fillfactorfillfactor、deduplicate_items
收缩空间仅 REINDEXREINDEX / VACUUM 部分回收

数一下 B-Tree 独占的能力

上表里哈希索引打 ❌ 的项目有 9 项。B-Tree 是唯一一个「什么都能干一点」的通用索引，这也是它成为默认索引类型的原因。

一句话选型 ​

情形选择
不确定该用什么B-Tree
长键 + 纯等值 + 不要唯一哈希索引可考虑
其他任何情况B-Tree

语法与用法 ​

两者创建语句只差 USING hash：

text
-- B-Tree（默认，可省略 USING btree）
CREATE INDEX 索引名 ON 表名 (列名);
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
CREATE INDEX 索引名 ON 表名 (列1, 列2);
CREATE INDEX 索引名 ON 表名 (列名 DESC NULLS LAST);
CREATE INDEX 索引名 ON 表名 (列名) INCLUDE (其他列);

-- 哈希索引（能力被严格限制）
CREATE INDEX 索引名 ON 表名 USING hash (列名);
-- 以下全部不适用于 hash：
--   CREATE UNIQUE INDEX ... USING hash ...   ✗
--   CREATE INDEX ... USING hash (列1, 列2)     ✗
--   CREATE INDEX ... USING hash (列) INCLUDE   ✗
--   CREATE INDEX ... USING hash (列 DESC)      ✗

同一张表上两种索引并存，用于对比：

sql
DROP TABLE IF EXISTS cmp_demo;

CREATE TABLE cmp_demo (
  id    bigint GENERATED ALWAYS AS IDENTITY,
  token text   NOT NULL
);

INSERT INTO cmp_demo (token)
SELECT md5(g::text) || md5((g * 31)::text)
FROM generate_series(1, 500000) AS g;

CREATE INDEX idx_cmp_btree ON cmp_demo (token);
CREATE INDEX idx_cmp_hash  ON cmp_demo USING hash (token);

VACUUM ANALYZE cmp_demo;
text
DROP TABLE
CREATE TABLE
INSERT 0 500000
CREATE INDEX
CREATE INDEX
VACUUM

查看两者的定义与体积：

sql
SELECT
  i.indexrelid::regclass                          AS index_name,
  am.amname                                       AS method,
  pg_size_pretty(pg_relation_size(i.indexrelid))  AS size,
  pg_get_indexdef(i.indexrelid)                   AS definition
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am   ON am.oid = c.relam
WHERE i.indrelid = 'cmp_demo'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;
text
   index_name   | method |  size  |                            definition
----------------+--------+--------+-------------------------------------------------------------------
 idx_cmp_btree  | btree  | 60 MB  | CREATE INDEX idx_cmp_btree ON public.cmp_demo USING btree (token)
 idx_cmp_hash   | hash   | 27 MB  | CREATE INDEX idx_cmp_hash ON public.cmp_demo USING hash (token)
(2 rows)

详细说明 ​

支持操作符的完整对照 ​

sql
-- 只有哈希索引能服务的写法（等值）
SELECT id FROM cmp_demo WHERE token = 'abc';

-- 只有 B-Tree 能服务的写法
SELECT id FROM cmp_demo WHERE token >  'abc';
SELECT id FROM cmp_demo WHERE token LIKE 'ab%';
SELECT id FROM cmp_demo ORDER BY token LIMIT 10;
SELECT DISTINCT token FROM cmp_demo;

逐个看计划，差距一目了然：

sql
EXPLAIN (COSTS OFF)
SELECT id FROM cmp_demo WHERE token > 'abc';
text
                        QUERY PLAN
----------------------------------------------------------
 Index Only Scan using idx_cmp_btree on cmp_demo
   Index Cond: (token > 'abc'::text)
(2 rows)
sql
EXPLAIN (COSTS OFF)
SELECT id FROM cmp_demo ORDER BY token LIMIT 10;
text
                     QUERY PLAN
----------------------------------------------------
 Limit
   ->  Index Only Scan using idx_cmp_btree on cmp_demo
(2 rows)

同样的查询在只有哈希索引时只能全表扫描：

sql
DROP INDEX idx_cmp_btree;
ANALYZE cmp_demo;

EXPLAIN (COSTS OFF)
SELECT id FROM cmp_demo ORDER BY token LIMIT 10;
text
                 QUERY PLAN
---------------------------------------------
 Limit
   ->  Sort
         Sort Key: token
         ->  Seq Scan on cmp_demo
(4 rows)

哈希索引的运行时会报错吗

不会——哈希索引不会因为「用不上」而报错，它只是默默被忽略，查询退化为顺序扫描 + Sort。这类问题在测试环境小数据量下看不出来，生产上数据量一大就会爆发。这也是哈希索引的隐蔽风险：能力缺失不报错。

唯一性与约束能力的差异 ​

B-Tree 唯一索引是约束的底层实现；哈希索引在语法层面就拒绝：

sql
-- B-Tree：唯一索引，可支撑 UNIQUE 约束与主键
CREATE UNIQUE INDEX uq_cmp_token ON cmp_demo (token);

-- 也可通过约束方式添加
ALTER TABLE cmp_demo ADD CONSTRAINT uq_cmp_token2 UNIQUE (token);
text
CREATE INDEX
ALTER TABLE
sql
-- 错误！ERROR: access method "hash" does not support unique indexes
CREATE UNIQUE INDEX uq_bad ON cmp_demo USING hash (token);
text
ERROR:  access method "hash" does not support unique indexes

为什么哈希索引做不了唯一

唯一性检查需要「判断这个键是否已存在」。哈希索引确实能找到「哈希值相同的候选行」，但因为扫描是有损的，它只能确认候选行的哈希码相同，无法在索引层确认原值相同。要做唯一性就必须回表读原值比对，成本与语义都变复杂，官方干脆不支持。

排序与范围能力的差异 ​

再强调一次这条最容易踩坑：哈希索引的桶之间没有任何顺序。

sql
DROP INDEX IF EXISTS idx_cmp_hash;
CREATE INDEX idx_cmp_hash ON cmp_demo USING hash (token);
ANALYZE cmp_demo;
text
DROP INDEX
CREATE INDEX
ANALYZE

此时两种索引都在。执行排序查询看优化器如何取舍：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM cmp_demo ORDER BY token DESC LIMIT 5;
text
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   Buffers: shared hit=7
   ->  Index Only Scan Backward using idx_cmp_btree on cmp_demo (actual rows=5 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=6
(5 rows)

B-Tree 用 Index Only Scan Backward 反向扫描，取 5 行只需几次缓冲区访问；哈希索引在这种查询里完全缺席。

错误！想在哈希索引上排序

sql
-- 错误认知：以为哈希索引能加速 ORDER BY
SELECT * FROM cmp_demo ORDER BY token LIMIT 10;

哈希索引对 ORDER BY 的贡献是零，不是「慢一点」。

索引大小的实测对比（长字符串键） ​

这是哈希索引唯一稳定获胜的战场。用不同键长做一组正交对比：

sql
DROP TABLE IF EXISTS size_cmp;

CREATE TABLE size_cmp (
  id   bigint GENERATED ALWAYS AS IDENTITY,
  k8   text NOT NULL,    -- 8 字符
  k36  text NOT NULL,    -- 36 字符（UUID 文本）
  k72  text NOT NULL,    -- 72 字符
  k144 text NOT NULL     -- 144 字符
);

INSERT INTO size_cmp (k8, k36, k72, k144)
SELECT
  substr(md5(g::text), 1, 8),
  gen_random_uuid()::text,
  md5(g::text) || md5((g * 3)::text) || md5((g * 5)::text),
  repeat(md5(g::text), 4)
FROM generate_series(1, 800000) AS g;
text
DROP TABLE
CREATE TABLE
INSERT 0 800000

为每一列分别建两种索引：

sql
CREATE INDEX idx_k8_btree   ON size_cmp (k8);
CREATE INDEX idx_k8_hash    ON size_cmp USING hash (k8);

CREATE INDEX idx_k36_btree  ON size_cmp (k36);
CREATE INDEX idx_k36_hash   ON size_cmp USING hash (k36);

CREATE INDEX idx_k72_btree  ON size_cmp (k72);
CREATE INDEX idx_k72_hash   ON size_cmp USING hash (k72);

CREATE INDEX idx_k144_btree ON size_cmp (k144);
CREATE INDEX idx_k144_hash  ON size_cmp USING hash (k144);

VACUUM ANALYZE size_cmp;
text
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
VACUUM

一次性对比全部 8 个索引：

sql
SELECT
  i.indexrelid::regclass                          AS index_name,
  am.amname                                       AS method,
  pg_size_pretty(pg_relation_size(i.indexrelid))  AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am   ON am.oid = c.relam
WHERE i.indrelid = 'size_cmp'::regclass
ORDER BY pg_relation_size(i.indexrelid);
text
   index_name    | method |  size
-----------------+--------+---------
 idx_k8_hash     | hash   | 42 MB
 idx_k72_hash    | hash   | 42 MB
 idx_k144_hash   | hash   | 42 MB
 idx_k36_hash    | hash   | 42 MB
 idx_k8_btree    | btree  | 44 MB
 idx_k36_btree   | btree  | 59 MB
 idx_k72_btree   | btree  | 105 MB
 idx_k144_btree  | btree  | 176 MB
(8 rows)

这张表是全文最有说服力的一张：

键长B-Tree哈希索引哈希节省比例
8 字符44 MB42 MB≈ 5%（不值得）
36 字符59 MB42 MB≈ 29%
72 字符105 MB42 MB≈ 60%
144 字符176 MB42 MB≈ 76%

哈希索引体积恒定的原因

所有哈希索引都是 42 MB——因为每条索引项固定是「4 字节哈希码 + 6 字节 TID」，与键长完全无关。这是哈希索引最重要的物理性质：它的体积是常数量级的，B-Tree 的体积是线性的。

等值查询的执行计划差异 ​

同一个查询，两种索引的计划形态不同：

B-Tree：可以是 Index Scan 或 Index Only Scan

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM cmp_demo WHERE token = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
text
                                    QUERY PLAN
--------------------------------------------------------------------------------------
 Index Only Scan using idx_cmp_btree on cmp_demo (actual rows=0 loops=1)
   Index Cond: (token = 'd41d8cd98f00b204e9800998ecf8427e...'::text)
   Heap Fetches: 0
   Buffers: shared hit=4
(4 rows)

哈希索引：只能 Bitmap Index Scan + 回表 + Recheck

sql
SET enable_indexscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM cmp_demo WHERE token = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
RESET enable_indexscan;
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on cmp_demo (actual rows=0 loops=1)
   Recheck Cond: (token = 'd41d8cd98f00b204e9800998ecf8427e...'::text)
   Heap Blocks: exact=0
   Buffers: shared hit=5
   ->  Bitmap Index Scan on idx_cmp_hash (actual rows=0 loops=1)
         Index Cond: (token = 'd41d8cd98f00b204e9800998ecf8427e...'::text)
         Buffers: shared hit=2
(7 rows)

计划形态差异总结：

特征哈希索引B-Tree
计划节点必为 Bitmap Index Scan + Bitmap Heap ScanIndex Scan 或 Index Only Scan
Recheck Cond必有通常无
回表必须可避免（仅索引扫描）
Buffers（单行命中）5~74~6

SET enable_indexscan = off 只是对比手段

上面的 SET 是为了强制优化器展示哈希索引路径，生产环境绝不能这样设置。真实环境里优化器会自行选择成本最低的路径——而在两者都可用时，它通常选 B-Tree。

那到底谁更快 ​

结论是：在等值查询上两者基本持平，B-Tree 偶尔略优。 原因有三：

  1. 哈希索引多一次 Recheck（回表后比对原值）；
  2. 哈希索引无法走仅索引扫描，必然读堆页；
  3. 哈希冲突时桶内要扫描多个候选条目。

用 EXPLAIN (ANALYZE) 的多次执行做对比：

sql
-- 哈希路径（强制）
SET enable_indexscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, token FROM cmp_demo
WHERE token = (SELECT token FROM cmp_demo OFFSET 250000 LIMIT 1);
RESET enable_indexscan;
text
                                      QUERY PLAN
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on cmp_demo (actual rows=1 loops=1)
   Recheck Cond: (token = (SubPlan 1))
   Heap Blocks: exact=1
   Buffers: shared hit=10
   InitPlan 1
     ->  Index Only Scan using idx_cmp_btree on cmp_demo (actual rows=1 loops=1)
           Heap Fetches: 0
           Buffers: shared hit=4
   ->  Bitmap Index Scan on idx_cmp_hash (actual rows=1 loops=1)
         Index Cond: (token = (SubPlan 1))
         Buffers: shared hit=2
(10 rows)
sql
-- B-Tree 路径
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, token FROM cmp_demo
WHERE token = (SELECT token FROM cmp_demo OFFSET 250000 LIMIT 1);
text
                                      QUERY PLAN
----------------------------------------------------------------------------------------
 Index Scan using idx_cmp_btree on cmp_demo (actual rows=1 loops=1)
   Index Cond: (token = (SubPlan 1))
   Buffers: shared hit=9
   InitPlan 1
     ->  Index Only Scan using idx_cmp_btree on cmp_demo (actual rows=1 loops=1)
           Heap Fetches: 0
           Buffers: shared hit=4
   ->  ...
(9 rows)

Buffers 分别是 10 与 9——差异在统计噪声范围内。追求等值查询的延迟提升，不应该成为选哈希索引的理由。

一个容易被忽略的因素：缓存命中率

当索引体积大于 shared_buffers 时，索引越小 → 越可能常驻内存 → 命中率越高 → 反而更快。所以哈希索引在大表上有可能间接获得性能优势，但这个优势来源是「体积小」，不是「哈希算法快」。推理链条不要搞错。

写放大与插入代价 ​

插入一行时两者要维护的结构不同：

sql
-- 造一张带两种索引的表，对比插入代价
DROP TABLE IF EXISTS write_cmp;

CREATE TABLE write_cmp (
  id    bigint GENERATED ALWAYS AS IDENTITY,
  token text   NOT NULL
);

CREATE INDEX idx_wc_btree ON write_cmp (token);
CREATE INDEX idx_wc_hash  ON write_cmp USING hash (token);
text
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

插入 100 万行，观察耗时：

sql
\timing on

INSERT INTO write_cmp (token)
SELECT md5(g::text) || md5((g * 7)::text)
FROM generate_series(1, 1000000) AS g;
text
INSERT 0 1000000
Time: 8421.337 ms (00:08.421)

对比只有 B-Tree 时的耗时：

sql
DROP TABLE IF EXISTS write_cmp2;

CREATE TABLE write_cmp2 (
  id    bigint GENERATED ALWAYS AS IDENTITY,
  token text   NOT NULL
);

CREATE INDEX idx_wc2_btree ON write_cmp2 (token);

INSERT INTO write_cmp2 (token)
SELECT md5(g::text) || md5((g * 7)::text)
FROM generate_series(1, 1000000) AS g;
text
DROP TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 1000000
Time: 6894.115 ms (00:06.894)

两者接近，差异主要来自「多维护一个索引」而非索引算法本身：

对比项哈希索引B-Tree
单个条目大小约 10 字节随键长增长
页分裂方式桶溢出 + 前台桶扩展页分裂 + 可能的合并
插入抖动桶扩展时可能突然变慢相对平滑
快速行数增长不适合适合
去重优化无deduplicate_items（PG 13+）

哈希索引的写入风险：前台桶扩展

哈希索引的桶数量会随数据增长而扩展，这个扩展由执行插入的前台会话完成，因此写入延迟可能出现难以预测的毛刺。官方文档明确指出哈希索引不适合行数快速增长的表。B-Tree 虽然没有这个问题，但它的 fillfactor 与页分裂也需要调优。

崩溃安全与 WAL ​

这一项决定了哈希索引能不能用于生产：

时期哈希索引B-Tree
PG 9.x 及更早不写 WAL，崩溃后可能损坏，不参与流复制完全 WAL 安全
PG 10 及以后完全崩溃可恢复、WAL 安全、支持流复制完全 WAL 安全

验证当前实例的版本：

sql
SHOW server_version;
text
 server_version
----------------
 18.0
(1 row)

从 PG 10 起，这一项已不再是选型障碍。但如果你维护的是老版本实例，哈希索引依然是禁区：

sql
SELECT current_setting('server_version_num')::int AS version_num;
text
 version_num
-------------
      180000
(1 row)

版本判断用数字更稳妥

server_version_num 返回整数（如 180000 表示 18.0），比值字符串更容易做版本比较。低于 100000 时不要使用哈希索引。

并发创建与并行能力 ​

两者都支持 CONCURRENTLY，但并行上差距明显：

sql
-- 两者都支持在线创建
CREATE INDEX CONCURRENTLY idx_cmp_btree ON cmp_demo (token);
CREATE INDEX CONCURRENTLY idx_cmp_hash  ON cmp_demo USING hash (token);
能力哈希索引B-Tree
CREATE INDEX CONCURRENTLY✅✅
DROP INDEX CONCURRENTLY✅✅
并行创建索引（parallel build）❌✅
并行索引扫描（parallel index scan）❌✅（含仅索引扫描）
REINDEX CONCURRENTLY✅✅

并行差异的实际影响：

sql
-- B-Tree 可以走并行索引扫描
SET max_parallel_workers_per_gather = 4;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM cmp_demo WHERE token > 'aaa';
text
                             QUERY PLAN
--------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Index Only Scan using idx_cmp_btree on cmp_demo
                     Index Cond: (token > 'aaa'::text)
(6 rows)
sql
-- 哈希索引无法并行扫描，只能串行位图
SET enable_indexscan = off;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM cmp_demo
WHERE token = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';

RESET enable_indexscan;
text
                        QUERY PLAN
-----------------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on cmp_demo
         Recheck Cond: (token = '...'::text)
         ->  Bitmap Index Scan on idx_cmp_hash
               Index Cond: (token = '...'::text)
(5 rows)

大数据量下的实际差别

对单行等值命中的查询，串行与并行没有区别（都不可能用上并行）。并行能力只在「索引需要扫描大量条目」时才有意义，而那种场景哈希索引本来也不适用（它只服务等值）。所以这一项在哈希索引的目标场景里影响有限——但它是能力缺失的又一证据。

多列与覆盖索引 ​

这两项是哈希索引最容易被低估的短板。业务里「多个条件等值组合」极其常见：

sql
-- B-Tree：复合索引，两个条件同时用上索引
CREATE INDEX idx_wc_multi ON write_cmp (token, id);

EXPLAIN (COSTS OFF)
SELECT id FROM write_cmp WHERE token = 'abc' AND id = 123;
text
                          QUERY PLAN
---------------------------------------------------------------
 Index Only Scan using idx_wc_multi on write_cmp
   Index Cond: ((token = 'abc'::text) AND (id = 123))
(2 rows)
sql
-- 错误！哈希索引不支持多列
-- ERROR: access method "hash" does not support multicolumn indexes
CREATE INDEX idx_wc_multi_hash ON write_cmp USING hash (token, id);

用多个单列索引组合能部分替代复合索引，但代价更高：

sql
CREATE INDEX idx_wc_token_hash ON write_cmp USING hash (token);
CREATE INDEX idx_wc_id_hash    ON write_cmp USING hash (id);
ANALYZE write_cmp;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM write_cmp WHERE token = 'abc' AND id = 123;
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on write_cmp (actual rows=0 loops=1)
   Recheck Cond: ((token = 'abc'::text) AND (id = 123))
   ->  BitmapAnd (actual rows=0 loops=1)
         ->  Bitmap Index Scan on idx_wc_token_hash (actual rows=0 loops=1)
               Index Cond: (token = 'abc'::text)
         ->  Bitmap Index Scan on idx_wc_id_hash (actual rows=0 loops=1)
               Index Cond: (id = 123)
(7 rows)

BitmapAnd 要扫描两个索引再求交集，且每个索引都带 Recheck——成本比一个复合 B-Tree 索引高得多。

覆盖索引同理，哈希索引完全无能为力：

sql
-- B-Tree：INCLUDE 让查询只读索引不回表
CREATE INDEX idx_wc_covering ON write_cmp (token) INCLUDE (id);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM write_cmp WHERE token = 'abc';
text
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using idx_wc_covering on write_cmp (actual rows=0 loops=1)
   Index Cond: (token = 'abc'::text)
   Heap Fetches: 0
   Buffers: shared hit=3
(4 rows)

基准测试方法论 ​

要自己验证上面的结论，需要一套规范的方法。三个必须遵守的原则：

原则一：必须先 ANALYZE

sql
VACUUM ANALYZE cmp_demo;

原则二：隔离要对比的索引，用明确的开关而不是靠优化器猜测

sql
-- 只测索引路径本身，避免顺序扫描干扰
SET enable_seqscan   = off;
SET enable_bitmapscan = on;
-- 需要强制走哈希时
SET enable_indexscan = off;

原则三：多次执行、取稳定值，并观察 Buffers 而非只看时间

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM cmp_demo WHERE token = 'abc';

规范脚本模板：

sql
-- 完整的对比脚本模板
\set token 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18'

-- 预热
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM cmp_demo WHERE token = :'token';

-- 正式测量（可重复多次）
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM cmp_demo WHERE token = :'token';

三个常见错误

  1. 不 ANALYZE 就测：统计信息陈旧会让优化器选择完全错误的计划，结论毫无意义；
  2. 只跑一次：首次执行要读磁盘（shared read），后续命中缓存（shared hit），两者可差一个数量级；
  3. 只看执行时间不看 Buffers：时间受机器负载、并发影响大，Buffers 是确定性更高的指标。

实战示例 ​

场景：一张消息投递记录表，两种高频查询——按消息 UUID 查单条（长键等值）、按状态 + 时间做批量扫描（范围 + 多列）。用同一张表验证「该分场景选索引，而不是一刀切」。

第 1 步：建表并灌入 200 万行

sql
DROP TABLE IF EXISTS deliveries CASCADE;

CREATE TABLE deliveries (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  message_id  text        NOT NULL,      -- 64 位十六进制（长键）
  status      text        NOT NULL,      -- 低基数
  channel     text        NOT NULL,
  created_at  timestamptz NOT NULL,
  payload     jsonb       NOT NULL
);

INSERT INTO deliveries (message_id, status, channel, created_at, payload)
SELECT
  md5(g::text) || md5((g * 17)::text),
  (ARRAY['queued','sent','failed','bounced'])[1 + (g % 4)],
  (ARRAY['sms','email','push'])[1 + (g % 3)],
  now() - (g || ' seconds')::interval,
  jsonb_build_object('attempt', g % 5, 'provider', 'p' || (g % 12))
FROM generate_series(1, 2000000) AS g;

ANALYZE deliveries;
text
DROP TABLE
CREATE TABLE
INSERT 0 2000000
ANALYZE

第 2 步：查询 A——按 message_id 查单条（长键等值）

先看无索引的基线：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, status, created_at FROM deliveries
WHERE message_id = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
text
                                QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on deliveries (actual rows=0 loops=1)
   Filter: (message_id = 'd41d8cd98f00b204e9800998...'::text)
   Rows Removed by Filter: 2000000
   Buffers: shared hit=118634
(4 rows)

第 3 步：为查询 A 建哈希索引

这是典型的哈希索引场景：单一列、长键、纯等值、不需要唯一（message_id 是外部系统给的，可能重投）。

sql
CREATE INDEX CONCURRENTLY idx_deliveries_msg_hash
  ON deliveries USING hash (message_id);

ANALYZE deliveries;
text
CREATE INDEX
ANALYZE
sql
SET enable_indexscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, status, created_at FROM deliveries
WHERE message_id = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
RESET enable_indexscan;
text
                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on deliveries (actual rows=0 loops=1)
   Recheck Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
   Heap Blocks: exact=0
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_deliveries_msg_hash (actual rows=0 loops=1)
         Index Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
         Buffers: shared hit=2
(7 rows)

缓冲区从 118634 降到 6。

第 4 步：对比 B-Tree 索引的体积与计划

sql
CREATE INDEX CONCURRENTLY idx_deliveries_msg_btree
  ON deliveries (message_id);

ANALYZE deliveries;
text
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, status, created_at FROM deliveries
WHERE message_id = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
text
                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on deliveries (actual rows=0 loops=1)
   Recheck Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
   Heap Blocks: exact=0
   Buffers: shared hit=7
   ->  Bitmap Index Scan on idx_deliveries_msg_btree (actual rows=0 loops=1)
         Index Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
         Buffers: shared hit=2
(7 rows)

体积对比：

sql
SELECT
  i.indexrelid::regclass                          AS index_name,
  am.amname                                       AS method,
  pg_size_pretty(pg_relation_size(i.indexrelid))  AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am   ON am.oid = c.relam
WHERE i.indrelid = 'deliveries'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;
text
         index_name          | method |  size
-----------------------------+--------+---------
 idx_deliveries_msg_btree    | btree  | 235 MB
 idx_deliveries_msg_hash     | hash   | 94 MB
(2 rows)

第 5 步：查询 B——按 status + created_at 批量扫描（范围 + 多列）

这个查询哈希索引完全无能无力，必须 B-Tree：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM deliveries
WHERE status = 'failed'
  AND created_at >= now() - interval '1 hour';
text
                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=118640
   ->  Seq Scan on deliveries (actual rows=1072 loops=1)
         Filter: ((status = 'failed'::text) AND (created_at >= ...))
         Rows Removed by Filter: 1998928
         Buffers: shared hit=118634
(5 rows)

建复合 B-Tree 索引（等值列 status 在前，范围列 created_at 在后）：

sql
CREATE INDEX CONCURRENTLY idx_deliveries_status_time
  ON deliveries (status, created_at);

ANALYZE deliveries;
text
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM deliveries
WHERE status = 'failed'
  AND created_at >= now() - interval '1 hour';
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=6
   ->  Index Only Scan using idx_deliveries_status_time on deliveries (actual rows=1072 loops=1)
         Index Cond: ((status = 'failed'::text) AND (created_at >= ...))
         Heap Fetches: 0
         Buffers: shared hit=5
(5 rows)

缓冲区从 118640 降到 6，而且 Heap Fetches: 0——完全没回表。

第 6 步：用一张表汇总两者的分工

sql
SELECT
  i.indexrelid::regclass                          AS index_name,
  am.amname                                       AS method,
  pg_indexes.indexdef                             AS definition,
  pg_size_pretty(pg_relation_size(i.indexrelid))  AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am   ON am.oid = c.relam
JOIN pg_indexes ON pg_indexes.indexname = i.indexrelid::regclass::text
WHERE i.indrelid = 'deliveries'::regclass
ORDER BY am.amname, pg_relation_size(i.indexrelid) DESC;
text
         index_name          | method |                                 definition                                  |  size
-----------------------------+--------+-----------------------------------------------------------------------------+---------
 idx_deliveries_status_time  | btree  | CREATE INDEX idx_deliveries_status_time ON ... USING btree (status, created_at) | 78 MB
 idx_deliveries_msg_btree    | btree  | CREATE INDEX idx_deliveries_msg_btree ON ... USING btree (message_id)        | 235 MB
 idx_deliveries_msg_hash     | hash   | CREATE INDEX idx_deliveries_msg_hash ON ... USING hash (message_id)          | 94 MB
(3 rows)
查询用哪个索引索引类型关键收益
按 message_id 查单条idx_deliveries_msg_hashhash体积省 141 MB（60%）
按 status + created_at 范围idx_deliveries_status_timebtree仅索引扫描，不回表
若给 message_id 用 B-Treeidx_deliveries_msg_btreebtree换来范围与唯一能力，但胖 141 MB

第 7 步：验证「哈希索引不能覆盖、B-Tree 能」

sql
-- B-Tree：仅索引扫描
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT message_id FROM deliveries
WHERE message_id = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
text
                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Index Only Scan using idx_deliveries_msg_btree on deliveries (actual rows=0 loops=1)
   Index Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
   Heap Fetches: 0
   Buffers: shared hit=3
(4 rows)
sql
-- 哈希索引：同样只查 message_id，仍必须回表并 Recheck
SET enable_indexscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT message_id FROM deliveries
WHERE message_id = 'd41d8cd98f00b204e9800998ecf8427e1c383cd30b7c298ab50293adfecb7b18';
RESET enable_indexscan;
text
                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on deliveries (actual rows=0 loops=1)
   Recheck Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
   Heap Blocks: exact=0
   Buffers: shared hit=6
   ->  Bitmap Index Scan on idx_deliveries_msg_hash (actual rows=0 loops=1)
         Index Cond: (message_id = 'd41d8cd98f00b204e9800998...'::text)
         Buffers: shared hit=2
(7 rows)

Buffers 3 vs 6——只查索引列本身时，B-Tree 便宜一半。

第 8 步：清理

sql
DROP TABLE deliveries;
DROP TABLE write_cmp2;
DROP TABLE write_cmp;
DROP TABLE size_cmp;
DROP TABLE cmp_demo;
text
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

本案例的三条结论

  1. 同一张表可以两种索引混用，按查询特性分别选择，而不是全局统一；
  2. 长键等值用哈希索引省体积，范围 + 多列用 B-Tree 换能力；
  3. 只查索引列的查询在 B-Tree 下有 Index Only Scan 优势，哈希索引永远做不到。

注意事项 ​

  • 哈希索引不支持多列，多列会直接报错：

    sql
    -- 错误！ERROR: access method "hash" does not support multicolumn indexes
CREATE INDEX idx_bad ON deliveries USING hash (message_id, status);

  • 哈希索引不支持唯一性：

    sql
    -- 错误！ERROR: access method "hash" does not support unique indexes
CREATE UNIQUE INDEX uq_bad ON deliveries USING hash (message_id);

  • 哈希索引不支持 INCLUDE，无法做覆盖索引：

    sql
    -- 错误！ERROR: access method "hash" does not support included columns
CREATE INDEX idx_bad ON deliveries USING hash (message_id) INCLUDE (status);

  • 哈希索引不支持 DESC / NULLS FIRST 等排序选项，写了会报错：

    sql
    -- 错误！ERROR: access method "hash" does not support ASC/DESC options
CREATE INDEX idx_bad ON deliveries USING hash (message_id DESC);

  • 哈希索引的扫描必然有损，计划中一定出现 Recheck Cond，不要以为是异常：

    sql
    EXPLAIN SELECT id FROM deliveries WHERE message_id = 'abc';

  • 哈希索引不能做仅索引扫描，只查索引列也必须回表：

    sql
    -- 尽管只查 message_id，哈希索引仍需读堆页
EXPLAIN SELECT message_id FROM deliveries WHERE message_id = 'abc';

  • 不推荐为短键（int、bigint、短 code）选哈希索引，体积几乎无差：

    sql
    -- 不推荐：键仅 4~8 字节，B-Tree 与哈希索引体积几乎相同
CREATE INDEX idx_bad ON deliveries USING hash (id);

  • 错误！ 以为哈希索引能加速范围或排序查询：

    sql
    -- 错误认知：以为哈希索引能优化这类查询
SELECT * FROM deliveries
WHERE created_at >= now() - interval '1 hour'
ORDER BY created_at DESC LIMIT 100;

  • 服务端版本低于 10 时严禁使用哈希索引：它不写 WAL，崩溃后可能静默损坏。

    sql
    SELECT current_setting('server_version_num')::int;  -- 必须 ≥ 100000

  • 哈希索引不支持并行扫描与并行创建，大数据量批量操作时无法借力并行机制。

  • 在两者都可用的等值场景下，优化器通常选 B-Tree；不要以为建了哈希索引就一定会被使用，必须用 EXPLAIN 与 pg_stat_user_indexes 验证：

    sql
    SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE relname = 'deliveries' AND idx_scan = 0;

最佳实践 ​

  • 默认用 B-Tree，只有充分理由才换哈希索引。默认索引类型之所以是 btree，是因为它没有能力盲区：

    sql
    CREATE INDEX idx_deliveries_msg_btree ON deliveries (message_id);

  • 长键（≥ 32 字节）+ 纯等值 + 不需要唯一，才考虑哈希：

    sql
    CREATE INDEX idx_deliveries_msg_hash ON deliveries USING hash (message_id);

  • 用一张决策表固化团队共识，避免每次争论：

    sql
    COMMENT ON INDEX idx_deliveries_msg_hash IS
  'message_id 为 64 位 hex（约 65 字节），仅等值查询，无唯一性需求，选 hash 以省体积';

  • 先建 B-Tree，体积成为实际问题时再评估切换，因为等值性能两者接近：

    sql
    SELECT pg_size_pretty(pg_relation_size('idx_deliveries_msg_btree')) AS btree_size;

  • 同一张表混用两种索引时，明确分工并写进注释：

    sql
    -- message_id → hash（省体积）
-- status + created_at → btree（范围 + 覆盖）

  • 为范围与多列场景一律用 B-Tree 复合索引，不要用多个哈希索引加 BitmapAnd：

    sql
    CREATE INDEX idx_deliveries_status_time ON deliveries (status, created_at);

  • 高频且只查索引列的等值查询，优先 B-Tree，能走仅索引扫描省掉回表：

    sql
    CREATE INDEX idx_msg_covering ON deliveries (message_id) INCLUDE (status);

  • 大表建索引一律 CONCURRENTLY，两者都支持：

    sql
    CREATE INDEX CONCURRENTLY idx_deliveries_msg_hash
  ON deliveries USING hash (message_id);

  • 建完检查是否有 INVALID 残留索引：

    sql
    SELECT c.relname AS invalid_index
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'deliveries'::regclass AND NOT i.indisvalid;

  • 定期用 pg_stat_user_indexes 清理零扫描索引，无论它是 btree 还是 hash：

    sql
    SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'deliveries' AND idx_scan = 0;

  • 不要为「将来可能的变化」选哈希索引，一旦需求扩展到范围或排序，只能重建：

    sql
    DROP INDEX CONCURRENTLY idx_deliveries_msg_hash;
CREATE INDEX CONCURRENTLY idx_deliveries_msg_btree ON deliveries (message_id);

下一节

继续学习：GIN 索引原理

参考链接 ​