I've been running Soju for a few months, storing messages in a Postgres database. My install has ~4.6 million messages:

soju=# select count(*) from "Message";
   count
---------
  4629046

I went through some of the code for retrieving messages from Postgres found a potential optimization for retrieving the most recent message of a given target.

To be honest - I'm not sure how often retrieving the most recent message of a target is, I haven't gotten to build a custom soju yet and see how much of an impact this makes, but it gets one query from 5 seconds down to under a second so, it may add up. At some point I'll try patching my copy with this and see how noticeable the performance improvement is.

The current query for finding the most recent message for a given target is:

	SELECT m.id FROM "Message" AS m, "MessageTarget" as t
	WHERE t.network = $1 AND t.target = $2 AND m.target = t.id
	ORDER BY m.time DESC LIMIT 1

This results in a big hash join when "Message" gets large, right now on my install this query takes about 5 seconds, here's the output of "explain analyze", I've replaced the target channel with "(my-target)":

  Limit  (cost=195466.63..195466.75 rows=1 width=12) (actual 
time=4798.091..4849.794 rows=1 loops=1)
    ->  Gather Merge  (cost=195466.63..195842.09 rows=3218 width=12) 
(actual time=4753.783..4805.483 rows=1 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          ->  Sort  (cost=194466.61..194470.63 rows=1609 width=12) 
(actual time=4566.813..4566.829 rows=1 loops=3)
                Sort Key: m."time" DESC
                Sort Method: top-N heapsort  Memory: 25kB
                Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
                Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
                ->  Hash Join  (cost=8.18..194458.56 rows=1609 width=12) 
(actual time=31.911..4490.601 rows=181417 loops=3)
                      Hash Cond: (m.target = t.id)
                      ->  Parallel Seq Scan on "Message" m  
(cost=0.00..189365.14 rows=1930414 width=16) (actual 
time=0.710..4029.037 rows=1543032 loops=3)
                      ->  Hash  (cost=8.17..8.17 rows=1 width=4) (actual 
time=28.123..28.129 rows=1 loops=3)
                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                            ->  Index Scan using 
"MessageTarget_network_target_key" on "MessageTarget" t  
(cost=0.15..8.17 rows=1 width=4) (actual time=28.068..28.078 rows=1 
loops=3)
                                  Index Cond: ((network = 1) AND (target 
= '(my-target)'::text))
  Planning Time: 1.594 ms
  JIT:
    Functions: 37
    Options: Inlining false, Optimization false, Expressions true, 
Deforming true
    Timing: Generation 5.493 ms, Inlining 0.000 ms, Optimization 10.794 
ms, Emission 117.567 ms, Total 133.854 ms
  Execution Time: 4852.882 ms

I noticed if I manually get the target id the result is incredibly fast:

explain analyze select m.id from "Message" as m where m.target=396097 
order by m.time limit 1;

  Limit  (cost=0.43..1.63 rows=1 width=12) (actual time=0.380..0.381 
rows=1 loops=1)
    ->  Index Scan using "MessageIndex" on "Message" m  
(cost=0.43..682062.37 rows=569093 width=12) (actual time=0.371..0.371 
rows=1 loops=1)
          Index Cond: (target = 396097)
  Planning Time: 0.753 ms
  Execution Time: 0.448 ms

So, I crafted a query that forces that to happen - it looks up the MessageTarget id as a subquery, turns it into an array, and accesses the first element:

explain analyze select m.id from "Message" as m where 
m.target=(array(select t.id from "MessageTarget" as t where t.network=1 
and t.target='(my-target)'))[1] order by m.time desc limit 1;

  Limit  (cost=8.60..11.09 rows=1 width=12) (actual time=0.396..0.398 
rows=1 loops=1)
    InitPlan 1 (returns $0)
      ->  Index Scan using "MessageTarget_network_target_key" on 
"MessageTarget" t  (cost=0.15..8.17 rows=1 width=4) (actual 
time=0.188..0.191 rows=1 loops=1)
            Index Cond: ((network = 1) AND (target = 
'(my-target)'::text))
    ->  Index Scan Backward using "MessageIndex" on "Message" m  
(cost=0.43..499757.62 rows=201438 width=12) (actual time=0.395..0.395 
rows=1 loops=1)
          Index Cond: (target = ($0)[1])
  Planning Time: 0.698 ms
  Execution Time: 0.527 ms

This should give the same result as the original but be a lot faster with larger message stores:

	SELECT m.id FROM "Message" AS m
	WHERE m.target = (ARRAY(
		SELECT t.id
		FROM "MessageTarget" as t
		WHERE t.network = $1 AND t.target = $2
	))[1]
	ORDER BY m.time DESC LIMIT 1