Do I need to delete the existing timestamp::date index first?

@geozelot - adding an index on the timestamp column seemed like a great idea but unfortunately did not help. Does the query need any re-structuring before it can benefit from this new index? thanks so much.

Also, should I replace my left join with inner join?

@geozelot I have updated the question with these changes

@geozelot I am now making use of the geography index by using p."geoJson" as geom (not transforming anymore), and at the end doing ST_DWithin(u."geom"::geography, a."geom"::geography, 30). Still the geoJson column index is not mentioned in the query plan.

the only difference with the Travelling Salesman Problem is that your solution does not return to the starting point, is this correct?

Also regarding what you said - "For 6 points only you could easily brute force the optimal solution (having only 6! = 720 permutations), but double that and your machine will burn." I tried this for 30 or so points and it was calculated in a couple of seconds!

One more thing, for each row in the result set, the optimized route is shown right? In terms of which id to visit first, then next, then next and so on - if I select uuid's in the final select clause instead of segment.

because it would need a lot of last minute code changes! so would only proceed if it is worth it.

thanks, may you live a happy and blessed life!

if I only take the row from the result set which has the exact number of items that I want, then it would be the result for the TSP problem right?