Comment 11 for bug 892820

rephrasing the supervisor check brings it down to 4 seconds:
SELECT BugTask.status, 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.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, 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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND (BugTask.product in (select product.id from product where bug_supervisor = 343381) OR
(BugTask.distribution, Bugtask.sourcepackagename) in (SELECT StructuralSubscription.distribution, StructuralSubscription.sourcepackagename from StructuralSubscription where subscriber=343381) OR
BugTask.distribution in (SELECT id from Distribution where bug_supervisor=343381))
                                                                                                                                                                                                                                                AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0

 Limit (cost=194.09..20947.97 rows=76 width=921) (actual time=3913.046..3920.141 rows=4 loops=1)
   -> Nested Loop Anti Join (cost=194.09..13321456.48 rows=48782 width=921) (actual time=3913.044..3920.137 rows=4 loops=1)
         -> Nested Loop (cost=194.09..13302301.74 rows=48782 width=921) (actual time=3912.964..3919.241 rows=4 loops=1)
               -> Index Scan using bugtask__datecreated__idx on bugtask (cost=194.09..110609.89 rows=64685 width=161) (actual time=355.727..3882.752 rows=1192 loops=1)
                     Filter: ((importance = 5) AND (distribution = 1) AND (status = 10) AND ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3)))
                     SubPlan 1
                       -> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.089..0.089 rows=0 loops=1)
                             Index Cond: (bug_supervisor = 343381)
                     SubPlan 2
                       -> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.197..3.329 rows=187 loops=1)
                             Recheck Cond: (subscriber = 343381)
                             -> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.152..0.152 rows=188 loops=1)
                                   Index Cond: (subscriber = 343381)
                     SubPlan 3
                       -> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.069..0.069 rows=0 loops=1)
                             Index Cond: (bug_supervisor = 343381)
               -> Index Scan using bug_pkey on bug (cost=0.00..203.92 rows=1 width=760) (actual time=0.029..0.029 rows=0 loops=1192)
                     Index Cond: (bug.id = public.bugtask.bug)
                     Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 5)))
                     SubPlan 5
                       -> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=1.937..1.937 rows=0 loops=3)
                             CTE teams
                               -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.095..4.739 rows=157 loops=1)
                                     Index Cond: (person = 1042386)
                             -> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=1.932..1.932 rows=0 loops=3)
                                   Sort Key: bugsubscription.bug
                                   Sort Method: quicksort Memory: 25kB
                                   -> Append (cost=4.45..17.26 rows=2 width=4) (actual time=1.858..1.858 rows=0 loops=3)
                                         -> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=1.754..1.754 rows=0 loops=3)
                                               Hash Cond: (bugsubscription.person = teams.team)
                                               -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.091..0.103 rows=2 loops=3)
                                                     Index Cond: (bug = $5)
                                               -> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=4.908..4.908 rows=157 loops=1)
                                                     -> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.099..4.848 rows=157 loops=1)
                                         -> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.098..0.098 rows=0 loops=3)
                                               Join Filter: (public.bugtask.assignee = teams.team)
                                               -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.036..0.037 rows=1 loops=3)
                                                     Index Cond: (bug = $5)
                                               -> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.002..0.033 rows=157 loops=3)
         -> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.222..0.222 rows=0 loops=4)
               Index Cond: (bugtag.bug = bug.id)
               Filter: (bugtag.tag = 'notserv'::text)
 Total runtime: 3920.679 ms