Performance problems with querying for a large numbers of redirects

[Scotchester]

We have an, ah, unseemly number of redirects (almost 100,000), and it seems like it's starting to timeout on the GraphQL query for them at this point. It's not entirely clear why it has started to do this, because the number of redirects hasn't jumped dramatically, but it seems to have been triggered by an upgrade from 0.18 to 0.19.1. Perhaps some kind of performance regression? (Several GraphQL subdependencies came along with that update, too.)

We've since upgraded to 0.24, which is the latest version that supports the version of Wagtail we're currently running (4.2.4 – yeah, I know…), but it still takes a very long time, often timing out after trying for 60 seconds.

If there isn't a performance regression that can easily be diagnosed and fixed, some other potential ways to address this could be:

• Enable pagination support on the redirects query
• Allow fetching only redirects added/modified after a certain datetime, so the frontend can ask for only what's changed since it last updated its redirects cache

[Scotchester]

P.S.: We also tried out fetching redirects via the REST API, and it also struggles with that amount. This could point to a performance regression in the Django database query, unrelated to wagtail-grapple, introduced by our simultaneous upgrade from Wagtail 4.1.1 to Wagtail 4.2.4.

[zerolab]

@Scotchester in some little way I am glad it is not grapple, but it is worth getting some numbers for core, maybe even the generated queries between versions, if you can.

I agree pagination / filtering would be beneficial for everyone. Do you happen to have the time/capacity for a PR?

Some bits for reference:

• the pagination structures: https://github.com/torchbox/wagtail-grapple/blob/main/grapple/types/structures.py#L155-L278
• get_paginated_result / resolve_paginated_queryset