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

[Colin Watson]

Make DistroSeries.getLatestUploads use a better index

Unassisted, PostgreSQL's planner apparently decides to do an index scan on `PackageUpload`'s primary key, which is very slow. Use a CTE to force it to use the more appropriate index on `PackageUpload (archive, distroseries, status)`.

[Colin Watson (cjwatson)]

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:...

[Cristian Gonzalez (cristiangsp)]

Looks good!

review: Approve

diff --git a/lib/lp/registry/model/distroseries.py b/lib/lp/registry/model/distroseries.py
index 08673ce..b2dc72b 100644
--- a/lib/lp/registry/model/distroseries.py
+++ b/lib/lp/registry/model/distroseries.py
@@ -1,4 +1,4 @@
-# Copyright 2009-2020 Canonical Ltd.  This software is licensed under the
+# Copyright 2009-2021 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Database classes for a distribution series."""
@@ -14,7 +14,10 @@ __all__ = [
 
 import collections
 from io import BytesIO
-from operator import attrgetter
+from operator import (
+    attrgetter,
+    itemgetter,
+    )
 
 import apt_pkg
 from lazr.delegates import delegate_to
@@ -30,10 +33,14 @@ from sqlobject import (
     )
 from storm.expr import (
     And,
+    Column,
     Desc,
     Join,
     Or,
+    Select,
     SQL,
+    Table,
+    With,
     )
 from storm.locals import (
     Int,
@@ -167,6 +174,7 @@ from lp.soyuz.model.publishing import (
 from lp.soyuz.model.queue import (
     PackageUpload,
     PackageUploadQueue,
+    PackageUploadSource,
     )
 from lp.soyuz.model.section import Section
 from lp.soyuz.model.sourcepackagerelease import SourcePackageRelease
@@ -1235,23 +1243,31 @@ class DistroSeries(SQLBase, BugTargetBase, HasSpecificationsMixin,
 
     def getLatestUploads(self):
         """See `IDistroSeries`."""
-        query = """
-        sourcepackagerelease.id=packageuploadsource.sourcepackagerelease
-        AND sourcepackagerelease.sourcepackagename=sourcepackagename.id
-        AND packageuploadsource.packageupload=packageupload.id
-        AND packageupload.status=%s
-        AND packageupload.distroseries=%s
-        AND packageupload.archive IN %s
-        """ % sqlvalues(
-                PackageUploadStatus.DONE,
-                self,
-                self.distribution.all_distro_archive_ids)
+        # Without this CTE, PostgreSQL sometimes decides to scan the primary
+        # key index on PackageUpload instead, which is very slow.
+        RelevantUpload = Table("RelevantUpload")
+        relevant_upload_cte = With(
+            RelevantUpload.name,
+            Select(
+                PackageUpload.id,
+                And(
+                    PackageUpload.status == PackageUploadStatus.DONE,
+                    PackageUpload.distroseries == self,
+                    PackageUpload.archiveID.is_in(
+                        self.distribution.all_distro_archive_ids))))
+        clauses = [
+            SourcePackageRelease.id ==
+                PackageUploadSource.sourcepackagereleaseID,
+            SourcePackageRelease.sourcepackagenameID == SourcePackageName.id,
+            PackageUploadSource.packageuploadID == Column(
+                "id", RelevantUpload),
+            ]
 
-        last_uploads = SourcePackageRelease.select(
-            query, limit=5, prejoins=['sourcepackagename'],
-            clauseTables=['SourcePackageName', 'PackageUpload',
-                          'PackageUploadSource'],
-            orderBy=['-packageupload.id'])
+        last_uploads = DecoratedResultSet(
+            IStore(SourcePackageRelease).with_(relevant_upload_cte).find(
+                (SourcePackageRelease, SourcePackageName),
+                *clauses).order_by(Desc(Column("id", RelevantUpload)))[:5],
+            result_decorator=itemgetter(0))
 
         distro_sprs = [
             self.distribution.getSourcePackageRelease(spr)