Bacula: indexing on media.lastwritten

I wrote this nearly a year ago, but did not publish it then.

Tonight I found this query running, so I tried an explain analyse on it:

bacula=#   explain analyse SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,Enabled,LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge FROM Media WHERE PoolId=26 AND MediaType='File' AND Enabled=1 AND VolStatus='Append'  ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId LIMIT 1;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=536.95..536.96 rows=1 width=205) (actual time=9672.909..9672.909 rows=1 loops=1)
   ->  Sort  (cost=536.95..536.96 rows=1 width=205) (actual time=9672.908..9672.908 rows=1 loops=1)
         Sort Key: ((lastwritten IS NULL)), lastwritten, mediaid
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on media  (cost=0.00..536.94 rows=1 width=205) (actual time=9672.848..9672.849 rows=1 loops=1)
               Filter: ((poolid = 26) AND (mediatype = 'File'::text) AND (enabled = 1) AND (volstatus = 'Append'::text))
               Rows Removed by Filter: 2597
 Total runtime: 9672.982 ms
(8 rows)

bacula=#

That’s 10 seconds.

Add an index:

bacula=# create index media_lastwritten_idx on media(lastwritten);
CREATE INDEX

Run the query again:

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=459.97..459.97 rows=1 width=205) (actual time=0.667..0.667 rows=1 loops=1)
   ->  Sort  (cost=459.97..459.97 rows=1 width=205) (actual time=0.667..0.667 rows=1 loops=1)
         Sort Key: ((lastwritten IS NULL)), lastwritten, mediaid
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on media  (cost=0.00..459.96 rows=1 width=205) (actual time=0.656..0.656 rows=1 loops=1)
               Filter: ((poolid = 26) AND (mediatype = 'File'::text) AND (enabled = 1) AND (volstatus = 'Append'::text))
               Rows Removed by Filter: 2597
 Total runtime: 0.727 ms
(8 rows)

Forgive my math, but that’s over 10,000 times faster now.