Comment 2 for bug 618406

https://bugs.launchpad.net/ubuntu is down to 7.21 seconds first hit, then 8, 9, 12, 8. The 12 is GIL contention on the server I think, given its capable of rendering much more quickly. The page id is still in my timeout-candidate PPR report - https://devpad.canonical.com/~lpqateam/ppr/lpnet/latest-daily-timeout-candidates.html - but I fear we'll need to wait and capture a fresher oops still since we've improved bug search substantially over the last week.

The bug heat search query is slowwww though:
Time: 52081.905 ms

explain analyze SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache, Bug.date_last_message, Bug.date_last_updated, Bug.date_made_private, Bug.datecreated, Bug.description, Bug.duplicateof, Bug.heat, Bug.heat_last_updated, Bug.id, Bug.latest_patch_uploaded, Bug.message_count, Bug.name, Bug.number_of_duplicates, Bug.owner, Bug.private, Bug.security_related, Bug.title, Bug.users_affected_count, Bug.users_unaffected_count, Bug.who_made_private FROM BugTask JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug AND BugTask.distribution = 1 AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id )) ORDER BY Bug.heat DESC, BugTask.id LIMIT 40 OFFSET 0;
                                                                                             QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=6957590.92..6957591.02 rows=40 width=1017) (actual time=4311.330..4311.343 rows=40 loops=1)
   -> Sort (cost=6957590.92..6958004.71 rows=165515 width=1017) (actual time=4311.328..4311.335 rows=40 loops=1)
         Sort Key: bug.heat, public.bugtask.id
         Sort Method: top-N heapsort Memory: 92kB
         -> Nested Loop (cost=0.00..6952359.05 rows=165515 width=1017) (actual time=0.076..4067.521 rows=88655 loops=1)
               -> Seq Scan on bugtask (cost=0.00..54723.29 rows=165515 width=276) (actual time=0.046..847.575 rows=190392 loops=1)
                     Filter: ((distribution = 1) AND ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25)))
               -> Index Scan using bug_pkey on bug (cost=0.00..41.66 rows=1 width=741) (actual time=0.016..0.016 rows=0 loops=190392)
                     Index Cond: (bug.id = public.bugtask.bug)
                     Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 1)))
                     SubPlan 1
                       -> Unique (cost=39.25..39.26 rows=2 width=4) (actual time=0.061..0.061 rows=1 loops=7934)
                             -> Sort (cost=39.25..39.25 rows=2 width=4) (actual time=0.060..0.060 rows=1 loops=7934)
                                   Sort Key: bugsubscription.bug
                                   Sort Method: quicksort Memory: 25kB
                                   -> Append (cost=0.00..39.24 rows=2 width=4) (actual time=0.038..0.055 rows=1 loops=7934)
                                         -> Nested Loop (cost=0.00..26.48 rows=1 width=4) (actual time=0.036..0.043 rows=1 loops=7934)
                                               -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (actual time=0.014..0.020 rows=3 loops=7934)
                                                     Index Cond: (bug = $0)
                                               -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=27736)
                                                     Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                         -> Nested Loop (cost=0.00..12.74 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=7934)
                                               -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.34 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=7934)
                                                     Index Cond: (bug = $0)
                                               -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=8233)
                                                     Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
 Total runtime: 4311.861 ms