PostgreSQL：datetime字段BRIN索引应用及索引优化技术问询

我有一张频繁周期性插入数据的表，当前在A、B、C列上建有Btree索引，该索引体积庞大，几乎是表体积的2/3。各列属性如下：

• 列A：boolean类型
• 列B：varchar类型（关联其他表的外键）
• 列C：datetime类型

核心查询分为两类：

1. 同时使用A、B、C列作为过滤条件
2. 仅使用A、C列作为过滤条件
   两类查询均包含C > now()的条件

插入特性：

• 列A、B的插入值完全随机无顺序
• 列C的插入值虽随机但均为未来时间
• 按B、C列过滤后，结果集仅约50行

咨询问题：

1. 既然按B、C过滤后结果仅约50行，是否仍有必要在索引中包含列A？
2. 列C插入无序的情况下，能否使用BRIN索引？
3. 我已为列C创建BRIN索引，但执行查询explain analyze select * from my_table where C between '2023-07-25 18:46:12.164035 +00:00' and '2024-07-25 18:46:12.164035 +00:00'时，数据库未使用该BRIN索引，而是执行了全表扫描（测试表未建A、B、C列的Btree索引），执行计划如下：

Seq Scan on my_table  (cost=0.00..24574.60 rows=1012513 width=33) (actual time=0.015..60.246 rows=1013338 loops=1)
  Filter: ((C >= '2023-07-25'::date) AND (C <= '2024-07-25'::date))
  Rows Removed by Filter: 702
Planning Time: 0.070 ms
Execution Time: 79.117 ms

这是为什么？

1. 是否有必要在索引中包含列A？ #

有必要，原因如下：

• 你的两类核心查询都用到A列做过滤，如果索引不带A，数据库用B+C索引查到50行后，还得回表读取A列的值做二次过滤，会额外增加IO开销。就算单次查询开销不大，频繁执行的话累积成本也很可观。
• 另外，boolean类型本身占用空间极小，加到索引里不会让索引体积大幅膨胀，完全没必要为这点空间牺牲查询性能。

2. 列C插入无序的情况下能否使用BRIN索引？ #

可以用，但要看数据块内的时间分布：
BRIN索引是基于数据块的统计信息（比如每个块的时间最大值、最小值）来快速定位范围查询。如果列C是随机未来时间，但单个数据块内的时间跨度不大（不会出现一个块里既有明天的时间又有一年后时间的极端情况），BRIN索引依然能有效工作。
而且BRIN索引体积远小于Btree，非常适合你这种频繁插入的大表，就算插入无序，只要数据块的时间范围没有严重重叠，它的效率会比全表扫描高很多。

3. 为什么BRIN索引没被使用？ #

从执行计划能看到，你的查询返回了1013338行，几乎是全表数据（只过滤掉702行）。数据库优化器会自动判断：如果查询要返回的行数占表的比例极高，用索引的成本（先查索引定位数据块，再扫描这些块）反而比直接全表扫描更高，所以会选择Seq Scan。
BRIN索引适合范围查询且返回结果集较小的场景，你可以试试缩小时间范围（比如只查最近一周的C值），这时优化器就会选择使用BRIN索引了。

内容的提问来源于stack exchange，提问作者Asfandyar Abbasi