get_bundled_aggregations takes a few seconds even when there is no relations

[MadLittleMods]

Mentioned in internal doc about speeding up /messages. Also see "2. Loading tons of events" in #13356

---

get_bundled_aggregations takes a few seconds even when there is no relations. This happens at the end of every /messages request.

For 500 messages that have 0 relations, reactions, threads, redactions, it takes 1 second to process. This is reproduced by running the many users, many messages Complement test I have in matrix-org/complement#443

Bunch of get_annotations_for_event and get_relations_for_event calls for each message:

Potential solutions

Batch up the lookups. It looks like most are just the magnitude of the round-trip to and from the database for each of the messages.

[clokep]

Batch up the lookups.

This was essentially the approach taken when I last looked at improving the performance here, see #11825 where this was done for edits and threads.

If the queries for references and annotations can't be batched we could at least run them in parallel (instead of in series) -- both on a per-event level and different relation level.

[clokep]

Batch up the lookups.

This was essentially the approach taken when I last looked at improving the performance here, see #11825 where this was done for edits and threads.

If the queries for references and annotations can't be batched we could at least run them in parallel (instead of in series) -- both on a per-event level and different relation level.

#14491 does this for annotations.

[clokep]

I'm going to close this, after deploying this change we saw an improvement in /message times a bit:

When looking at jaeger traces get_bundled_aggregations is now < 5% of a total /messages request.

[MadLittleMods]

Looks like a great improvement! I don't really notice it in the overall /messages response time quantiles (first graph). But it is very obvious in the large room (>1000 members) response time quantiles (in the graph below the first one). And we see another drop in number of messages that take more than 10s

https://grafana.matrix.org/d/dYoRgTgVz/messages-timing?orgId=1&from=1669125091174&to=1669150291174