我有一个数据量很大的表格(500+万行),需要实时过滤/查询。使用普通的B-树索引无法获得令人满意的性能或可预测的查询计划。考虑使用BRIN会有很大帮助,但因为我们的数据不能以任何可控的物理顺序插入,所以我设置了一个MATERIALIZED VIEW
来选择数据(包括连接的数据)并按特定顺序排序。 大概如下...
CREATE MATERIALIZED VIEW my_view AS
SELECT a.one, b.two, b.three, c.four, c.five, c.six
FROM a, b, c WHERE ...joins
ORDER BY b.three, b.two, a.one, c.four;
我随后基于多个列创建了索引,因为这个视图是为单次查询而设计的,所以所有指定的列总是会被使用。
CREATE INDEX my_view_idx ON my_view
USING BRIN (three, two, one, four) WITH (pages_per_range = 64);
我按照可选性对列进行了排序(在表格和BRIN中都是这样),这意味着 b.three
将过滤掉80%的记录(即只有20%的记录匹配),b.two
将过滤掉70%,依此类推。
对 BRIN 列进行排序与物理排序是否必须相同? 我找不到任何描述这一点的资源。最接近的是来自:https://www.postgresql.org/docs/10/indexes-multicolumn.html ...
多列 BRIN 索引可用于涉及索引的任何子集的查询条件。与 B 树或 GiST 不同,索引搜索效果与查询条件使用哪些索引列无关,类似于 GIN。
... 但这并未描述列的排序,仅涉及查询时的包含性。
我可以进行实验(并且已经在进行中,并且结果出奇地不错),但这是一个缓慢的过程,因为需要2个或更长时间来实现视图和构建索引,所以我希望我的猜测有某种事实依据,以避免浪费大量时间。