From f18d7546c63ae30c4058d1ec6ab2d5c3b001d257 Mon Sep 17 00:00:00 2001
From: Mark Haines <mark.haines@matrix.org>
Date: Mon, 4 Jul 2016 15:48:25 +0100
Subject: Use a query that postgresql optimises better for get_events_around

---
 synapse/storage/stream.py | 28 ++++++++++++++++------------
 1 file changed, 16 insertions(+), 12 deletions(-)

diff --git a/synapse/storage/stream.py b/synapse/storage/stream.py
index b9ad965fd6..4dd11284e5 100644
--- a/synapse/storage/stream.py
+++ b/synapse/storage/stream.py
@@ -591,25 +591,28 @@ class StreamStore(SQLBaseStore):
 
         query_before = (
             "SELECT topological_ordering, stream_ordering, event_id FROM events"
-            " WHERE room_id = ? AND (topological_ordering < ?"
-            " OR (topological_ordering = ? AND stream_ordering < ?))"
-            " ORDER BY topological_ordering DESC, stream_ordering DESC"
-            " LIMIT ?"
+            " WHERE room_id = ? AND topological_ordering < ?"
+            " UNION ALL "
+            " SELECT topological_ordering, stream_ordering, event_id FROM events"
+            " WHERE room_id = ? AND topological_ordering = ? AND stream_ordering < ?"
+            " ORDER BY topological_ordering DESC, stream_ordering DESC LIMIT ?"
         )
 
         query_after = (
             "SELECT topological_ordering, stream_ordering, event_id FROM events"
-            " WHERE room_id = ? AND (topological_ordering > ?"
-            " OR (topological_ordering = ? AND stream_ordering > ?))"
-            " ORDER BY topological_ordering ASC, stream_ordering ASC"
-            " LIMIT ?"
+            " WHERE room_id = ? AND topological_ordering > ?"
+            " UNION ALL"
+            " SELECT topological_ordering, stream_ordering, event_id FROM events"
+            " WHERE room_id = ? AND topological_ordering = ? AND stream_ordering > ?"
+            " ORDER BY topological_ordering ASC, stream_ordering ASC LIMIT ?"
         )
 
         txn.execute(
             query_before,
             (
-                room_id, topological_ordering, topological_ordering,
-                stream_ordering, before_limit,
+                room_id, topological_ordering,
+                room_id, topological_ordering, stream_ordering,
+                before_limit,
             )
         )
 
@@ -630,8 +633,9 @@ class StreamStore(SQLBaseStore):
         txn.execute(
             query_after,
             (
-                room_id, topological_ordering, topological_ordering,
-                stream_ordering, after_limit,
+                room_id, topological_ordering,
+                room_id, topological_ordering, stream_ordering,
+                after_limit,
             )
         )
 
-- 
cgit 1.4.1