Sql执行耗时很长

开源版问题专区
1

【TDengine 使用环境】
测试

【TDengine 版本】

3.4.1.0

【操作系统以及版本】

centos7.9

【部署方式】容器

【集群节点数】

1

【集群副本数】

0

【资源配置】16C64G

【报错完整截图】

select device_id, last_row(zlgl) as value from product_nbq where ts < ‘2026-08-17 00:00:00’ group by device_id;

image
image2586×134 20.4 KB

我的大致需求时对每个设备都要查询某个时间段前最新的一条数据，然后加在一起。所以这里是用last_row 查询。然后准备在代码里做加法。但是查询耗时很长。请问有什么方式解决。

2

只有这些信息很难判断这个结果是否合理。请查询一下执行计划，看看是否有不合理的地方：

EXPLAIN ANALYZE VERBOSE true select device_id, last_row(zlgl) as value from product_nbq where ts < ‘2026-08-17 00:00:00’ group by device_id\G;

然后将输出结果发送出来，我们分析一下。

3

→ Aggregate (cost=17.335..19.445 rows=9615 functions=2 width=12 input_order=desc)
Output: columns=2 width=12 blocking=1
Merge ResBlocks: True
Exec cost: compute=9.943 create=2026-08-17 17:42:47.677215 start=0.016 times=5 input_wait=9.378 output_wait=0.109
→ Data Exchange 8:1 (cost=3.508..14.984 rows=9615 width=102)
Output: columns=2 width=102
Network: mode=concurrent fetch_times=1.0(1) fetch_rows=1201.9(1258) fetch_cost=5.833(10.561)
Exec cost: compute=5.202 create=2026-08-17 17:42:47.677208 start=0.023 times=9616 input_wait=2.642 output_wait=7.118
→ Aggregate (cost=15314.614(19722.152)..15316.209(19723.732) rows=1201.9(1258) functions=2 width=102 input_order=desc)
Output: columns=2 width=102 blocking=1
Merge ResBlocks: False
Exec cost: compute=4.464(5.700) create=2026-08-17 17:42:27.951272(2026-08-17 17:42:27.952709) start=0.013(0.022) times=1202.9(1259) input_wait=15310.952(19717.227) output_wait=0.779(1.129)
→ Table Scan on product_nbq (cost=433.552(987.716)..13018.080(18045.274) rows=71253.6(104764) columns=2 pseudo_columns=1 width=20 order=[asc|0 desc|1] mode=ts_order data_load=data)
Output: columns=3 width=20
Time Range: [-9223372036854775808, 1786895999999]
Partition Key: partitions=1
Exec cost: compute=15310.306(19716.361) create=2026-08-17 17:42:27.951266(2026-08-17 17:42:27.952705) start=0.019(0.028) times=1202.9(1259) input_wait=0.000(0.000) output_wait=4.171(5.656)
I/O cost: total_blocks=2387.5(2495) file_load_blocks=1123645.8(1345578) stt_load_blocks=170.6(316) mem_load_blocks=0.0(0) sma_load_blocks=0.0(0) composed_blocks=1210.6(1251)
file_load_elapsed=10834.183(16288.197) stt_load_elapsed=556.814(1137.917) mem_load_elapsed=0.000(0.000) sma_load_elapsed=0.000(0.000) composed_elapsed=7.804(10.291)
check_rows=71253.6(104764) slowest_vgroup_id=9 slow_deviation=37.3% cost_ratio=1.9 data_deviation=66.0%
Planning Time: 0.824 ms
Execution Time: 19752.540 ms

4

device_id 是tag