Merge lp:~lifeless/launchpad/bug-722794 into lp:launchpad

[Robert Collins]

Use a tuned query plan for +needs-packaging

Hopefully solve a top-12 timeout using a query put together by stub. Does not alter the sister page +packaging which isn't currently timing out (perhaps due to unuse, or different stats leading to a different plan). I hope I've made why clear in the comments.

[Steve Kowalik (stevenk)]

review: Approve

=== modified file 'lib/lp/registry/model/distroseries.py'
--- lib/lp/registry/model/distroseries.py	2011-03-22 04:40:12 +0000
+++ lib/lp/registry/model/distroseries.py	2011-03-23 04:46:32 +0000
@@ -474,21 +474,64 @@
         translatable messages, and the source package release's component.
         """
         find_spec = (
+            SQL("DISTINCT ON (score, sourcepackagename.name) TRUE as _ignored"),
             SourcePackageName,
             SQL("""
                 coalesce(total_bug_heat, 0) + coalesce(po_messages, 0) +
-                CASE WHEN spr.component = 1 THEN 1000 ELSE 0 END AS score"""),
+                CASE WHEN component = 1 THEN 1000 ELSE 0 END AS score"""),
             SQL("coalesce(bug_count, 0) AS bug_count"),
             SQL("coalesce(total_messages, 0) AS total_messages"))
-        joins, conditions = self._current_sourcepackage_joins_and_conditions
-        origin = SQL(joins)
-        condition = SQL(conditions + "AND packaging.id IS NULL")
+        # This does not use _current_sourcepackage_joins_and_conditions because
+        # the two queries are working on different data sets - +needs-packaging
+        # was timing out and +packaging wasn't, and destabilising things
+        # unnecessarily is not good.
+        origin = SQL("""
+            SourcePackageName, (SELECT
+        spr.sourcepackagename,
+        spr.component,
+        bug_count,
+        total_bug_heat,
+        SUM(POTemplate.messagecount) * %(po_message_weight)s AS po_messages,
+        SUM(POTemplate.messagecount) AS total_messages
+    FROM
+        SourcePackageRelease AS spr
+        JOIN SourcePackagePublishingHistory AS spph
+            ON spr.id = spph.sourcepackagerelease
+        JOIN Archive
+            ON spph.archive = Archive.id
+        JOIN Section
+            ON spph.section = Section.id
+        JOIN DistroSeries
+            ON spph.distroseries = DistroSeries.id
+        LEFT OUTER JOIN DistributionSourcePackage AS dsp
+            ON dsp.sourcepackagename = spr.sourcepackagename
+                AND dsp.distribution = DistroSeries.distribution
+        LEFT OUTER JOIN POTemplate
+            ON POTemplate.sourcepackagename = spr.sourcepackagename
+                AND POTemplate.distroseries = DistroSeries.id
+    WHERE
+        DistroSeries.id = %(distroseries)s
+        AND spph.status IN %(active_status)s
+        AND Archive.purpose = %(primary)s
+        AND Section.name <> 'translations'
+        AND NOT EXISTS (
+            SELECT TRUE FROM Packaging
+            WHERE
+                Packaging.sourcepackagename = spr.sourcepackagename
+                AND Packaging.distroseries = spph.distroseries)
+    GROUP BY
+        spr.sourcepackagename, spr.component, bug_count, total_bug_heat
+    ) AS spn_info""" % sqlvalues(
+            po_message_weight=self._current_sourcepackage_po_weight,
+            distroseries=self,
+            active_status=active_publishing_status,
+            primary=ArchivePurpose.PRIMARY))
+        condition = SQL("""sourcepackagename.id = spn_info.sourcepackagename""")
         results = IStore(self).using(origin).find(find_spec, condition)
         results = results.order_by('score DESC', SourcePackageName.name)
-        results = results.config(distinct=True)
 
-        def decorator(result):
-            spn, score, bug_count, total_messages = result
+        def decorator(row):
+            _, spn, score, bug_count, total_messages = row
             return {
                 'package': SourcePackage(
                     sourcepackagename=spn, distroseries=self),
@@ -538,12 +581,23 @@
                 ''')
 
     @property
+    def _current_sourcepackage_po_weight(self):
+        """See getPrioritized*.""" 
+        # Bugs and PO messages are heuristically scored. These queries
+        # can easily timeout so filters and weights are used to create
+        # an acceptable prioritization of packages that is fast to excecute.
+        return .5
+
+    @property
     def _current_sourcepackage_joins_and_conditions(self):
-        """The SQL joins and conditions to prioritize source packages."""
+        """The SQL joins and conditions to prioritize source packages.
+        
+        Used for getPrioritizedPackagings only.
+        """
         # Bugs and PO messages are heuristically scored. These queries
         # can easily timeout so filters and weights are used to create
         # an acceptable prioritization of packages that is fast to excecute.
-        po_message_weight = .5
+        po_message_weight = self._current_sourcepackage_po_weight
         message_score = ("""
             LEFT JOIN (
                 SELECT