The following query is executing in a long time, 500ms or so. This needs to be about
100ms or so in order to be acceptable. Can anyone spot any optimisations that I could
make to this query to bring the exec time down? Have I designed this query correctly?
Is joining to the same table every time like that the right thing to be doing?
I'm pretty sure I have indexes on the right fields etc. Thanks to anyone who can
spare a minute or three to look at this.
Here's the query and the query plan:
SELECT images.imageid,
images_sites.siteid,
images.title,
images.description,
albums.albumid,
albums.title AS albumtitle,
albums.private AS album_private,
images.entered,
images.taken,
images.private,
images.comments,
images.showcomments,
images.shownames,
images.commentlimit,
images.commentlimit_user,
images.trashed,
images.deleted,
imageid_file.fileid AS imageid_fileid,
imageid_file.filename AS imageid_filename,
imageid_file.size AS imageid_size,
imageid_file.md5 AS imageid_md5,
imageid_file.sha1 AS imageid_sha1,
size120_file.fileid AS size120_fileid,
size120_file.filename AS size120_filename,
size240_file.fileid AS size240_fileid,
size240_file.filename AS size240_filename,
size420_file.fileid AS size420_fileid,
size420_file.filename AS size420_filename,
size600_file.fileid AS size600_fileid,
size600_file.filename AS size600_filename,
size800_file.fileid AS size800_fileid,
size800_file.filename AS size800_filename,
size1024_file.fileid AS size1024_fileid,
size1024_file.filename AS size1024_filename,
size130sq_file.fileid AS size130sq_fileid,
size130sq_file.filename AS size130sq_filename,
size240sq_file.fileid AS size240sq_fileid,
size240sq_file.filename AS size240sq_filename,
size420sq_file.fileid AS size420sq_fileid,
size420sq_file.filename AS size420sq_filename
FROM images
JOIN files imageid_file ON (images.imageid = imageid_file.fileid)
JOIN files size120_file ON (images.size120 = size120_file.fileid)
JOIN files size240_file ON (images.size240 = size240_file.fileid)
JOIN files size420_file ON (images.size420 = size420_file.fileid)
JOIN files size600_file ON (images.size600 = size600_file.fileid)
JOIN files size800_file ON (images.size800 = size800_file.fileid)
JOIN files size1024_file ON (images.size1024 = size1024_file.fileid)
JOIN files size130sq_file ON (images.size130sq = size130sq_file.fileid)
JOIN files size240sq_file ON (images.size240sq = size240sq_file.fileid)
JOIN files size420sq_file ON (images.size420sq = size420sq_file.fileid)
JOIN images_sites ON (images_sites.imageid = images.imageid)
LEFT OUTER JOIN albums ON (images_sites.albumid = albums.albumid)
WHERE images_sites.siteid = 1
AND images_sites.albumid = 6
AND (albums.private IS NULL OR albums.private <= 5)
AND images.private <= 5
ORDER BY images.entered;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=14045.35..14045.43 rows=34 width=404) (actual time=507.877..507.881 rows=11 loops=1)
Sort Key: images.entered
Sort Method: quicksort Memory: 22kB
-> Nested Loop (cost=13084.62..14044.48 rows=34 width=404) (actual time=507.409..507.814 rows=11 loops=1)
-> Nested Loop (cost=13084.62..13986.47 rows=34 width=395) (actual time=507.399..507.724 rows=11 loops=1)
-> Nested Loop (cost=13084.62..13928.46 rows=34 width=386) (actual time=507.389..507.642 rows=11
loops=1)
-> Merge Join (cost=13084.62..13870.45 rows=34 width=377) (actual time=507.368..507.498 rows=11
loops=1)
Merge Cond: (images.imageid = images_sites.imageid)
-> Merge Join (cost=13057.19..13833.47 rows=3699 width=340) (actual time=505.822..507.116
rows=142loops=1)
Merge Cond: (imageid_file.fileid = images.imageid)
-> Index Scan using files_pkey on files imageid_file (cost=0.00..2346.61 rows=47327
width=95)(actual time=0.037..0.519 rows=441 loops=1)
-> Sort (cost=13057.16..13066.40 rows=3699 width=245) (actual time=505.751..505.880
rows=142loops=1)
Sort Key: images.imageid
Sort Method: external sort Disk: 752kB
-> Hash Join (cost=10763.72..12837.94 rows=3699 width=245) (actual
time=432.126..484.225rows=3699 loops=1)
Hash Cond: (size120_file.fileid = images.size120)
-> Seq Scan on files size120_file (cost=0.00..1682.27 rows=47327
width=17)(actual time=0.010..30.557 rows=47327 loops=1)
-> Hash (cost=10717.49..10717.49 rows=3699 width=236) (actual
time=413.613..413.613rows=3699 loops=1)
-> Hash Join (cost=8643.27..10717.49 rows=3699 width=236) (actual
time=350.447..403.727rows=3699 loops=1)
Hash Cond: (size240_file.fileid = images.size240)
-> Seq Scan on files size240_file (cost=0.00..1682.27
rows=47327width=17) (actual time=0.002..29.939 rows=47327 loops=1)
-> Hash (cost=8597.04..8597.04 rows=3699 width=227) (actual
time=332.231..332.231rows=3699 loops=1)
-> Hash Join (cost=6522.82..8597.04 rows=3699
width=227)(actual time=270.161..322.655 rows=3699 loops=1)
Hash Cond: (size420_file.fileid = images.size420)
-> Seq Scan on files size420_file
(cost=0.00..1682.27rows=47327 width=17) (actual time=0.002..30.203 rows=47327 loops=1)
-> Hash (cost=6476.59..6476.59 rows=3699
width=218)(actual time=251.625..251.625 rows=3699 loops=1)
-> Hash Join (cost=4402.38..6476.59
rows=3699width=218) (actual time=190.153..242.053 rows=3699 loops=1)
Hash Cond: (size600_file.fileid =
images.size600)
-> Seq Scan on files size600_file
(cost=0.00..1682.27rows=47327 width=17) (actual time=0.002..30.357 rows=47327 loops=1)
-> Hash (cost=4356.14..4356.14
rows=3699width=209) (actual time=171.758..171.758 rows=3699 loops=1)
-> Hash Join
(cost=2281.93..4356.14rows=3699 width=209) (actual time=110.588..162.765 rows=3699 loops=1)
Hash Cond:
(size800_file.fileid= images.size800)
-> Seq Scan on files
size800_file (cost=0.00..1682.27 rows=47327 width=17) (actual time=0.002..30.997 rows=47327 loops=1)
-> Hash
(cost=2235.69..2235.69rows=3699 width=200) (actual time=92.159..92.159 rows=3699 loops=1)
-> Hash Join
(cost=161.47..2235.69rows=3699 width=200) (actual time=33.021..83.512 rows=3699 loops=1)
Hash Cond:
(size1024_file.fileid= images.size1024)
-> Seq Scan
onfiles size1024_file (cost=0.00..1682.27 rows=47327 width=17) (actual time=0.002..30.880 rows=47327 loops=1)
-> Hash
(cost=115.24..115.24rows=3699 width=191) (actual time=14.067..14.067 rows=3699 loops=1)
-> Seq
Scanon images (cost=0.00..115.24 rows=3699 width=191) (actual time=0.043..6.580 rows=3699 loops=1)
Filter:(private <= 5)
-> Sort (cost=27.43..27.52 rows=34 width=41) (actual time=0.273..0.280 rows=11 loops=1)
Sort Key: images_sites.imageid
Sort Method: quicksort Memory: 17kB
-> Hash Left Join (cost=6.06..26.57 rows=34 width=41) (actual time=0.190..0.216
rows=11loops=1)
Hash Cond: (images_sites.albumid = albums.albumid)
Filter: ((albums.private IS NULL) OR (albums.private <= 5))
-> Bitmap Heap Scan on images_sites (cost=4.51..24.46 rows=34 width=12)
(actualtime=0.098..0.103 rows=11 loops=1)
Recheck Cond: (albumid = 6)
Filter: (siteid = 1)
-> Bitmap Index Scan on images_sites_albumid (cost=0.00..4.51 rows=34
width=0)(actual time=0.065..0.065 rows=11 loops=1)
Index Cond: (albumid = 6)
-> Hash (cost=1.54..1.54 rows=1 width=33) (actual time=0.050..0.050 rows=1
loops=1)
-> Seq Scan on albums (cost=0.00..1.54 rows=1 width=33) (actual
time=0.035..0.045rows=1 loops=1)
Filter: (albumid = 6)
-> Index Scan using files_pkey on files size420sq_file (cost=0.00..1.69 rows=1 width=17) (actual
time=0.009..0.009rows=1 loops=11)
Index Cond: (size420sq_file.fileid = images.size420sq)
-> Index Scan using files_pkey on files size240sq_file (cost=0.00..1.69 rows=1 width=17) (actual
time=0.004..0.005rows=1 loops=11)
Index Cond: (size240sq_file.fileid = images.size240sq)
-> Index Scan using files_pkey on files size130sq_file (cost=0.00..1.69 rows=1 width=17) (actual
time=0.004..0.004rows=1 loops=11)
Index Cond: (size130sq_file.fileid = images.size130sq)
Total runtime: 509.838 ms
