launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/bug-932451 into lp:launchpad.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #932451 in Launchpad itself: "PillarNameSet.getByName is hilariously slow"
  https://bugs.launchpad.net/launchpad/+bug/932451

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/bug-932451/+merge/93111

PillarNameSet.getByName, as used by every traversal to a product or distribution, is rather slow. This branch alters the query slightly to get it fully indexed. I think the following plans say it all.

OLD
===

 Seq Scan on pillarname  (cost=8.27..718.88 rows=16542 width=16) (actual time=6.765..14.168 rows=1 loops=1)
   Filter: ((alias_for IS NULL) AND ((hashed SubPlan 1) OR (name = 'unity'::text)))
   SubPlan 1
     ->  Index Scan using pillarname_name_key on pillarname  (cost=0.00..8.27 rows=1 width=4) (actual time=0.026..0.029 rows=1 loops=1)
           Index Cond: (name = 'unity'::text)
 Total runtime: 14.243 ms
(6 rows)


NEW
===

 Limit  (cost=16.80..20.52 rows=1 width=16) (actual time=0.060..0.061 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using pillarname_name_key on pillarname  (cost=0.00..8.27 rows=1 width=4) (actual time=0.032..0.035 rows=1 loops=1)
           Index Cond: (name = 'unity'::text)
   ->  Bitmap Heap Scan on pillarname  (cost=8.52..15.97 rows=2 width=16) (actual time=0.058..0.058 rows=1 loops=1)
         Recheck Cond: ((id = $0) OR (name = 'unity'::text))
         Filter: (alias_for IS NULL)
         ->  BitmapOr  (cost=8.52..8.52 rows=2 width=0) (actual time=0.052..0.052 rows=0 loops=1)
               ->  Bitmap Index Scan on pillarname_pkey  (cost=0.00..4.26 rows=1 width=0) (actual time=0.041..0.041 rows=0 loops=1)
                     Index Cond: (id = $0)
               ->  Bitmap Index Scan on pillarname_name_key  (cost=0.00..4.26 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)
                     Index Cond: (name = 'unity'::text)
 Total runtime: 0.122 ms
(13 rows)
-- 
https://code.launchpad.net/~wgrant/launchpad/bug-932451/+merge/93111
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/bug-932451 into lp:launchpad.

=== modified file 'lib/lp/registry/model/pillar.py'
--- lib/lp/registry/model/pillar.py	2011-12-30 06:14:56 +0000
+++ lib/lp/registry/model/pillar.py	2012-02-15 00:39:17 +0000
@@ -116,19 +116,20 @@
         # the Project, Product and Distribution tables (and this approach
         # works better with SQLObject too.
 
-
         # Retrieve information out of the PillarName table.
         store = getUtility(IStoreSelector).get(MAIN_STORE, DEFAULT_FLAVOR)
-        cur = cursor()
         query = """
             SELECT id, product, project, distribution
             FROM PillarName
-            WHERE (id IN (SELECT alias_for FROM PillarName WHERE name=?)
+            WHERE (id = (SELECT alias_for FROM PillarName WHERE name=?)
                    OR name=?)
-                AND alias_for IS NULL
+                AND alias_for IS NULL%s
+            LIMIT 1
             """
         if ignore_inactive:
-            query += " AND active IS TRUE"
+            query %= " AND active IS TRUE"
+        else:
+            query %= ""
         name = ensure_unicode(name)
         result = store.execute(query, [name, name])
         row = result.get_one()