Pre-filling caches can take a long time on matrix.org

[NegativeMjark]

This means that the server takes a long time to start.

https://github.com/matrix-org/synapse/blob/v0.18.5/synapse/storage/__init__.py#L188

SELECT user_id, MAX(stream_id) FROM device_inbox WHERE stream_id > 274181 - 100000 GROUP BY user_id

explain SELECT user_id, MAX(stream_id) FROM device_inbox WHERE stream_id > 274181 - 100000 GROUP BY user_id;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=259595.44..259601.27 rows=583 width=27)
   Group Key: user_id
   ->  Index Scan using device_inbox_stream_id on device_inbox  (cost=0.43..250096.69 rows=1899751 width=27)
         Index Cond: (stream_id > 174181)
(4 rows)

[NegativeMjark]

The query should probably be:

SELECT user_id, MAX(stream_id) FROM (SELECT user_id, stream_id FROM device_inbox ORDER BY stream_id DESC limit 100000) AS a GROUP BY user_id;

                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=12044.46..12059.26 rows=1480 width=27)
   Group Key: device_inbox.user_id
   ->  Limit  (cost=0.43..10544.46 rows=100000 width=27)
         ->  Index Scan Backward using device_inbox_stream_id on device_inbox  (cost=0.43..508668.03 rows=4824225 width=27)