用于查询键控时间范围内排序值的索引

假设我有键/值/时间范围元组，例如：

CREATE TABLE historical_values(
key TEXT,
value NUMERIC,
from_time TIMESTAMPTZ,
to_time TIMESTAMPTZ
)

并希望能够有效地查询特定键和时间的值(降序排序)，例如：

SELECT value
FROM historical_values
WHERE
key = [KEY]
AND from_time <= [TIME]
AND to_time >= [TIME]
ORDER BY value DESC

我应该使用哪种索引/类型来获得最佳查找性能？我怀疑我的解决方案将涉及tstzrange和gist索引，但我不确定如何使其与键匹配和值排序要求很好地配合。

编辑：这是有关使用情况的更多信息。

理想情况下使用 Postgres v9.6 中提供的功能。
关系将包含大约 1k 个键和每个键 5m 的值。值是大整数(最多 32 个字节)，大多是唯一的。时间范围从几个小时到几年不等。时间跨度为5年。不允许使用NULL值，但某些时间范围是开放式的(可以使用NULL或遥远的未来时间进行to_time)。
主键是键和时间范围(因为每个键的时间范围只有一个历史值)。
常见的操作是 a) 更新to_time以"关闭"历史值，以及 b) 插入带有from_time = NOW的新值。
可以查询所有值。分区是一种选择。

数据库设计

对于这样的大表("每个键 1k 键和 5m 个值")，我建议优化存储，例如：

CREATE TABLE hist_keys (
key_id serial PRIMARY KEY
, key text NOT NULL UNIQUE
);
CREATE TABLE hist_values (
hist_value_id bigserial PRIMARY KEY  -- optional, see below!
, key_id        int NOT NULL REFERENCES hist_keys
, value         numeric
, from_time     timestamptz NOT NULL
, to_time       timestamptz NOT NULL
, CONSTRAINT range_valid CHECK (from_time <= to_time)  -- or < ?
);

还有助于索引性能。

并考虑分区。key_id上的列表分区。甚至可以在from_time上添加子分区(这次是范围分区)。在此处阅读手册。

每key_id一个分区，(并启用约束排除！Postgres 只会查看给定键的小分区(和索引)，而不是整个大表。大获全胜。

但我强烈建议至少先升级到Postgres 10，它添加了"声明性分区"。使管理分区变得更加容易。

更好的是，跳到Postgres 11(当前测试版)，它增加了对分区的重大改进(包括性能改进)。最值得注意的是，为了获得最佳查找性能，引用Postgres 11(当前测试版)发行说明中有关分区的章节：

允许在查询处理期间更快地消除分区(Amit Langote，David Rowley，Dilip Kumar)
这加快了对具有许多分区的分区表的访问速度。
允许在查询执行期间消除分区(David Rowley，Beena Emerson)
以前，分区消除只能在计划时进行，这意味着许多联接和准备好的查询无法使用分区消除。

指数

从value列的角度来看，对于每个新查询，所选行的小子集都是任意的。我不指望你能找到一种有用的方法来支持ORDER BY value DESC索引。我会专注于其他专栏。如果您可以从中获取仅索引扫描(对于 btree 和 GiST)，也许可以将value作为最后一列添加到每个索引中。

不分区：

CREATE UNIQUE INDEX hist_btree_idx ON hist_values (key_id, from_time, to_timeDESC);

UNIQUE是可选的，但请参阅下文。
请注意反对from_time和to_time排序顺序的重要性。请参阅(密切相关！

优化对一系列时间戳(两列)的查询

这与在(key_id, from_time, to_time)上实现PK的索引几乎相同。不幸的是，我们不能将其用作PK索引。引用手册：

此外，它必须是具有默认排序顺序的 b 树索引。

因此，我在上面建议的表设计中添加了一个bigserial作为代理主键，并NOT NULL约束和UNIQUE索引来强制执行您的唯一性规则。

在 Postgres 10 或更高版本中，请考虑使用IDENTITY列：

自动递增表列

在这种特殊情况下，您甚至可以使用 PK 约束来避免重复索引并保持表的最小大小。取决于完整的情况。对于 FK 约束或类似约束，您可能需要它。看：

PostgreSQL如何执行UNIQUE约束/它使用什么类型的索引？

像你已经怀疑的那样的GiST 索引可能会更快。我建议在表中保留原始timestamptz列(16 字节而不是tstzrange的 32 字节)，并在安装附加模块后添加key_idbtree_gist：

CREATE INDEX hist_gist_idx ON hist_values
USING GiST (key_id, tstzrange(from_time, to_time, '[]'));

表达式tstzrange(from_time, to_time, '[]')构造一个包括上限和下限的范围。在此处阅读手册。

查询需要与索引匹配：

SELECT value
FROM   hist_values
WHERE  key = [KEY]
AND    tstzrange(from_time, to_time, '[]') @>  tstzrange([TIME_FROM], [TIME_TO], '[]') 
ORDER  BY value DESC;

它相当于您的原始版本。
@>范围包含运算符。

key_id上具有列表分区

每个key_id都有一个单独的表，我们可以从索引中省略key_id，从而提高大小和性能 - 特别是对于 GiST 索引 - 为此我们也不需要额外的模块btree_gist。结果为 ~ 1000 个分区和相应的索引：

CREATE INDEX hist999_gist_idx ON hist_values USING GiST (tstzrange(from_time, to_time, '[]'));

数据库设计

指数

相关内容

最新更新

热门标签：