long requests for locations history

1234a year ago

Hello! We are currently trying to use tracer with our 2500 devices, and we are issuing troubles with requesting route history from traccar. Our devices are sending data every 10 seconds, and we don't have a lot of historical data in database, maximum 7 days for each device

For example if im requesting 24hours ride history, the request handles for something like 80 seconds, its too much
We have tried it with two endpoints:

u = f'{base_url}/reports/route/?from=' + dt_from + '&to=' + dt_to + '&deviceId=1270'
u = f'{base_url}/positions/?from=' + dt_from + '&to=' + dt_to + '&deviceId=1268'

But the result is the same

We have tried the raw sql requests to Postgres, here is EXPLAIN:

EXPLAIN ANALYZE SELECT * FROM tc_positions where deviceid = 1264 and fixtime between '2023-12-05' and '2023-12-06';
QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using position_deviceid_fixtime on tc_positions  (cost=0.56..15454.25 rows=4751 width=848) (actual time=22.819..149809.259 rows=7904 loops=1)
   Index Cond: ((deviceid = 1264) AND (fixtime >= '2023-12-05 00:00:00'::timestamp without time zone) AND (fixtime <= '2023-12-06 00:00:00'::timestamp without time zone))
 Planning Time: 0.127 ms
 Execution Time: 149814.569 ms
(4 rows)

150 seconds =(

Maybe we did something wrong? Or its normal request time? We were using our own timescaleDB database, and we had result like 30-40 seconds on the same requests, so tracer db looks much more slower

Anton Tananaeva year ago

The index looks correct. The time probably means that your database is just slow or you have too much data.