TimescaleDB按时间区间获取最新数据行的查询优化问询

我有一个存储为TimescaleDB超表的electricity_data表，数据写入速率为每秒1行，表结构如下：

CREATE TABLE electricity_data
(
    "time" timestamptz NOT NULL,
    meter_id integer REFERENCES meters NOT NULL,
    import_low double precision,
    import_normal double precision,
    export_low double precision,
    export_normal double precision,
    PRIMARY KEY ("time", meter_id)
)

需要在指定时间段内，获取每个时间区间（例如过去一年的每月）的最新数据行。

• 使用DISTINCT ON的查询可实现需求但性能缓慢，执行耗时约59秒；
• 单独查询单月最新行的操作几乎瞬时完成（耗时0.152秒）；
• 尝试使用GROUP BY结合MAX(time)的查询，耗时缩短至约10秒，但仍慢于手动逐个区间查询的方式，且添加索引无明显效果。

查询1：DISTINCT ON 实现 #

EXPLAIN ANALYZE
SELECT
DISTINCT ON (bucket)
time_bucket('1 month', "time", 'Europe/Amsterdam') AS bucket,
import_low,
import_normal,
export_low,
export_normal
FROM electricity_data
WHERE meter_id = 1
AND "time" BETWEEN '2022-01-01T00:00:00 Europe/Amsterdam' AND '2023-01-01T00:00:00 Europe/Amsterdam'
ORDER BY bucket DESC

执行计划 #

Unique  (cost=0.42..542380.99 rows=200 width=40) (actual time=3654.263..59130.398 rows=12 loops=1)
  ->  Custom Scan (ChunkAppend) on electricity_data  (cost=0.42..514045.41 rows=11334231 width=40) (actual time=3654.260..58255.396 rows=11161474 loops=1)
        Order: time_bucket('1 mon'::interval, electricity_data.""time"", 'Europe/Amsterdam'::text, NULL::timestamp with time zone, NULL::interval) DESC
        ->  Index Scan using _hyper_12_1533_chunk_electricity_data_time_idx on _hyper_12_1533_chunk  (cost=0.42..11530.51 rows=255951 width=40) (actual time=3654.253..3986.885 rows=255582 loops=1)
              Index Cond: ((""time"" >= '2021-12-31 23:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-12-31 23:00:00+00'::timestamp with time zone))
              Filter: (meter_id = 1)
              Rows Removed by Filter: 24330
        ->  Index Scan Backward using ""1529_1849_electricity_data_pkey"" on _hyper_12_1529_chunk  (cost=0.42..25777.81 rows=604553 width=40) (actual time=1.468..1810.493 rows=603808 loops=1)
              Index Cond: ((""time"" >= '2021-12-31 23:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-12-31 23:00:00+00'::timestamp with time zone) AND (meter_id = 1))
(...)
Planning Time: 57.424 ms
JIT:
  Functions: 217
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 43.496 ms, Inlining 18.805 ms, Optimization 2348.206 ms, Emission 1288.087 ms, Total 3698.594 ms
Execution Time: 59176.016 ms

查询2：单月最新行查询（高效） #

EXPLAIN ANALYZE
SELECT
"time",
import_low,
import_normal,
export_low,
export_normal
FROM electricity_data
WHERE meter_id = 1
AND "time" BETWEEN '2022-12-01T00:00:00 Europe/Amsterdam' AND '2023-01-01T00:00:00 Europe/Amsterdam'
ORDER BY "time" DESC
LIMIT 1

执行计划 #

Limit  (cost=0.42..0.47 rows=1 width=40) (actual time=0.048..0.050 rows=1 loops=1)
  ->  Custom Scan (ChunkAppend) on electricity_data  (cost=0.42..11530.51 rows=255951 width=40) (actual time=0.047..0.048 rows=1 loops=1)
        Order: electricity_data.""time"" DESC
        ->  Index Scan using _hyper_12_1533_chunk_electricity_data_time_idx on _hyper_12_1533_chunk  (cost=0.42..11530.51 rows=255951 width=40) (actual time=0.046..0.046 rows=1 loops=1)
              Index Cond: ((""time"" >= '2022-11-30 23:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-12-31 23:00:00+00'::timestamp with time zone))
              Filter: (meter_id = 1)
        ->  Index Scan Backward using ""1529_1849_electricity_data_pkey"" on _hyper_12_1529_chunk  (cost=0.42..25777.81 rows=604553 width=40) (never executed)
              Index Cond: ((""time"" >= '2022-11-30 23:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-12-31 23:00:00+00'::timestamp with time zone) AND (meter_id = 1))
(...)
        ->  Index Scan using _hyper_12_1512_chunk_electricity_data_time_idx on _hyper_12_1512_chunk  (cost=0.42..8.94 rows=174 width=40) (never executed)
              Index Cond: ((""time"" >= '2022-11-30 23:00:00+00'::timestamp with time zone) AND (""time"" <= '2022-12-31 23:00:00+00'::timestamp with time zone))
              Filter: (meter_id = 1)
Planning Time: 2.162 ms
Execution Time: 0.152 ms

查询3：GROUP BY + MAX(time) 实现 #

EXPLAIN ANALYZE
SELECT MAX("time") AS "time"
FROM electricity_data
WHERE meter_id = 1
    AND "time" >= '2022-01-01T00:00:00 Europe/Amsterdam'
    AND "time" < '2023-01-01T00:00:00 Europe/Amsterdam'
GROUP BY time_bucket('1 month', "time", 'Europe/Amsterdam');

执行计划 #

(... plan removed)
Planning Time: 50.463 ms
JIT:
  Functions: 451
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 76.476 ms, Inlining 0.000 ms, Optimization 13.849 ms, Emission 416.718 ms, Total 507.043 ms
Execution Time: 9910.058 ms

1. 利用LATERAL关联复用单月高效查询 #

既然单区间查询性能优异，可以先生成所有目标时间区间，再逐个关联查询每个区间的最新行，总耗时接近多次单区间查询的总和：

WITH monthly_buckets AS (
    SELECT time_bucket('1 month', generate_series(
        '2022-01-01T00:00:00 Europe/Amsterdam'::timestamptz,
        '2023-01-01T00:00:00 Europe/Amsterdam'::timestamptz,
        '1 month'
    ), 'Europe/Amsterdam') AS bucket
)
SELECT 
    mb.bucket,
    ed.import_low,
    ed.import_normal,
    ed.export_low,
    ed.export_normal
FROM monthly_buckets mb
LEFT JOIN LATERAL (
    SELECT import_low, import_normal, export_low, export_normal
    FROM electricity_data
    WHERE 
        meter_id = 1
        AND "time" >= mb.bucket
        AND "time" < mb.bucket + '1 month'::interval
    ORDER BY "time" DESC
    LIMIT 1
) ed ON true
ORDER BY mb.bucket DESC;

2. 创建连续物化视图预计算结果 #

如果该查询是高频操作，可创建按meter_id和月粒度聚合的连续物化视图，自动刷新最新数据：

CREATE MATERIALIZED VIEW monthly_electricity_latest
WITH (timescaledb.continuous) AS
SELECT
    meter_id,
    time_bucket('1 month', "time", 'Europe/Amsterdam') AS bucket,
    last(import_low, "time") AS import_low,
    last(import_normal, "time") AS import_normal,
    last(export_low, "time") AS export_low,
    last(export_normal, "time") AS export_normal
FROM electricity_data
GROUP BY meter_id, bucket;

-- 创建索引加速查询
CREATE INDEX idx_monthly_electricity_latest_meter_bucket ON monthly_electricity_latest(meter_id, bucket);

查询时直接从物化视图获取，耗时几乎为0：

SELECT bucket, import_low, import_normal, export_low, export_normal
FROM monthly_electricity_latest
WHERE meter_id = 1
AND bucket >= '2022-01-01T00:00:00 Europe/Amsterdam'
AND bucket < '2023-01-01T00:00:00 Europe/Amsterdam'
ORDER BY bucket DESC;

3. 创建表达式索引优化GROUP BY查询 #

如果不想用LATERAL或物化视图，可创建包含meter_id、time_bucket计算结果和time的表达式索引，让数据库快速分组并获取最新值：

CREATE INDEX idx_electricity_meter_time_bucket ON electricity_data(meter_id, time_bucket('1 month', "time", 'Europe/Amsterdam'), "time" DESC)
INCLUDE (import_low, import_normal, export_low, export_normal);

修改查询以适配索引：

SELECT
    bucket,
    last(import_low, "time") AS import_low,
    last(import_normal, "time") AS import_normal,
    last(export_low, "time") AS export_low,
    last(export_normal, "time") AS export_normal
FROM (
    SELECT
        time_bucket('1 month', "time", 'Europe/Amsterdam') AS bucket,
        "time",
        import_low,
        import_normal,
        export_low,
        export_normal
    FROM electricity_data
    WHERE meter_id = 1
        AND "time" >= '2022-01-01T00:00:00 Europe/Amsterdam'
        AND "time" < '2023-01-01T00:00:00 Europe/Amsterdam'
) t
GROUP BY bucket
ORDER BY bucket DESC;

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