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.
_______________________________________________
Mailing list: https://launchpad.net/~launchpad-reviewers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~launchpad-reviewers
More help : https://help.launchpad.net/ListHelp
