Search Postgresql Archives

Query optimisation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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=142 loops=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=142 loops=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.225 rows=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.613 rows=3699 loops=1)
                                                  ->  Hash Join  (cost=8643.27..10717.49 rows=3699 width=236) (actual time=350.447..403.727 rows=3699 loops=1)
                                                        Hash Cond: (size240_file.fileid = images.size240)
                                                        ->  Seq Scan on files size240_file  (cost=0.00..1682.27 rows=47327 width=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.231 rows=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.27 rows=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=3699 width=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.27 rows=47327 width=17) (actual time=0.002..30.357 rows=47327 loops=1)
                                                                                ->  Hash  (cost=4356.14..4356.14 rows=3699 width=209) (actual time=171.758..171.758 rows=3699 loops=1)
                                                                                      ->  Hash Join  (cost=2281.93..4356.14 rows=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.69 rows=3699 width=200) (actual time=92.159..92.159 rows=3699 loops=1)
                                                                                                  ->  Hash Join  (cost=161.47..2235.69 rows=3699 width=200) (actual time=33.021..83.512 rows=3699 loops=1)
                                                                                                        Hash Cond: (size1024_file.fileid = images.size1024)
                                                                                                        ->  Seq Scan on files 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.24 rows=3699 width=191) (actual time=14.067..14.067 rows=3699 loops=1)
                                                                                                              ->  Seq Scan on 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=11 loops=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) (actual time=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.045 rows=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.009 rows=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.005 rows=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.004 rows=1 loops=11)
              Index Cond: (size130sq_file.fileid = images.size130sq)
Total runtime: 509.838 ms






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux