Quassel IRC

My QuasselCore has a large database - right now, I have 73,778,201 rows in the backlog table. I also have a lot of buffers - several of which for channels which no longer exist on networks which also no longer exist. For obvious reasons, there has been no activity in those channels for a long time.

One such buffer is bufferid 170, whose most recent message was messageid 62,238,289. According to PostgreSQL logs, Quassel generates the following query for it (or rather, close to this; Quassel uses prepared statements according to the log):

SELECT messageid, time,  type, flags, sender, message FROM backlog LEFT JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = 170 ORDER BY messageid DESC LIMIT 5;

This query, according to EXPLAIN ANALYZE, takes 174 seconds (!) to run:

                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.00..28.34 rows=5 width=112) (actual time=173945.226..174009.210 rows=5 loops=1)
   ->  Nested Loop Left Join  (cost=1.00..14617727.96 rows=2673195 width=112) (actual time=173945.223..174009.203 rows=5 loops=1)
         ->  Index Scan Backward using backlog_pkey on backlog  (cost=0.57..3082649.25 rows=2673195 width=68) (actual time=173891.479..173912.633 rows=5 loops=1)
               Filter: (bufferid = 170)
               Rows Removed by Filter: 12429359
         ->  Index Scan using sender_pkey on sender  (cost=0.43..4.31 rows=1 width=52) (actual time=19.211..19.215 rows=1 loops=5)
               Index Cond: (backlog.senderid = senderid)
 Planning time: 0.642 ms
 Execution time: 174009.380 ms
(9 rows)

Is a LEFT JOIN actually needed in this case? After adding the following index:

CREATE INDEX "backlog_messageid_backwards" ON backlog USING btree (messageid DESC);

...the same query, using a normal JOIN instead of a LEFT JOIN, instead takes 6.6 seconds:

reimu_quassel=> EXPLAIN ANALYZE SELECT messageid, time,  type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = 170 ORDER BY messageid DESC LIMIT 5;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.00..28.35 rows=5 width=112) (actual time=6582.896..6583.055 rows=5 loops=1)
   ->  Nested Loop  (cost=1.00..14624504.64 rows=2673212 width=112) (actual time=6582.894..6583.053 rows=5 loops=1)
         ->  Index Scan Backward using backlog_pkey on backlog  (cost=0.57..3082667.12 rows=2673212 width=68) (actual time=6582.614..6582.718 rows=5 loops=1)
               Filter: (bufferid = 170)
               Rows Removed by Filter: 12429831
         ->  Index Scan using sender_pkey on sender  (cost=0.43..4.31 rows=1 width=52) (actual time=0.035..0.036 rows=1 loops=5)
               Index Cond: (senderid = backlog.senderid)
 Planning time: 2.285 ms
 Execution time: 6583.132 ms
(9 rows)

In any case, some sort of optimization for backlog fetching in cases like this is clearly needed.