launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/getBranchTips-optimise into lp:launchpad.

Commit message:
Tweak the Distribution.getBranchTips query to get a good plan over the main target, the charms distro.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/getBranchTips-optimise/+merge/243942

Tweak the Distribution.getBranchTips query to get a good plan over the main target, the charms distro.

https://pastebin.canonical.com/121870/ has plans before and after.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/getBranchTips-optimise into lp:launchpad.

=== modified file 'lib/lp/registry/model/distribution.py'
--- lib/lp/registry/model/distribution.py	2014-07-30 15:42:05 +0000
+++ lib/lp/registry/model/distribution.py	2014-12-08 04:27:45 +0000
@@ -616,8 +616,16 @@
         from lp.code.model.seriessourcepackagebranch import (
             SeriesSourcePackageBranch)
 
+        # This method returns thousands of branch unique names in a
+        # single call, so the query is perilous and awkwardly tuned to
+        # get a good plan with the normal dataset (the charms distro).
+        OfficialSeries = ClassAlias(DistroSeries)
+
+        ds_ids = Select(
+            DistroSeries.id, tables=[DistroSeries],
+            where=DistroSeries.distributionID == self.id)
         clauses = [
-            DistroSeries.distribution == self.id,
+            DistroSeries.id.is_in(ds_ids),
             get_branch_privacy_filter(user),
         ]
 
@@ -625,17 +633,18 @@
             # If "since" was provided, take into account.
             clauses.append(Branch.last_scanned > since)
 
-        OfficialSeries = ClassAlias(DistroSeries)
         branches = IStore(self).using(
             Branch,
             Join(DistroSeries,
                  DistroSeries.id == Branch.distroseriesID),
             LeftJoin(
-                SeriesSourcePackageBranch,
-                SeriesSourcePackageBranch.branchID == Branch.id),
-            LeftJoin(
-                OfficialSeries,
-                OfficialSeries.id == SeriesSourcePackageBranch.distroseriesID),
+                Join(
+                    SeriesSourcePackageBranch,
+                    OfficialSeries,
+                    OfficialSeries.id ==
+                        SeriesSourcePackageBranch.distroseriesID),
+                And(SeriesSourcePackageBranch.branchID == Branch.id,
+                    SeriesSourcePackageBranch.distroseriesID.is_in(ds_ids))),
         ).find(
             (Branch.unique_name, Branch.last_scanned_id, OfficialSeries.name),
             And(*clauses)