[Bug]: API responds extremely slowly to post/list request under certain conditions

[Sjmarf]

Requirements

• Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
• Did you check to see if this issue already exists?
• Is this only a single bug? Do not put multiple bugs in one issue.
• Do you agree to follow the rules in our Code of Conduct?
• Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Summary

This only happens in a very specific edge case circumstance so isn't a critical issue. It takes ~20 seconds to perform the post/list request.

Steps to Reproduce

1. Log into an account that has <50 posts in the "moderator view" feed.
2. Go to api/v3/post/list?type_=ModeratorView&limit=50&page=1
3. Copy the next_page value from the response to clipboard
4. Go to api/v3/post/list?type_=ModeratorView&limit=50&page_cursor=%s. Replace %s with the string you copied in the previous step.
5. Observe that it takes ~20s for Lemmy to return the correct response (which is { "posts": [] })

Technical Details

I can't reproduce this issue by using the page parameter so it's possibly related to the cursor system

Lemmy Instance

Tested on lemmy.ml (0.19.6-beta.1) and sh.itjust.works (0.19.5)

[dessalines]

Could be really difficult to add indexes for that query. Would really be helpful if we had some more DB experts to help with these.