BRIN指数在Postgres中的测试效率

Question

我在OLAP对BRIN索引的查询中发现了许多用例，因为它们要小得多，并且具有与BTREE索引相同数量级的速度。

通常，我使用BRIN索引来单调(或接近)增加时间戳列。在我们的OLTP数据库中，它们被紧密地放置在磁盘上，因为随着时间的推移，它们自然是按顺序编写的。

在我们的仓库Postgres实例中，表的某些部分是在大批中加载的，不一定是按时间排序的。

是否有一些分析性的查询，可以告诉我数据是否放置得太随意，不能使用BRIN，即使列值分布似乎应该遵循创建BRIN索引的所有先决条件？

Answer 1

BRIN索引只适用于大表(数千个数据页或更多的数据页)。(你已经知道了，只是为了引导公众。)但是如何判断行的物理排序是否足够呢？

手册提供了一个提示：

BRIN索引(块范围indexes的缩写)存储表的连续物理块范围中存储的值的摘要。因此，对于值与表行的物理顺序密切相关的列，它们是最有效的。

大胆强调我的。

ANALYZE收集存储在pg_statistic中的相应统计信息。最好通过系统视图correlation中的列pg_stats访问：

correlation float4列值的物理行排序和逻辑排序之间的统计相关性。这范围从-1到+1。当值接近-1或+1时，由于减少了对磁盘的随机访问，估计列上的索引扫描要比接近于零的时候便宜。(如果列数据类型没有<运算符，则此列为空。)

所有的统计数据都是基于行的样本，因此只是估计，即使是最新的数据。您可以通过增加样本大小来提高有效性，即设置更高的“统计目标”--当然，这也增加了ANALYZE的成本。默认的default_statistics_target仅为100，这在大多数情况下是可以的，但对于具有非平凡数据分布(用于排序和筛选)的大型表的列来说，这一值通常太低。请参见：

• 检查PostgreSQL中的统计目标

您甚至可以暂时增加statistics目标，运行ANALYZE，并重置该值，以获得与增加的有效性的一次性关联。

因此，一旦对表进行了分析(手动或autovacuum)，检查：

SELECT schemaname, tablename, attname, n_distinct, correlation
FROM   pg_stats
WHERE  schemaname = 'public'
AND    tablename  = 'tbl'
AND    attname    = 'my_index_col';

• 接近0的correlation意味着一个BRIN索引将是无用的。
• 接近1(或-1)的correlation意味着BRIN指数将是优秀的。
  • 如果n_distinct中的计数较低(相对于总计数)或负比率接近于0，则可能相应地增加pages_per_range (默认的128)的设置。
  • 如果n_distinct的计数较高(相对于总计数)或负比接近-1，则可能相应地减少pages_per_range的设置。

• 中间的一切都是灰色地带。有很多因素在起作用。这在很大程度上取决于avg行大小和典型的查询。在我的经验中，接近0或1/ -1的值是常见的，这使得决策变得容易。

尽管如此，如果表没有得到足够的排序，您可以使用CLUSTER或阻塞程度较低的社区工具pg_挤挤或pg_雷帕克来实现。按排序顺序重写整个表对于大型表来说代价高昂，如果存在UPDATE (或DELETE + INSERT)操作，则会随着时间的推移而恶化。但可以支付某些用例的费用。请参见：

• 配置PostgreSQL以提高读取性能