[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 @tomhughes for running it. 


The page 10 plan from your run shows what happens:

```sql
    Parallel Index Scan Backward using points_gpxid_idx on gps_points  (actual time=2112.287..68102.921 rows=54748 loops=3)
      Index Cond: (gpx_id <= 11718378)
      Rows Removed by Filter: 216282942
```

The way I read that plan: the query can only use one of the two indexes, and with the cursor Postgres goes for `points_gpxid_idx`. It walks the traces backwards from the cursor and throws away every point outside the bbox until it has 5,000. In this case it read 216 million points to keep 5,000. So the time depends on how far the next trace in the bbox is in id order. Sometimes it is close and the page takes 0.3 s, sometimes it is millions of points away and the page takes minutes. I think the `gpx_id <=` bound I added is what pushed the planner down that path.


I changed the [query](https://github.com/openstreetmap/openstreetmap-website/pull/7322/commits/8dc1e7ebb) so the bbox scan runs in a subquery with `OFFSET 0`. The subquery has no ORDER or LIMIT, so Postgres has no reason to use the gpx_id index and reads the bbox with the tile index like page 1. The cursor filter, sort and limit happen outside, on those points only. On my test database (8M points) pages stay flat at around 300 ms.


One limit: with this table a page cannot be cheaper than page 1, since it still reads the whole bbox. What changes is that every page costs about the same and the sort stays in memory. Could you run this on prod? Same bbox as before: https://gist.github.com/Rub21/62fdc0985d4adfa926130ffd6ef0ba77


Two other bugs I found while walking all the pages of a bbox on my test database: I fixed in this [commit](https://github.com/openstreetmap/openstreetmap-website/pull/7322/commits/200b03289)!


- Some points have fractions of a second in the timestamp, and the cursor kept only whole seconds, so the next page repeated points. The cursor now uses unix microseconds.
- Some traces have several points with exactly the same timestamp:
  - [1456032](https://www.openstreetmap.org/trace/1456032/data), GPS TrackMaker: 21,398 points, every point is written twice with the same time.
  - [3142945](https://www.openstreetmap.org/trace/3142945/data), Tractive pet tracker: 40,914 points, up to 134 points in the same second.
  - [1178393](https://www.openstreetmap.org/trace/1178393/data), GPS TrackMaker: 2,129 points, all with `1899-12-30T00:00:01Z`, so the whole trace is one group.
  - [1190026](https://www.openstreetmap.org/trace/1190026/data), GPSBabel: 7,304 points with one timestamp.

  In my test (`40.2,56.0,40.6,56.2`, 128,442 points) there are 2,568 groups like that (same trace, segment and timestamp). If a page ended in the middle of such a group, the next page asked for `timestamp > cursor` and skipped the rest of the group, since they are equal, not greater. The page now loads one extra point to detect that. The page size changes in two cases:
  - If the page would end inside a group, it stops before the group, so it has a few points less than 5,000. The whole group comes in the next page.
  - If the group itself is bigger than a page (like 1178393 or 1190026), the page returns the whole group, so in that rare case it is bigger than 5,000. The other option was to lose those points.

 
Is it ok for the API that a page is not always 5,000 points? If the limit has to be strict, I can cut at 5,000 and accept losing points in that rare case.



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

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