Remove redundant db calls from Login rest endpoint

[lamoboos223]

Description:

when I was testing the login endpoint I noticed in jaeger that it calls the db 2 times, the first db call to get the name and password_hash and in the second call it gets deactivated status. instead of making it two queries, I suggest to make it in one query.

here is a screenshot for you:

the suggested new query

SELECT name, password_hash, deactivated  FROM users WHERE lower(name) = lower(name)

also, you should remove the name from the select query because you already have it.

[lamoboos223]

diff --git a/synapse/handlers/auth.py b/synapse/handlers/auth.py
index 04eee52fe..8567e3faa 100644
--- a/synapse/handlers/auth.py
+++ b/synapse/handlers/auth.py
@@ -1059,7 +1059,7 @@ class AuthHandler:
 
     async def _find_user_id_and_pwd_hash(
         self, user_id: str
-    ) -> Optional[Tuple[str, str]]:
+    ) -> Optional[Dict[str, Tuple[str, bool]]]:
         """Checks to see if a user with the given id exists. Will check case
         insensitively, but will return None if there are multiple inexact
         matches.
@@ -1068,17 +1068,20 @@ class AuthHandler:
             A 2-tuple of `(canonical_user_id, password_hash)` or `None`
             if there is not exactly one match
         """
-        user_infos = await self.store.get_users_by_id_case_insensitive(user_id)
+        user_infos = await self.store.get_users_by_id_case_insensitive1(user_id)
+        for user_id, (password, deactivated) in user_infos.items():
+            pass
 
         result = None
         if not user_infos:
             logger.warning("Attempted to login as %s but they do not exist", user_id)
         elif len(user_infos) == 1:
             # a single match (possibly not exact)
-            result = user_infos.popitem()
+            item = user_infos.popitem()
+            result = {item[0]: item[1]}
         elif user_id in user_infos:
             # multiple matches, but one is exact
-            result = (user_id, user_infos[user_id])
+            result = {user_id: user_infos[user_id]}
         else:
             # multiple matches, none of them exact
             logger.warning(
@@ -1424,13 +1427,16 @@ class AuthHandler:
         lookupres = await self._find_user_id_and_pwd_hash(user_id)
         if not lookupres:
             return None
-        (user_id, password_hash) = lookupres
+            
+        # lookupres is a dict with one entry
+        # Get the first (and only) item as (user_id, (password_hash, deactivated))
+        canonical_user_id, (password_hash, deactivated) = next(iter(lookupres.items()))
 
         result = await self.validate_hash(password, password_hash)
         if not result:
             logger.warning("Failed password login for user %s", user_id)
             return None
-        return user_id
+        return canonical_user_id
 
     def generate_login_token(self) -> str:
         """Generates an opaque string, for use as an short-term login token"""
diff --git a/synapse/storage/databases/main/registration.py b/synapse/storage/databases/main/registration.py
index c582cf057..ddda5845a 100644
--- a/synapse/storage/databases/main/registration.py
+++ b/synapse/storage/databases/main/registration.py
@@ -660,6 +660,20 @@ class RegistrationWorkerStore(CacheInvalidationWorkerStore):
             allow_none=True,
         )
         return True if res == UserTypes.SUPPORT else False
+    
+    async def get_users_by_id_case_insensitive1(self, user_id: str) -> Dict[str, Tuple[str, bool]]:
+        """Gets users that match user_id case insensitively.
+
+        Returns:
+            A mapping of user_id -> (password_hash, deactivated).
+        """
+        def f(txn: LoggingTransaction) -> Dict[str, Tuple[str, bool]]:
+            sql = "SELECT name, password_hash, deactivated FROM users WHERE lower(name) = lower(?)"
+            txn.execute(sql, (user_id,))
+            result = cast(List[Tuple[str, str, bool]], txn.fetchall())
+            return {row[0]: (row[1], bool(row[2])) for row in result}
+
+        return await self.db_pool.runInteraction("get_users_by_id_case_insensitive1", f)
 
     async def get_users_by_id_case_insensitive(self, user_id: str) -> Dict[str, str]:
         """Gets users that match user_id case insensitively.
@@ -667,7 +681,6 @@ class RegistrationWorkerStore(CacheInvalidationWorkerStore):
         Returns:
              A mapping of user_id -> password_hash.
         """
-
         def f(txn: LoggingTransaction) -> Dict[str, str]:
             sql = "SELECT name, password_hash FROM users WHERE lower(name) = lower(?)"
             txn.execute(sql, (user_id,))