Select m.room.create event ID as a JOIN.

### Performance comparison

* Before
    * cold: 25 seconds
    * warm: 16 seconds
* After
    * cold: 13 seconds
    * warm: 11 seconds

Signed-off-by: Olivier Wilkinson (reivilibre) <olivier@librepush.net>

2 files changed, 48 insertions, 36 deletions

diff --git a/synapse/handlers/room_list.py b/synapse/handlers/room_list.py
index de5f5c63e8..e24f82f327 100644
--- a/synapse/handlers/room_list.py
+++ b/synapse/handlers/room_list.py
@@ -22,7 +22,6 @@ import msgpack
 from unpaddedbase64 import decode_base64, encode_base64
 
 from twisted.internet import defer
-from twisted.internet.defer import maybeDeferred
 
 from synapse.api.constants import EventTypes, JoinRules
 from synapse.api.errors import Codes, HttpResponseException
@@ -149,7 +148,12 @@ class RoomListHandler(BaseHandler):
         probing_limit = limit + 1 if limit is not None else None
 
         results = yield self.store.get_largest_public_rooms(
-            network_tuple, search_filter, probing_limit, pagination_token, forwards
+            network_tuple,
+            search_filter,
+            probing_limit,
+            pagination_token,
+            forwards,
+            fetch_creation_event_ids=from_federation,
         )
 
         def build_room_entry(room):
@@ -164,13 +168,12 @@ class RoomListHandler(BaseHandler):
             }
 
             # Filter out Nones – rather omit the field altogether
-            return {
-                k: v for k, v in entry.items() if v is not None
-            }
+            return {k: v for k, v in entry.items() if v is not None}
+
+        if from_federation:
+            room_creation_event_ids = [r["creation_event_id"] for r in results]
 
-        results = [
-            build_room_entry(r) for r in results
-        ]
+        results = [build_room_entry(r) for r in results]
 
         response = {}
         num_results = len(results)
@@ -195,37 +198,19 @@ class RoomListHandler(BaseHandler):
         if from_federation:
             # only show rooms with m.federate=True or absent (default is True)
 
-            # get rooms' state
-            room_state_ids = yield defer.gatherResults(
-                [
-                    maybeDeferred(self.store.get_current_state_ids, room["room_id"])
-                    for room in results
-                ],
-                consumeErrors=True,
-            )
-
-            # get rooms' creation state events' IDs
-            room_creation_event_ids = {
-                room["room_id"]: event_ids.get((EventTypes.Create, ""))
-                for (room, event_ids) in zip(results, room_state_ids)
-            }
-
-            # get rooms' creation state events
-            creation_events_by_id = yield self.store.get_events(
-                room_creation_event_ids.values()
-            )
-
-            # associate them with the room IDs
-            room_creation_events = {
-                room_id: creation_events_by_id[event_id]
-                for (room_id, event_id) in room_creation_event_ids.items()
-            }
+            # we already have rooms' creation state events' IDs
+            # so get rooms' creation state events
+            creation_events_by_id = yield self.store.get_events(room_creation_event_ids)
 
             # now filter out rooms with m.federate: False in their create event
             results = [
                 room
-                for room in results
-                if room_creation_events[room["room_id"]].content.get("m.federate", True)
+                for (room, room_creation_event_id) in zip(
+                    results, room_creation_event_ids
+                )
+                if creation_events_by_id[room_creation_event_id].content.get(
+                    "m.federate", True
+                )
             ]
 
         for room in results:
diff --git a/synapse/storage/room.py b/synapse/storage/room.py
index 3f92fba432..fa730e06fc 100644
--- a/synapse/storage/room.py
+++ b/synapse/storage/room.py
@@ -193,7 +193,13 @@ class RoomWorkerStore(SQLBaseStore):
 
     @defer.inlineCallbacks
     def get_largest_public_rooms(
-        self, network_tuple, search_filter, limit, pagination_token, forwards
+        self,
+        network_tuple,
+        search_filter,
+        limit,
+        pagination_token,
+        forwards,
+        fetch_creation_event_ids=False,
     ):
         """TODO doc this
 
@@ -204,6 +210,8 @@ class RoomWorkerStore(SQLBaseStore):
             pagination_token (str|None): if present, a room ID which is to be
                 the (first/last) included in the results.
             forwards (bool): true iff going forwards, going backwards otherwise
+            fetch_creation_event_ids (bool): if true, room creation_event_ids will
+                be included in the results.
 
         Returns:
             Rooms in order: biggest number of joined users first.
@@ -217,6 +225,14 @@ class RoomWorkerStore(SQLBaseStore):
             SELECT
                 room_id, name, topic, canonical_alias, joined_members,
                 avatar, history_visibility, joined_members
+        """
+
+        if fetch_creation_event_ids:
+            sql += """
+                , cse_create.event_id AS creation_event_id
+            """
+
+        sql += """
             FROM
                 room_stats
                 JOIN room_state USING (room_id)
@@ -229,6 +245,11 @@ class RoomWorkerStore(SQLBaseStore):
                 LEFT JOIN appservice_room_list arl USING (room_id)
             """
 
+        if fetch_creation_event_ids:
+            sql += """
+                LEFT JOIN current_state_events cse_create USING (room_id)
+            """
+
         sql += """
             WHERE
                 is_public
@@ -238,6 +259,12 @@ class RoomWorkerStore(SQLBaseStore):
                 )
         """
 
+        if fetch_creation_event_ids:
+            sql += """
+                AND cse_create.type = 'm.room.create'
+                AND cse_create.state_key = ''
+            """
+
         if pagination_token:
             pt_joined = yield self._simple_select_one_onecol(
                 table="room_stats",