postgresql - Postgres 选择 BTREE 而不是 BRIN 索引

Question

我正在运行 Postgres 9.5 并且正在使用 BRIN 索引。我有一个包含大约 1.5 亿行的事实表，我正试图让 PG 使用 BRIN 索引。我的查询是:

select sum(transaction_amt), 
       sum (total_amt) 
from fact_transaction 
where transaction_date_key between 20170101 and 20170201 

我在列 transaction_date_key 上创建了 BTREE 索引和 BRIN 索引(默认 pages_per_range 值为 128)(上述查询指的是 2017 年 1 月至 2 月)。我原以为 PG 会选择使用 BRIN 索引，但它与 BTREE 索引一起使用。这是解释计划:

https://explain.depesz.com/s/uPI

然后我删除了 BTREE 索引，对表进行了清理/分析，然后重新运行查询，它确实选择了 BRIN 索引，但是运行时间相当长:

https://explain.depesz.com/s/5VXi

事实上，当使用 BTREE 索引而不是 BRIN 索引时，我的测试都更快。我以为应该是相反的？

我更喜欢使用 BRIN 索引，因为它的尺寸较小，但我似乎无法让 PG 使用它。

注意:我加载了从 2017 年 1 月到 2017 年 6 月(通过 transaction_date_key 定义)的数据，因为我读到物理表排序在使用 BRIN 索引时会有所不同。

有谁知道为什么 PG 选择使用 BTREE 索引以及为什么 BRIN 在我的情况下这么慢？

Answer 1

看来 BRIN 索引扫描的选择性不是很强——它返回了 3000 万行，所有这些都必须重新检查，这是花费时间的地方。

这可能意味着 transaction_date_key 与表中行的物理位置没有很好的相关性。

A BRIN index works通过将表 block 的范围“集中在一起”(多少可以使用存储参数 pages_per_range 进行配置，其默认值为 128)。存储每个 block 的索引值的最大值和最小值。

所以你表中的很多 block 范围包含 20170101 和 20170201 之间的 transaction_date_key，并且必须扫描所有这些 block 以计算查询结果。

我看到两种改善情况的选择:

• 降低 pages_per_range 存储参数。这将使索引更大，但会减少“误报” block 的数量。

• 在 transaction_date_key 属性上对表进行聚类。正如您所发现的，这需要(至少暂时)列上的 B 树索引。