Optimize more Postgresql queries for getting converstaion members (#4901)

akshaymankar · web-flow · commit 1d1751611b4a · 2025-12-11T14:20:18.000+01:00
diff --git a/changelog.d/3-bug-fixes/optimize-conv-member-queries b/changelog.d/3-bug-fixes/optimize-conv-member-queries
@@ -1 +1 @@
-Optimize Postgresql queries for getting conversation members
+Optimize Postgresql queries for getting conversation members (#4896, ##)
diff --git a/libs/wire-subsystems/src/Wire/ConversationStore/Postgres.hs b/libs/wire-subsystems/src/Wire/ConversationStore/Postgres.hs
@@ -272,15 +272,26 @@ getConversationsImpl cids = do
                                 (otr_archived :: boolean?), (otr_archived_ref :: text?), (hidden :: boolean?), (hidden_ref :: text?), (conversation_role :: text?)
                          FROM conversation_member
                          WHERE conv = ANY ($1 :: uuid[])
-                         OR conv IN (SELECT parent_conv FROM conversation WHERE id = ANY ($1 :: uuid[]))
+
+                         UNION ALL
+
+                         SELECT (conv :: uuid), ("user" :: uuid), (service :: uuid?), (provider :: uuid?), (otr_muted_status :: integer?), (otr_muted_ref :: text?),
+                                (otr_archived :: boolean?), (otr_archived_ref :: text?), (hidden :: boolean?), (hidden_ref :: text?), (conversation_role :: text?)
+                         FROM conversation_member
+                         WHERE conv IN (SELECT parent_conv FROM conversation WHERE id = ANY ($1 :: uuid[]))
                         |]
     selectAllRemoteMembers :: Hasql.Statement [ConvId] [RemoteMemberRow]
     selectAllRemoteMembers =
       dimapPG @[_] @(Vector _)
         [vectorStatement|SELECT (conv :: uuid), (user_remote_domain :: text), (user_remote_id :: uuid), (conversation_role :: text)
                          FROM local_conversation_remote_member
                          WHERE conv = ANY ($1 :: uuid[])
-                         OR conv IN (SELECT parent_conv FROM conversation WHERE id = ANY ($1 :: uuid[]))
+
+                         UNION ALL
+
+                         SELECT (conv :: uuid), (user_remote_domain :: text), (user_remote_id :: uuid), (conversation_role :: text)
+                         FROM local_conversation_remote_member
+                         WHERE conv IN (SELECT parent_conv FROM conversation WHERE id = ANY ($1 :: uuid[]))
                         |]
 
     findMembers :: (HasField "id_" a b, Eq b) => ConvId -> Maybe ConvId -> [(ConvId, a)] -> [a]

Original file line number	Diff line number	Diff line change
`@@ -1 +1 @@`
`1`		`-Optimize Postgresql queries for getting conversation members`
	`1`	`+Optimize Postgresql queries for getting conversation members (#4896, ##)`