寻求BRIN索引查询性能远超BTREE索引的无回表场景示例

我发现BRIN索引的空间占用比B-tree小好几个数量级，这一点一眼就能看出来，但我一直找不到一个无回表的查询场景——也就是查询所需数据完全在索引里，不需要访问基表——能让BRIN的执行速度比B-tree快上数量级，说实话，就连快两倍的场景都很难找。

我自己做了个测试，用的是和物理存储完全相关的列（pg_stats.correlation = 1），读取100万行数据，具体测试流程和代码如下：

-- 创建测试表并插入1000万条关联数据
drop table if exists ttt;
create table ttt(a int, b int);
insert into ttt select i, i from generate_series(1, 1e7) i;

-- 创建BRIN和B-tree索引，调整BRIN的range页数量
create index brin_ttt on ttt using brin(b) with (pages_per_range = 128);
create index btree_ttt on ttt(b);

-- 分析表并关闭并行查询
analyze ttt;
set max_parallel_workers_per_gather = 0;

-- 查看表和索引的空间占用
select relname, pg_size_pretty(pg_relation_size(oid)) size 
from pg_class c 
where c.relname in ('brin_ttt', 'btree_ttt', 'ttt');

-- 预缓存所需数据块（重复执行几次）
set enable_indexscan = off;
explain (analyze, buffers) select count(*) from ttt where b > 1e7::int - 1e6::int;
reset enable_indexscan;
explain (analyze, buffers) select count(*) from ttt where b > 1e7::int - 1e6::int;

-- 再次执行确保缓存生效，对比性能
set enable_indexscan = off;
explain (analyze, buffers) select count(*) from ttt where b > 1e7::int - 1e6::int;
reset enable_indexscan;
explain (analyze, buffers) select count(*) from ttt where b > 1e7::int - 1e6::int;

测试结果 #

即使在这种列与物理存储完全关联的理想场景下，B-tree反而更快：

• B-tree索引扫描耗时：167ms
• BRIN索引扫描耗时：190ms

对应的执行计划详情如下：

禁用B-tree（使用BRIN）的执行计划

Aggregate (cost=59199.58..59199.59 rows=1 width=8) (actual time=190.156..190.158 rows=1 loops=1)
  Buffers: shared hit=4442
  ->  Bitmap Heap Scan on ttt (cost=254.47..56785.71 rows=965548 width=0) (actual time=0.518..132.440 rows=1000000 loops=1)
        Recheck Cond: (b > 9000000)
        Rows Removed by Index Recheck: 3392
        Heap Blocks: lossy=4440
        Buffers: shared hit=4442
        ->  Bitmap Index Scan on brin_ttt (cost=0.00..13.09 rows=982659 width=0) (actual time=0.108..0.109 rows=44400 loops=1)
              Index Cond: (b > 9000000)
              Buffers: shared hit=2
Planning:
  Buffers: shared hit=11
Planning Time: 0.138 ms
Execution Time: 190.184 ms

启用B-tree的执行计划

Aggregate (cost=29903.40..29903.40 rows=1 width=8) (actual time=167.632..167.633 rows=1 loops=1)
  Buffers: shared hit=2736
  ->  Index Only Scan using btree_ttt on ttt (cost=0.43..27489.53 rows=965548 width=0) (actual time=0.045..111.415 rows=1000000 loops=1)
        Index Cond: (b > 9000000)
        Heap Fetches: 0
        Buffers: shared hit=2736
Planning:
  Buffers: shared hit=1
Planning Time: 0.107 ms
Execution Time: 167.657 ms

我调整了pages_per_range参数，也试过不同的查询过滤范围，但BRIN的性能始终没能超过B-tree。有没有大佬能给个符合要求的无回表场景示例，让BRIN的速度能远超B-tree？

内容来源于stack exchange