[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)

I measured the cursor on both `gps_points` and `gpx_tracks`, using a larger bbox (`40.10, 55.95, 40.60, 56.45`) that holds 137,325 points. I set the page to 500 points instead of 5,000, so I could reach page 100 and page 200.


| page | `gps_points` | points | `gpx_tracks` | points |
|---|---|---|---|---|
| 1 (no cursor) | 26,788.512 ms | 500 | 21.278 ms | 75 |
| 10 | 825.503 ms | 500 | 4.542 ms | 500 |
| 50 | 524.585 ms | 500 | 1.176 ms | 500 |
| 100 | 482.819 ms | 500 | 1.131 ms | 500 |
| 200 | 717.324 ms | 500 | 1.063 ms | 500 |

Page 1 of `gpx_tracks` returns 75 points because its first segment falls mostly outside the bbox, so those two are only comparable from page 10 on. 

## Execute plans using cursor in gps_points table

https://gist.github.com/Rub21/9abdead9f826dcc70ca72ac3fc4710ab

## Execute plans using cursor in gpx_tracks table

https://gist.github.com/Rub21/715b96d09d9da082caf8071da0e86427

Note: One thing about my numbers: I ran this on an M2 Mac, in Docker, and the Postgres container is amd64 under emulation


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

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