Fetch thread summaries for multiple events in a single query

[clokep]

This uses the same approach as #11660, but for the thread relation type instead of the edit relation type.

I did similar performance testing on this as in #11660 and #11612 and it seems to be ~15-20% improvement compared to develop when MSC3440 enabled. (The changes in this PR bring performance roughly on par as develop with MSC3440 disabled.)

Part of #11825.

Should be reviewable commit-by-commit.

[erikjohnston]

If you wanted to combine the queries for last value and counts, you could use a windows:

SELECT DISTINCT ON (parent.event_id) parent.event_id, count(*) OVER w, first_value(child.event_id) OVER w FROM events AS child
INNER JOIN event_relations USING (event_id)
INNER JOIN events AS parent ON
    parent.event_id = relates_to_id
    AND parent.room_id = child.room_id
WHERE
    relates_to_id = ?
    AND relation_type = 'io.element.thread'
WINDOW w AS (
    PARTITION BY parent.event_id
    ORDER BY parent.event_id, child.topological_ordering DESC, child.stream_ordering DESC
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

which I think is as efficient. What's going on here is that the count() OVER w and first_value(..) OVER w is operating on the set of rows defined in the window w, which in this case is all rows with the same parent.event_id.

But it's probably easier to keep them as separate queries :)

[clokep]

We chatted a bit about this and it seems like it would diverge the Postgresql and sqlite code more, which isn't ideal. @erikjohnston said he was mostly curious if it was doable. 👍

I'll leave this to a future enhancement.