[openstreetmap/openstreetmap-website] Serve only trackable and identifiable points and add cursor pagination to the trackpoints API (PR #7322)

[Ruben L. Mendoza]

Rub21 left a comment (openstreetmap/openstreetmap-website#7322)

 Thanks for running it. If I read the plan right, it walks the whole gpx_id index (2.6 TB) because the planner picks it for the merge join with gpx_files, and it never touches the tile index. I am not sure why, I made two requests to the production API with the same bbox and the current query takes about 10 seconds per page:

```bash
 curl -s -o /dev/null -w "%{http_code} %{time_total}s\n" "https://api.openstreetmap.org/api/0.6/trackpoints?bbox=40.10,55.95,40.60,56.45&page=0"
 200 11.68s
 curl -s -o /dev/null -w "%{http_code} %{time_total}s\n" "https://api.openstreetmap.org/api/0.6/trackpoints?bbox=40.10,55.95,40.60,56.45&page=5"
 200 9.81s
``` 

Maybe the OFFSET 0 subquery is the wrong approach here :( 

The plan also shows the real limit, I think this bbox has 5.1 million points, and every page has to read all of them again. Even with the tile index a page would not be faster as we want,  I think it is fine to focus on the tracks PR (#7348) first and build the pagination on top of gpx_tracks.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/7322#issuecomment-5705861744
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/pull/7322/c5705861744 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20260916/d42b4e75/attachment.htm>