在我的数据库(PostgreSQL12;timescaleDB 1.7.0)中,有多个度量表,其中包含每分钟和设备一行。它包含一个deviceId、时间、四个双精度数和一个枚举值。
有不同的基于时间的查询来分析数据,比如在12h的切片上绘制图表,或者选择最后5m的聚合状态。
为了提高查询性能,我为12小时的情况设置了timescale的连续聚合视图,这极大地缩短了查询时间,因为一切都是预先计算的。我对5m的小得多的切片进行了同样的尝试,希望能有所改善,因为每个查询的数据都会小得多,尽管不像12h示例中那样剧烈。令人惊讶的是,情况恰恰相反。现在,选择原始数据比选择聚合视图要快得多,因为我不太理解聚合视图。
以下是我的视图的定义:
CREATE VIEW metric_5m
WITH ( timescaledb.continuous,
timescaledb.refresh_interval = '5 minutes' )
AS
SELECT device,
time_bucket('5 minutes', time) as "time_bucket",
max(metric.maximum) as "maximum",
min(metric.minimum) as "minimum",
avg(metric.average) as "average",
avg(metric.sd) as "sd"
FROM metric
GROUP BY time_bucket, device;选择原始数据(在我的测试设置中大约为360万行)大约需要300ms,而选择视图大约需要3500ms。我怀疑我不知何故错误地使用了它,或者间隔太小,因为它在12小时的示例中表现得很好,但我找不到原因。
所以,感谢大家在这方面的帮助!
发布于 2020-09-02 02:28:21
您的猜测是正确的,即在连续聚合上观察到的查询执行缓慢是由于间隔太小。连续聚合的物化存储部分,然后使用这些部分来计算最终聚合。这需要空间和时间。因此,连续聚集对于较大的间隔具有显著的优势,并且直接在较小间隔内对超表执行聚集查询的效率更高。
我不知道有人研究过如何在连续聚合得到回报时估计分组间隔。我希望它取决于聚合的数量、聚合中的数据类型和聚合类型,因为不同的聚合将具有不同的部分。例如,与sum或count相比,avg需要更多的部分信息。This blogpost给出了一些关于连续聚合的细节,以及它们是如何使用偏量实现的。
您可以尝试查看compression是否有助于提高性能,因为它将减少从磁盘读取的数据量,并且压缩的数据可以按分组列进行组织。
https://stackoverflow.com/questions/63687817
复制相似问题