1

quassel=> \d backlog
                                        Table "public.backlog"
  Column   |            Type             |                          Modifiers
-----------+-----------------------------+-------------------------------------------------------------
 messageid | integer                     | not null default nextval('backlog_messageid_seq'::regclass)
 time      | timestamp without time zone | not null
 bufferid  | integer                     | not null
 type      | integer                     | not null
 flags     | integer                     | not null
 senderid  | integer                     | not null
 message   | text                        |
Indexes:
    "backlog_pkey" PRIMARY KEY, btree (messageid)
    "backlog_bufferid_idx" btree (bufferid, messageid DESC)
Foreign-key constraints:
    "backlog_bufferid_fkey" FOREIGN KEY (bufferid) REFERENCES buffer(bufferid) ON DELETE CASCADE
    "backlog_senderid_fkey" FOREIGN KEY (senderid) REFERENCES sender(senderid) ON DELETE SET NULL

2

quassel=> explain (analyze, buffers) SELECT messageid, time,  type, flags, sender, message
FROM backlog
LEFT JOIN sender ON backlog.senderid = sender.senderid
WHERE bufferid = 39
ORDER BY messageid DESC LIMIT 10;
                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.72..37.78 rows=10 width=102) (actual time=154410.353..154410.424 rows=10 loops=1)
   Buffers: shared hit=13952 read=19244
   ->  Nested Loop Left Join  (cost=0.72..145800.61 rows=39345 width=102) (actual time=154410.350..154410.414 rows=10 loops=1)
         Buffers: shared hit=13952 read=19244
         ->  Index Scan Backward using backlog_pkey on backlog  (cost=0.43..63830.21 rows=39345 width=62) (actual time=154410.327..154410.341 rows=10 loops=1)
               Filter: (bufferid = 39)
               Rows Removed by Filter: 1248320
               Buffers: shared hit=13921 read=19244
         ->  Index Scan using sender_pkey on sender  (cost=0.29..2.07 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=10)
               Index Cond: (backlog.senderid = senderid)
               Buffers: shared hit=31
 Total runtime: 154410.477 ms
(12 rows)

3

quassel=> begin;
BEGIN
quassel=> alter table backlog drop constraint backlog_pkey;
ALTER TABLE
quassel=> explain analyze SELECT messageid, time,  type, flags, sender, message
FROM backlog
LEFT JOIN sender ON backlog.senderid = sender.senderid
WHERE bufferid = 39
ORDER BY messageid DESC LIMIT 10;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.72..40.50 rows=10 width=102) (actual time=63.826..162.134 rows=10 loops=1)
   ->  Nested Loop Left Join  (cost=0.72..156518.91 rows=39345 width=102) (actual time=63.823..162.126 rows=10 loops=1)
         ->  Index Scan using backlog_bufferid_idx on backlog  (cost=0.43..74548.51 rows=39345 width=62) (actual time=63.798..63.814 rows=10 loops=1)
               Index Cond: (bufferid = 39)
         ->  Index Scan using sender_pkey on sender  (cost=0.29..2.07 rows=1 width=48) (actual time=8.532..9.825 rows=1 loops=10)
               Index Cond: (backlog.senderid = senderid)
 Total runtime: 162.377 ms
(7 rows)
 
quassel=> rollback;
ROLLBACK