sql - Postgresql 13 中大型数据集的滚动统计

Question

我在 Postgresql 13 中有一个简单的表，其结构如下（简化视图）：

姓名	类型
地点	细绳
压力	数字
时间	没有时区的时间戳

它基本上可以捕获一些气象信息，例如大约 2000 个不同位置的气压。分钟间隔。为了计算移动统计数据，例如平均值、每个位置和超过 30 分钟的滚动时间窗口，我使用以下 SQL：

SELECT t1.location,t1.time, avg(t2.pressure)
FROM meteomeasures as t1
inner join meteomeasures as t2
on t2.time + interval '30 minutes' > t1.time and
t1.location = t2.location
group by t1.location, t1.time

我也有关于位置、时间和压力的索引。我需要处理的完整数据集大约有 1.4 亿行，所以它相当大。即使在只有大约的测试数据集上。800 万行，上述查询在可用硬件上需要近 2 小时才能完成。在不采取更多涉及和成本更高的措施、需要硬件升级和/或分布式数据库架构的情况下，是否有一种方法可以提高性能并允许在无需等待 25 小时或更长时间的情况下获得完整数据集的相同输出？

标签： sqlpostgresql