summary refs log tree commit diff
path: root/synapse/storage
diff options
context:
space:
mode:
authorErik Johnston <erikj@element.io>2024-01-23 11:37:16 +0000
committerGitHub <noreply@github.com>2024-01-23 11:37:16 +0000
commitc925b4556798539d172664ed69734c8611f669d2 (patch)
tree7406bfa72116da736caf2ec6af4876a8df9ff5ca /synapse/storage
parentCorrectly mention previous copyright (#16820) (diff)
downloadsynapse-c925b4556798539d172664ed69734c8611f669d2.tar.xz
Speed up e2e device keys queries for bot accounts (#16841)
This helps with bot accounts with lots of non-e2e devices.

The change is basically to change the order of the join for the case of
using `INNER JOIN`
Diffstat (limited to 'synapse/storage')
-rw-r--r--synapse/storage/databases/main/end_to_end_keys.py29
1 files changed, 18 insertions, 11 deletions
diff --git a/synapse/storage/databases/main/end_to_end_keys.py b/synapse/storage/databases/main/end_to_end_keys.py
index d2206b32e9..c96371a0d3 100644
--- a/synapse/storage/databases/main/end_to_end_keys.py
+++ b/synapse/storage/databases/main/end_to_end_keys.py
@@ -408,17 +408,24 @@ class EndToEndKeyWorkerStore(EndToEndKeyBackgroundStore, CacheInvalidationWorker
         def get_e2e_device_keys_txn(
             txn: LoggingTransaction, query_clause: str, query_params: list
         ) -> None:
-            sql = (
-                "SELECT user_id, device_id, "
-                "    d.display_name, "
-                "    k.key_json"
-                " FROM devices d"
-                "    %s JOIN e2e_device_keys_json k USING (user_id, device_id)"
-                " WHERE %s AND NOT d.hidden"
-            ) % (
-                "LEFT" if include_all_devices else "INNER",
-                query_clause,
-            )
+            if include_all_devices:
+                sql = f"""
+                    SELECT user_id, device_id, d.display_name, k.key_json
+                    FROM devices d
+                    LEFT JOIN e2e_device_keys_json k USING (user_id, device_id)
+                    WHERE {query_clause} AND NOT d.hidden
+                """
+            else:
+                # We swap around `e2e_device_keys_json` and `devices`, as we
+                # want Postgres to query `e2e_device_keys_json` first as it will
+                # have fewer rows in it. This helps *a lot* with accounts with
+                # lots of non-e2e devices (such as bots).
+                sql = f"""
+                    SELECT user_id, device_id, d.display_name, k.key_json
+                    FROM e2e_device_keys_json k
+                    INNER JOIN devices d USING (user_id, device_id)
+                    WHERE {query_clause} AND NOT d.hidden
+                """
 
             txn.execute(sql, query_params)