京公网安备11010502063487号“PostgreSQL 该选 B-tree、Hash、GIN 还是 BRIN” 是建索引时最常见的选择题。这篇在本机自己起一个 PostgreSQL 16.15,在同一张 100 万行的表上逐种建索引,量大小、建索引耗时和查询耗时,并验证每种索引的适用条件。
没有索引:Seq Scan on t,39.88 ms btree:大小 18.8 MB,建索引 0.21 s;Index Scan using t_x on t,0.00 ms hash :大小 32.0 MB,建索引 0.88 s;Index Scan using t_x on t,0.01 ms brin :大小 24 KB,建索引 0.06 s;Bitmap Heap Scan on t,39.77 ms
B-tree 和 Hash 都是亚毫秒,Hash 更大;BRIN 在随机列上等于没有索引。
r 上的 btree索引(18.8 MB):Bitmap Heap Scan on t,0.40 ms r 上的 brin 索引(24 KB):Bitmap Heap Scan on t,43.66 ms id 上的 btree索引(21.4 MB):Index Scan using t_x on t,0.08 ms id 上的 brin 索引(24 KB):Bitmap Heap Scan on t,0.44 ms r 上只有 hash 索引:Seq Scan on t,39.97 ms(Hash 用不上范围查询,退回全表扫描)
Hash 不支持范围;BRIN 只在顺序列上有效,而且小得多。
pages_per_range = 128 :索引 24 KB,Bitmap Heap Scan on t,0.40 ms pages_per_range = 32 :索引 32 KB,Bitmap Heap Scan on t,0.15 ms pages_per_range = 8 :索引 64 KB,Bitmap Heap Scan on t,0.22 ms pages_per_range = 1 :索引 352 KB,Bitmap Heap Scan on t,0.87 ms 对照:B-tree 索引 21960 KB
pages_per_range 越小索引越大,并不一定更快;默认 128 通常够用。
没有索引:Seq Scan on t,101.30 ms using gin (doc) :大小 12.0 MB,建索引 1.31 s,Bitmap Heap Scan on t,5.78 ms using gin (doc jsonb_path_ops) :大小 8.9 MB,建索引 0.49 s,Bitmap Heap Scan on t,2.17 ms 额外插入 2 万行:没有 GIN 索引 0.02 s,有 GIN 索引 0.06 s
GIN 加速 @> 包含查询;jsonb_path_ops 更小更快但支持的操作符少;写入有额外代价。
没有索引:Seq Scan on t,59.80 ms,命中 192 行 只有 B-tree 索引:Seq Scan on t,58.45 ms(B-tree 帮不上中间匹配) GIN + gin_trgm_ops:Bitmap Heap Scan on t,0.07 ms;索引 38.9 MB,建索引 2.12 s
trigram 的 GIN 索引让 LIKE '%abc%' 走索引,B-tree 做不到,代价是索引很大。