PostgreSQL选择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索引，对表进行了vacuum / analyze操作，并重新运行了查询，它确实选择了BRIN索引，但运行时间明显更长：

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

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

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

注意：我加载了数据，从2017年1月到2017年6月（通过transaction_date_key定义），因为我读到物理表排序在使用BRIN索引时很重要。

有人知道为什么PG选择使用BTREE索引，以及为什么在我的情况下BRIN索引如此缓慢吗？

- Ryan

---

你能给我们展示 explain (analyze, verbose, buffers, timing) 的输出结果，而不仅仅是 explain (analyze) 吗？ - user330315

当然 - 现在正在运行它们。 - Ryan

B-Tree和BRIN：https://explain.depesz.com/s/S3Zp仅限于BRIN：https://explain.depesz.com/s/Z1A5 - Ryan

BRIN的优势在于其大小，而不是速度——因为BRIN索引非常小，与顺序扫描相比，扫描索引增加的开销很小。我认为它从来没有意味着要与BTREE（在性能上）相媲美。如果BTREE是可能的（其大小适用于应用程序），则其速度优于BRIN。至少对于那些只期望选择小子集的查询而言。当选择几乎所有表时，BRIN可能表现更好（在这些情况下，如果没有BRIN，则会选择顺序扫描）。 - pozs

谢谢@Laurenz Albe，将pages_per_range更改为64后，使用BRIN索引运行查询速度大大提高。我有一个后续问题，但我会为此创建一个新帖子（关于选择pages_per_range大小的有效方法）。 - Ryan

Answer 1

似乎BRIN索引扫描的选择性不是很高，它返回了3000万行数据，所有这些数据都必须被重新检查，这就是时间消耗的原因。

这可能意味着transaction_date_key与表中行的物理位置关联不好。 BRIN索引通过“合并”一定范围内的表块（可以使用存储参数pages_per_range配置多少个，默认值为128）来工作。每个块范围的最大值和最小值被存储。

因此，在您的表中，许多块范围包含transaction_date_key在20170101和20170201之间，所有这些块都必须被扫描以计算查询结果。

我看到两种改进情况：

降低pages_per_range存储参数。这将使索引变得更大，但它将减少“误报”块的数量。

在transaction_date_key属性上对表进行聚类。正如您已经发现的那样，这需要（至少是暂时的）在该列上创建B树索引。

- Laurenz Albe