Hi,
I don't have much data (several devices for less than a month) but the query of positions last too long.
The query below takes 70 seconds to complete and returns 341k rows for 16 days:
select * from positions where deviceId = 24 order by fixtime
As i can see there is already an index created by Traccar named position_deviceid_fixtime but I created another index like this, the script completed with success in less than a second (why too fast?)
CREATE INDEX IX_positions_deviceId_fixtime ON positions USING btree(deviceId, fixtime)
But the positions query still takes too much time (no difference). Should i do anything like rebuild, reindex, restart or something? Or my script is wrong?
Thanks in advance.
You should start by studying query execution plan. It will tell you why query is slow and how to improve it.
It looks like it already uses indexes which is created by me.
Here is the Explain response:
Index Scan using ix_positions_deviceid_fixtime on positions (cost=0.42..69964.32 rows=338428 width=181)
Index Cond: (deviceid = 24)
But still too slow.
The problem is that it doesn't use index properly. Looks like it does full scan and then applies condition, if I'm understanding explanation correctly. If you need all data for a device you might need to create a separate index just on deviceid column.
Hi,
I don't have much data (several devices for less than a month) but the query of positions last too long.
The query below takes 70 seconds to complete and returns 341k rows for 16 days:
select * from positions where deviceId = 24 order by fixtime
As i can see there is already an index created by Traccar named position_deviceid_fixtime but I created another index like this, the script completed with success in less than a second (why too fast?)
CREATE INDEX IX_positions_deviceId_fixtime ON positions USING btree(deviceId, fixtime)
But the positions query still takes too much time (no difference). Should i do anything like rebuild, reindex, restart or something? Or my script is wrong?
Thanks in advance.