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.
