TimescaleDB多表联合查询耗时过长，寻求优化方案

我们近期将时序数据迁移至TimescaleDB，基础查询表现正常，但引擎模拟器需要从多张超表中获取数据，经聚合、排序后做流处理。以下查询耗时超一分钟，无法满足处理至少一年数据的需求：

WITH f_md AS (
    SELECT 
        raw_message,
        datetime
    FROM my_f_data
    WHERE
        id=ANY(ARRAY['V03', 'V06'])
    AND
        datetime BETWEEN
        TIMESTAMP '2024-03-20 06:50:00' AND
        TIMESTAMP '2024-03-20 13:00:00'
), s_md AS (
    SELECT
        raw_message,
        datetime
    FROM my_s_data
    WHERE
        id=ANY(ARRAY['S107', 'S057'])
    AND
        datetime BETWEEN
        TIMESTAMP '2024-03-20 10:50:00' AND
        TIMESTAMP '2024-03-20 20:00:00'
), all_data AS (
    SELECT * FROM f_md
    UNION ALL
    SELECT * FROM s_md
)
SELECT * FROM all_data
ORDER BY datetime

相关表均为hypertables（超表），时间戳为微秒粒度，无主键，datetime为排序键。以下是具体优化建议：

• 创建复合索引：针对两张超表，创建(id, datetime)的复合索引。因为查询同时过滤id和datetime范围，复合索引能让数据库直接定位到符合条件的行，避免全表扫描。执行命令：

  CREATE INDEX idx_my_f_data_id_datetime ON my_f_data (id, datetime);
  CREATE INDEX idx_my_s_data_id_datetime ON my_s_data (id, datetime);
  

  注：TimescaleDB的超表会自动将索引同步到所有分块，无需额外操作。

• 优化排序逻辑，减少全局排序开销：当前查询是先取出所有数据再整体排序，数据量大会导致磁盘排序开销剧增。可以让每个子查询先单独排序，再通过合并有序结果的方式减少全局排序压力，修改后的查询如下：

  WITH f_md AS (
      SELECT 
          raw_message,
          datetime
      FROM my_f_data
      WHERE
          id=ANY(ARRAY['V03', 'V06'])
      AND
          datetime BETWEEN TIMESTAMP '2024-03-20 06:50:00' AND TIMESTAMP '2024-03-20 13:00:00'
      ORDER BY datetime
  ), s_md AS (
      SELECT
          raw_message,
          datetime
      FROM my_s_data
      WHERE
          id=ANY(ARRAY['S107', 'S057'])
      AND
          datetime BETWEEN TIMESTAMP '2024-03-20 10:50:00' AND TIMESTAMP '2024-03-20 20:00:00'
      ORDER BY datetime
  )
  SELECT * FROM f_md
  UNION ALL
  SELECT * FROM s_md
  ORDER BY datetime;
  

• 调整超表分块策略：检查超表的分块时间间隔是否合理。分块过大则查询时需扫描过多数据，过小会增加元数据开销。可通过以下命令查看当前设置：

  SELECT chunk_interval FROM timescaledb_information.hypertables WHERE table_name = 'my_f_data';
  

  针对年级数据查询，建议将分块间隔设置为1天或1周，平衡扫描效率与分块数量。

• 启用并行查询：调整PostgreSQL的max_parallel_workers_per_gather参数（建议根据CPU核心数设置为4-8），让数据库并行处理子查询的数据读取与排序，提升整体处理速度。

• 压缩大字段减少IO开销：如果raw_message字段体积较大，可对超表启用列级压缩，减少磁盘IO开销：

  ALTER TABLE my_f_data SET (timescaledb.compress, timescaledb.compress_segmentby = 'id');
  ALTER TABLE my_s_data SET (timescaledb.compress, timescaledb.compress_segmentby = 'id');
  

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