Person:+branches timeout: sometimes-slow bug-branch link query

                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate (cost=196060.19..196060.58 rows=39 width=4) (actual time=5332.197..5332.204 rows=18 loops=1)
   -> Hash Join (cost=184038.09..196059.95 rows=97 width=4) (actual time=5163.797..5332.140 rows=18 loops=1)
         Hash Cond: (bugbranch.bug = bug.id)
         -> Seq Scan on bugbranch (cost=0.00..9341.04 rows=97 width=8) (actual time=1.622..99.300 rows=18 loops=1)
               Filter: (branch = ANY ('{451217,451208,451207,451206,310641,450226,449792,449762,449192,449190,444685,439423,436128,421710,317004,374978,404904,412011,411115,245099,400223,377965,373979,372939,372691,362490,362566,362564,361252,359795,359423,357633,355943,348553,322307,319438,316198,316197,315929,310090,308190,305716,305524,296541,294443,245102,275463,273980,40682,34254,175815,152799,115607,81913,81670,62623,52209,42949,39318,38448,37654,12332,30797,21766,13518,16918,12486,10605,10457,5670,9447,7882,4233,3674,2510,2469}'::integer[]))
         -> Hash (cost=172795.96..172795.96 rows=685211 width=4) (actual time=5135.243..5135.243 rows=683467 loops=1)
               -> Seq Scan on bug (cost=2964.63..172795.96 rows=685211 width=4) (actual time=0.009..4752.782 rows=683467 loops=1)
                     Filter: ((NOT private) OR (hashed SubPlan 1))
                     SubPlan 1
                       -> Nested Loop (cost=0.00..2954.48 rows=4062 width=4) (actual time=0.074..1576.219 rows=130835 loops=1)
                             -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..273.54 rows=106 width=4) (actual time=0.031..210.154 rows=98 loops=1)
                                   Index Cond: (person = 67034)
                             -> Index Scan using bugsubscription_person_idx on bugsubscription (cost=0.00..25.17 rows=10 width=8) (actual time=3.594..13.150 rows=1335 loops=98)
                                   Index Cond: (bugsubscription.person = teamparticipation.team)
 Total runtime: 5333.678 ms

So privacy is killing us - we're triggering a sequential scan on bug!

 explain analyze with bugbranches as (select bugbranch.bug, bugbranch.branch from bugbranch where branch in (451217, 451208, 451207, 451206, 310641, 450226, 449792, 449762, 449192, 449190, 444685, 439423, 436128, 421710, 317004, 374978, 404904, 412011, 411115, 245099, 400223, 377965, 373979, 372939, 372691, 362490, 362566, 362564, 361252, 359795, 359423, 357633, 355943, 348553,
322307, 319438, 316198, 316197, 315929, 310090, 308190, 305716, 305524, 296541, 294443, 245102, 275463, 273980, 40682, 34254, 175815, 152799, 115607, 81913, 81670, 62623, 52209, 42949, 39318, 38448, 37654, 12332, 30797, 21766, 13518, 16918, 12486, 10605, 10457, 5670, 9447, 7882, 4233, 3674, 2510, 2469))
(SELECT BugBranches.branch
FROM Bug, BugBranches
WHERE Bug.id = BugBranches.bug
  AND Bug.private = FALSE)
UNION
(SELECT BugBranches.branch
FROM Bug, BugBranches
WHERE Bug.id = BugBranches.bug
  AND Bug.private AND Bug.id IN
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.team = BugSubscription.person
            AND TeamParticipation.person = 67034));
                                                                                                                                                                                                                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate (cost=13604.15..13606.09 rows=194 width=4) (actual time=450.964..450.970 rows=18 loops=1)
   CTE bugbranches
     -> Seq Scan on bugbranch (cost=0.00..9341.04 rows=97 width=8) (actual time=1.584..160.240 rows=18 loops=1)
           Filter: (branch = ANY ('{451217,451208,451207,451206,310641,450226,449792,449762,449192,449190,444685,439423,436128,421710,317004,374978,404904,412011,411115,245099,400223,377965,373979,372939,372691,362490,362566,362564,361252,359795,359423,357633,355943,348553,322307,319438,316198,316197,315929,310090,308190,305716,305524,296541,294443,245102,275463,273980,40682,34254,175815,152799,115607,81913,81670,62623,52209,42949,39318,38448,37654,12332,30797,21766,13518,16918,12486,10605,10457,5670,9447,7882,4233,3674,2510,2469}'::integer[]))
   -> Append (cost=0.00..4262.63 rows=194 width=4) (actual time=1.615..450.934 rows=18 loops=1)
         -> Nested Loop (cost=0.00..619.49 rows=97 width=4) (actual time=1....

better still:

 explain analyze SELECT DISTINCT BugBranch.branch
FROM Bug,
     BugBranch
WHERE BugBranch.branch IN (451217, 451208, 451207, 451206, 310641, 450226, 449792, 449762, 449192, 449190, 444685, 439423, 436128, 421710, 317004, 374978, 404904, 412011, 411115, 245099, 400223, 377965, 373979, 372939, 372691, 362490, 362566, 362564, 361252, 359795, 359423, 357633, 355943, 348553,
322307, 319438, 316198, 316197, 315929, 310090, 308190, 305716, 305524, 296541, 294443, 245102, 275463, 273980, 40682, 34254, 175815, 152799, 115607, 81913, 81670, 62623, 52209, 42949, 39318, 38448, 37654, 12332, 30797, 21766, 13518, 16918, 12486, 10605, 10457, 5670, 9447, 7882, 4233, 3674, 2510, 2469)
  AND Bug.id = BugBranch.bug
  AND (Bug.private = FALSE
       OR exists
         (SELECT TRUE
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.team = BugSubscription.person
            AND TeamParticipation.person = 67034 and bug.id=BugSubscription.bug));
                                                                                                                                                                                                                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate (cost=12527.12..12527.51 rows=39 width=4) (actual time=186.053..186.064 rows=18 loops=1)
   -> Nested Loop (cost=0.00..12526.88 rows=97 width=4) (actual time=1.613..186.019 rows=18 loops=1)
         -> Seq Scan on bugbranch (cost=0.00..9341.04 rows=97 width=8) (actual time=1.585..185.567 rows=18 loops=1)
               Filter: (branch = ANY ('{451217,451208,451207,451206,310641,450226,449792,449762,449192,449190,444685,439423,436128,421710,317004,374978,404904,412011,411115,245099,400223,377965,373979,372939,372691,362490,362566,362564,361252,359795,359423,357633,355943,348553,322307,319438,316198,316197,315929,310090,308190,305716,305524,296541,294443,245102,275463,273980,40682,34254,175815,152799,115607,81913,81670,62623,52209,42949,39318,38448,37654,12332,30797,21766,13518,16918,12486,10605,10457,5670,9447,7882,4233,3674,2510,2469}'::integer[]))
         -> Index Scan using bug_pkey on bug (cost=0.00..32.83 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=18)
               Index Cond: (bug.id = bugbranch.bug)
               Filter: ((NO...

Fixed in stable r12975 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/12975>.

This bug is the second incarnation of the timeout. As noted in bug 711077, getBranchesWithVisibleBugs() is still slow. And if you see bug 827935, that page is still timing out.