launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging ~wgrant/launchpad:optimise-gina into launchpad:master.

Commit message:
Optimise gina source and binary DB lookups

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/+git/launchpad/+merge/377080

The relatively modern SourcePackagePublishingHistory.sourcepackagename
and BinaryPackagePublishingHistory.binarypackagename denormalised fields
can improve gina's plans by orders of magnitude in some cases.

This also fixes gina's binary handlers (not used on production since
2006) to correctly deal with multiple archives per distribution (it's
been incorrect since 2007's ArchiveRework).
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~wgrant/launchpad:optimise-gina into launchpad:master.

diff --git a/database/schema/patch-2210-01-3.sql b/database/schema/patch-2210-01-3.sql
new file mode 100644
index 0000000..483ad39
--- /dev/null
+++ b/database/schema/patch-2210-01-3.sql
@@ -0,0 +1,7 @@
+-- Copyright 2019 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+CREATE INDEX binarypackagepublishinghistory__archive__bpr__idx
+    ON binarypackagepublishinghistory (archive, binarypackagerelease);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 01, 3);
diff --git a/lib/lp/soyuz/scripts/gina/handlers.py b/lib/lp/soyuz/scripts/gina/handlers.py
index 7d167d5..66d2aeb 100644
--- a/lib/lp/soyuz/scripts/gina/handlers.py
+++ b/lib/lp/soyuz/scripts/gina/handlers.py
@@ -560,9 +560,11 @@ class SourcePackageHandler:
                 SourcePackagePublishingHistory.distroseries =
                     DistroSeries.id AND
                 SourcePackagePublishingHistory.archive = %s AND
+                SourcePackagePublishingHistory.sourcepackagename = %s AND
                 DistroSeries.distribution = %s
                 """ % sqlvalues(sourcepackagename, version,
                                 distroseries.main_archive,
+                                sourcepackagename,
                                 distroseries.distribution)
         ret = SourcePackageRelease.select(query,
             clauseTables=['SourcePackagePublishingHistory', 'DistroSeries'],
@@ -740,7 +742,8 @@ class BinaryPackageHandler:
         architecture = binarypackagedata.architecture
 
         clauseTables = ["BinaryPackageRelease", "DistroSeries",
-                        "BinaryPackageBuild", "DistroArchSeries"]
+                        "DistroArchSeries", "BinaryPackageBuild",
+                        "BinaryPackagePublishingHistory"]
         distroseries = distroarchseries.distroseries
 
         # When looking for binaries, we need to remember that they are
@@ -748,13 +751,18 @@ class BinaryPackageHandler:
         # distribution and the architecture tag of the distroarchseries
         # they were built for
         query = (
+            "BinaryPackagePublishingHistory.archive = %s AND "
+            "BinaryPackagePublishingHistory.binarypackagename = %s AND "
+            "BinaryPackageRelease.id ="
+            " BinaryPackagePublishingHistory.binarypackagerelease AND "
             "BinaryPackageRelease.binarypackagename=%s AND "
             "BinaryPackageRelease.version=%s AND "
             "BinaryPackageRelease.build = BinaryPackageBuild.id AND "
             "BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND "
             "DistroArchSeries.distroseries = DistroSeries.id AND "
-            "DistroSeries.distribution = %d" %
-            (binaryname.id, quote(version), distroseries.distribution.id))
+            "DistroSeries.distribution = %s" %
+            sqlvalues(distroseries.main_archive, binaryname, binaryname,
+                      version, distroseries.distribution))
 
         if architecture != "all":
             query += ("AND DistroArchSeries.architecturetag = %s" %
@@ -762,7 +770,7 @@ class BinaryPackageHandler:
 
         try:
             bpr = BinaryPackageRelease.selectOne(
-                query, clauseTables=clauseTables)
+                query, clauseTables=clauseTables, distinct=True)
         except SQLObjectMoreThanOneResultError:
             # XXX kiko 2005-10-27: Untested
             raise MultiplePackageReleaseError("Found more than one "
@@ -845,9 +853,10 @@ class BinaryPackageHandler:
         query = ("BinaryPackageBuild.source_package_release = %d AND "
                  "BinaryPackageBuild.distro_arch_series = "
                  "    DistroArchSeries.id AND "
+                 "BinaryPackageBuild.archive = %d AND "
                  "DistroArchSeries.distroseries = DistroSeries.id AND "
                  "DistroSeries.distribution = %d"
-                 % (srcpkg.id, distribution.id))
+                 % (srcpkg.id, distribution.main_archive.id, distribution.id))
 
         if archtag != "all":
             query += ("AND DistroArchSeries.architecturetag = %s"