代码之家 › 专栏 › 技术社区 › Benjamin Egelund-Müller

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

postgresql-performance database-design indexing postgresql

Benjamin Egelund-Müller · 技术社区 · 7 年前

假设我有key/value/timerange元组,例如:

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 和A gist 索引,但我不知道如何使这与密钥匹配和值排序要求配合得很好。

编辑: 这里有一些关于用法的更多信息。

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

1 回复 | 直到 7 年前

Erwin Brandstetter 7 年前

数据库设计

对于这样的大表(“1k个键,每个键有5个值”),我建议优化存储,例如:

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 . Read the manual here.

每个分区一个 基耶德 ,(和) constraint exclusion 启用!)postgres只查看给定键的小分区(和索引),而不是整个大表。主要获胜。

但我强烈建议至少升级到 邮政10 首先,它增加了 "declarative partitioning" . 使管理分区更加容易。

更好的是,跳到 邮政11 (目前是beta版),它增加了分区的主要改进(包括性能改进)。最值得注意的是,为了你的目标 以获得最佳的查找性能 ,引用 chapter on partitioning in release notes for Postgres 11 (currently beta) :

允许在查询处理期间更快地消除分区(Amit Langote、David Rowley、Dilip Kumar)

这加快了对具有多个分区的分区表的访问。

允许在查询执行期间消除分区(David Rowley,Beena Emerson)

以前的分区消除只能在计划时进行, 这意味着许多连接和准备好的查询不能使用分区消除。

索引

从 value 列中,选定行的小子集对于每个新查询都是任意的。我不指望你能找到一个有用的方法来支持 ORDER BY value DESC 有索引。我会把注意力集中在其他栏目上。 也许吧 添加 价值 作为每个索引的最后一列,如果您只能从索引中扫描索引(BTRAGE和GIST的可能)。

无分区:

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

UNIQUE 是可选的,但请参见下文。
注意相反排序顺序的重要性 从时代开始 和 to_time . 见(密切相关!):

Optimizing queries on a range of timestamps (two columns)

这几乎与实现pk的索引相同 (key_id, from_time, to_time) . 不幸的是,我们不能用它作为pk索引。 Quoting the manual:

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

所以我添加了一个 bigserial 作为上面我建议的表设计中的代理主键 NOT NULL 约束条件加上 独特的 执行唯一性规则的索引。

在博士后10或更晚的时候考虑 IDENTITY 改为列:

Auto increment table column

在这种特殊情况下,您甚至可以使用pk约束来避免重复索引并保持表的最小大小。视情况而定。对于FK约束或类似的约束,可能需要它。见:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

一 主旨索引 就像你已经怀疑的可能更快。我建议保留你的原稿 timestamptz 表中的列(对于 tstzrange )并添加 基耶德 安装附加模块后 btree_gist :

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

表达 tstzrange(from_time, to_time, '[]') 构造范围包括上界和下界。 Read the manual here.

查询需要与索引匹配:

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

相当于你的原作。
@> being the range contains operator.

打开列表分区 基耶德

用一个 每个单独的表格 基耶德 我们可以省略 基耶德 从索引来看,改进了大小和性能—特别是gist索引—我们也不需要额外的模块 BTE . 生成约1000个分区和相应的索引:

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