postgresql - 选择连续聚合比在 timescaledb 中选择原始数据慢

Question

在我的数据库(Postgresql 12；timescaleDB 1.7.0)中有多个指标表，每分钟包含一行和设备。它包含一个 deviceId、时间、四个 double 值和一个枚举值。
有不同的基于时间的查询来分析数据，例如在它的 12 小时切片上绘制图形或选择最后 5m 的聚合状态。
为了提高查询性能，我为 12 小时的情况设置了 timescale 的连续聚合 View ，这大大缩短了查询时间，因为一切都是预先计算好的。我对 5m 的小得多的切片进行了相同的尝试，期望有所改进，因为每个查询的数据会小得多，尽管不像 12h 示例中那样剧烈。
令人惊讶的是，情况恰恰相反。现在选择原始数据比选择我不太理解的聚合 View 要快得多。
这是我的观点的定义:

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 万行)大约需要 300 毫秒，而选择 View 需要大约 3500 毫秒。我怀疑我以某种方式使用它错误或间隔太小，因为它在 12h 示例中表现非常好，但我找不到原因。
所以，感谢这方面的每一个帮助!

Answer 1

您的猜测是正确的，在连续聚合上观察到的缓慢查询执行是由于间隔太小造成的。连续聚合的物化存储部分，然后用于计算最终聚合。这需要空间和时间。因此，连续聚合在更大的间隔上具有显着的优势，并且在小间隔内直接在超表上执行聚合查询更有效。
我不知道有人研究过如何在连续聚合得到返回时估计分组间隔。我希望它取决于聚合的数量、聚合中的数据类型和聚合类型，因为不同的聚合将具有不同的部分。例如，avg需要比 sum 更多的部分信息或 count . This blogpost给出了一些关于连续聚合的细节以及它们是如何用部分实现的。
你可以试试看 compression可以帮助提高性能，因为它会减少从磁盘读取的数据量，并且可以按分组列组织压缩数据。