admin管理员组

文章数量:1346328

I am testing a Timescale DB for a migration, and there is a query which is very slow depending on the time range, when using m.ts >= '2019-07-03 11:50:00+00' the query takes 1m30s (!), but with m.ts >= '2019-08-03 11:50:00+00' it takes about 250ms.

The query is this one, bear in mind that there are no rows for the requested sensor type 'ca1' so the whole table (hopefully index) has to be scanned.

SELECT ts, s.type, m.val
FROM measure m 
 JOIN node n on node_id = n.id 
 JOIN measure_sensor s on m.sensor_id = s.id 
WHERE 
(n.serial = 'TEST0000003') 
AND 
(s.type IN ('ca1'))
AND 
(m.ts <= '2025-04-01 12:51:02+00'::timestamptz )
AND (m.ts >= '2019-07-03 11:50:00+00'::timestamptz) 
ORDER BY m.ts desc 
LIMIT 4;

The Timescale chunk does not vary in this period.

The table measure has 128M rows and the following indexes (probably redundant, I am not the only one who worked on this, but I am the only one left...)

indexname indexdef
idx_measure_node_sensor_ts CREATE INDEX idx_measure_node_sensor_ts ON public.measure USING btree (node_id, sensor_id, ts DESC)
measure2_pkey CREATE UNIQUE INDEX measure2_pkey ON public.measure USING btree (ts, node_id, sensor_id)
measure2_ts_idx CREATE INDEX measure2_ts_idx ON public.measure USING btree (ts DESC)

本文标签： postgresqlVery slow query on TimescaleDB when retrieving latest valuesStack Overflow

版权声明：本文标题：postgresql - Very slow query on TimescaleDB when retrieving latest values - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1743827852a2545973.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。