Skip to content

Improve performance of notes/following API

Caramel requested to merge Caramel/Sharkey:notes-following-api-performance into develop

What does this PR do? (Please give us a brief description of what this PR does.)

Currently retrieving following list on large instances like woem.men takes a long time, and often timeouts: https://woem.men/notes/a0ctnbzb6ooy02qq

This is because the query plan generated by PostgreSQL 16 (don't know about other versions) fetches last posts for every single user before trimming this list down.

I tested the changed query on sylveon.social, for sugar's account, after creating this table and filling it with data. That's not necessarily the most effective approach, as sylveon.social uses Iceshrimp, not Sharkey (we don't have a Sharkey testing instance that has a lot of data). This improved query times for executing the following feed query from 2.6 seconds to 20 ms.

I also tested this change on localhost, albeit not for performance (localhost database is pretty much empty, so it makes no difference here).

Contribution Guidelines By submitting this merge request, you agree to follow our Contribution Guidelines

  • I agree to follow this project's Contribution Guidelines
  • I have made sure to test this pull request
Edited by Caramel

Merge request reports