TimescaleDB连续聚合物化视图查询性能低于原始超表问题咨询

问题根因 #

• 索引不匹配：TimescaleDB 对连续聚合视图默认创建的索引为(bucket, entity_id, user_id)，你当前慢查询的场景要么是无过滤按bucket聚合，要么是关联entities表按category过滤后聚合，都无法命中默认索引，需要全表扫描视图的所有行。而原始超表按时间分区，按天聚合时可以直接并行扫描各时间分区，效率更高。
• 聚合颗粒度太细无压缩优势：你创建的连续聚合维度是entity_id + user_id + 天，在100万entity_id、10个user_id的配置下，聚合后的行数和原始700万行数据差异极小，预聚合减少扫描行数的优势完全没有发挥，反而多了连续聚合的视图层开销。
• 实时聚合额外开销：如果你的连续聚合开启了默认的实时聚合配置，每次查询还需要合并未物化的热区块数据，进一步增加了查询耗时。
• 额外注意：你当前用avg(m.avg)计算全局平均的逻辑是错误的，平均值的平均值不等于整体平均值，会导致计算结果偏差。

优化方案 #

• 新增适配查询场景的索引：
  针对按bucket全量聚合的场景，新增索引：CREATE INDEX ON ack_alarm_number_daily (bucket);
  针对关联entities表按entity_id过滤的场景，新增索引：CREATE INDEX ON ack_alarm_number_daily (entity_id, bucket);
• 修正平均值计算逻辑：创建连续聚合时同时存储sum和count：

  CREATE MATERIALIZED VIEW IF NOT EXISTS ack_alarm_number_daily
  WITH (timescaledb.continuous) AS
  SELECT  time_bucket(INTERVAL '1 day', time) AS bucket,
          entity_id, user_id,
          SUM(value) as sum_val,
          COUNT(value) as count_val,
          AVG(value) as avg_val
  FROM ack_alarm_number
  GROUP BY entity_id, user_id, bucket;
  

  查询全局平均时用SUM(sum_val)/SUM(count_val)计算，保证结果正确。
• 新增更粗颗粒度的连续聚合：如果有大量全量按天聚合的需求，可以直接创建仅按bucket分组的连续聚合，行数会压缩到仅90行左右，查询效率会提升几个数量级。
• 当原始数据规模达到十亿级别以上时，细颗粒度连续聚合的优势会明显体现，当前700万行的规模下原始表本身扫描速度极快，连续聚合的优势不明显。

内容的提问来源于stack exchange，提问作者Oldook