[openstreetmap/openstreetmap-website] Nearby users changesets times out (#2396)

[Tom Hughes]

Right, so the problem is the sort on changeset ID combined with the relatively low cardinality selection on the user ID of the changeset.

So the planner basically has two options, neither of them very good:

* Use the user index to find all the changesets for nearby users, then sort and limit that - wasteful because you have to find all the changsets for the user in order to then do the pagination.
* Use the changeset ID index to read the changesets in order, filtering on user ID until we have the number of results we want.

It is going (for my user at least) for the second option, but it will have to read through vast numbers of changesets by other people to try and find 20 by nearby users.

If there was an index on user_id+changeset_id then it might do better using that but given we want multiple users I'm not sure if it would be clever enough - it would either have to do a bunch of parallel index scans and merge them until it had enough results, or read 20 for user user then sort and discard the unwanted ones.

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/2396#issuecomment-542694772
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20191016/d513da8c/attachment.html>