← Back to team overview

launchpad-reviewers team mailing list archive

Re: [Merge] ~cjwatson/launchpad:optimize-latest-uploads into launchpad:master

 

Old query plan (just `EXPLAIN` as it was far too slow for `ANALYZE`):

```
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.14..186.71 rows=5 width=1288)
   ->  Nested Loop Left Join  (cost=2.14..1614196.46 rows=43728 width=1288)
         ->  Nested Loop  (cost=1.72..1595163.07 rows=43728 width=1268)
               ->  Nested Loop  (cost=1.30..1576129.69 rows=43728 width=1268)
                     ->  Nested Loop  (cost=0.87..1540544.13 rows=43728 width=8)
                           ->  Index Scan Backward using distroreleasequeue_pkey on packageupload  (cost=0.44..1356507.63 rows=173502 width=4)
                                 Filter: ((archive = ANY ('{1,534}'::integer[])) AND (status = 3) AND (distroseries = 108))
                           ->  Index Scan using packageuploadsource__packageupload__key on packageuploadsource  (cost=0.43..1.06 rows=1 width=8)
                                 Index Cond: (packageupload = packageupload.id)
                     ->  Index Scan using sourcepackagerelease_pkey on sourcepackagerelease  (cost=0.43..0.81 rows=1 width=1264)
                           Index Cond: (id = packageuploadsource.sourcepackagerelease)
               ->  Index Only Scan using sourcepackagename_pkey on sourcepackagename  (cost=0.42..0.44 rows=1 width=4)
                     Index Cond: (id = sourcepackagerelease.sourcepackagename)
         ->  Index Scan using sourcepackagename_pkey on sourcepackagename _prejoin1  (cost=0.42..0.44 rows=1 width=20)
               Index Cond: (sourcepackagerelease.sourcepackagename = id)
(15 rows)
```

New query plan:

```
launchpad_staging=> EXPLAIN (ANALYZE, BUFFERS) WITH RelevantUpload AS (SELECT id FROM PackageUpload WHERE status = 3 AND distroseries = 108 AND archive IN (1, 534)) SELECT SourcePackageRelease.*, "_prejoin1".id, "_prejoin1".name FROM RelevantUpload, PackageUploadSource, SourcePackageName, SourcePackageRelease LEFT JOIN SourcePackageName AS "_prejoin1" ON SourcePackageRelease.sourcepackagename = "_prejoin1".id WHERE sourcepackagerelease.id=packageuploadsource.sourcepackagerelease AND sourcepackagerelease.sourcepackagename=sourcepackagename.id AND packageuploadsource.packageupload=relevantupload.id ORDER BY relevantupload.id DESC LIMIT 5;
                                                                                                      QUERY PLAN                                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=237905.38..237919.24 rows=5 width=1483) (actual time=236.269..905.036 rows=5 loops=1)
   Buffers: shared hit=80357
   CTE relevantupload
     ->  Bitmap Heap Scan on packageupload  (cost=3415.68..219334.98 rows=173502 width=4) (actual time=22.161..86.424 rows=139208 loops=1)
           Recheck Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 108) AND (status = 3))
           Heap Blocks: exact=27781
           Buffers: shared hit=28322
           ->  Bitmap Index Scan on packageupload__archive__distroseries__status__idx  (cost=0.00..3372.30 rows=173502 width=0) (actual time=16.410..16.410 rows=139208 loops=1)
                 Index Cond: ((archive = ANY ('{1,534}'::integer[])) AND (distroseries = 108) AND (status = 3))
                 Buffers: shared hit=541
   ->  Nested Loop Left Join  (cost=18570.40..499505.59 rows=173502 width=1483) (actual time=236.268..905.030 rows=5 loops=1)
         Buffers: shared hit=80357
         ->  Nested Loop  (cost=18569.98..423985.81 rows=173502 width=1463) (actual time=236.262..905.008 rows=5 loops=1)
               Buffers: shared hit=80337
               ->  Nested Loop  (cost=18569.56..348466.03 rows=173502 width=1463) (actual time=236.250..904.970 rows=5 loops=1)
                     Buffers: shared hit=80317
                     ->  Merge Join  (cost=18569.13..207271.25 rows=173502 width=8) (actual time=236.228..904.908 rows=5 loops=1)
                           Merge Cond: (packageuploadsource.packageupload = relevantupload.id)
                           Buffers: shared hit=80297
                           ->  Index Scan Backward using packageuploadsource__packageupload__key on packageuploadsource  (cost=0.43..169948.91 rows=6460446 width=8) (actual time=0.011..483.255 rows=2408882 loops=1)
                                 Buffers: shared hit=51975
                           ->  Sort  (cost=18568.70..19002.45 rows=173502 width=4) (actual time=174.576..174.614 rows=503 loops=1)
                                 Sort Key: relevantupload.id DESC
                                 Sort Method: quicksort  Memory: 9939kB
                                 Buffers: shared hit=28322
                                 ->  CTE Scan on relevantupload  (cost=0.00..3470.04 rows=173502 width=4) (actual time=22.165..128.897 rows=139208 loops=1)
                                       Buffers: shared hit=28322
                     ->  Index Scan using sourcepackagerelease_pkey on sourcepackagerelease  (cost=0.43..0.81 rows=1 width=1459) (actual time=0.008..0.008 rows=1 loops=5)
                           Index Cond: (id = packageuploadsource.sourcepackagerelease)
                           Buffers: shared hit=20
               ->  Index Only Scan using sourcepackagename_pkey on sourcepackagename  (cost=0.42..0.44 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=5)
                     Index Cond: (id = sourcepackagerelease.sourcepackagename)
                     Heap Fetches: 5
                     Buffers: shared hit=20
         ->  Index Scan using sourcepackagename_pkey on sourcepackagename _prejoin1  (cost=0.42..0.44 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=5)
               Index Cond: (sourcepackagerelease.sourcepackagename = id)
               Buffers: shared hit=20
 Planning time: 1.732 ms
 Execution time: 907.883 ms
(39 rows)
```
-- 
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/403735
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:optimize-latest-uploads into launchpad:master.


Follow ups