Fix get_users_who_share_room_with_user SQL github/anoa/presence_speedups anoa/presence_speedups

Three fixes here:

* Using UNION only selects DISTINCT rows from each query. However, if we DISTINCT the rows during the query, and then use UNION ALL which doesn't attempt to select distinct rows, we get a 1/3rd speedup in query time!
* We should select other_user_id instead of user_id from users_who_share_private_rooms, as user_id will always be the requesting user.
* Added p1.user_id != p2.user_id to filter out the same entries between each table in the users_in_public_rooms query.

1 files changed, 4 insertions, 3 deletions

diff --git a/synapse/storage/databases/main/roommember.py b/synapse/storage/databases/main/roommember.py
index fbedc18191..55a56c6c74 100644
--- a/synapse/storage/databases/main/roommember.py
+++ b/synapse/storage/databases/main/roommember.py
@@ -496,9 +496,10 @@ class RoomMemberWorkerStore(EventsWorkerStore):
                 FROM users_in_public_rooms as p1
                 INNER JOIN users_in_public_rooms as p2
                     ON p1.room_id = p2.room_id
+                    AND p1.user_id != p2.user_id
                     AND p1.user_id = ?
-                UNION
-                SELECT DISTINCT user_id
+                UNION ALL
+                SELECT DISTINCT other_user_id
                 FROM users_who_share_private_rooms
                 WHERE
                     user_id = ?
@@ -512,7 +513,7 @@ class RoomMemberWorkerStore(EventsWorkerStore):
             "get_users_who_share_room_with_user", _get_users_who_share_room_with_user
         )
 
-        return {row["user_id"] for row in rows}
+        return {row.get("user_id") or row.get("other_user_id") for row in rows}
 
     async def get_joined_users_from_context(
         self, event: EventBase, context: EventContext