Product:+code-index timeouts

SELECT COUNT(*)
FROM BranchMergeProposal join branch as source on source.id=branchmergeproposal.source_branch and source.product=7432 join branch as target on target.id=branchmergeproposal.target_branch
WHERE source.private = false or source.id in(
                               (SELECT Branch.id
                                FROM Branch,
                                     TeamParticipation
                                WHERE Branch.OWNER = TeamParticipation.team
                                  AND TeamParticipation.person = 790463
                                  AND Branch.private = TRUE)
                             UNION
                               (SELECT Branch.id
                                FROM Branch,
                                     BranchSubscription,
                                     TeamParticipation
                                WHERE BranchSubscription.branch = Branch.id
                                  AND BranchSubscription.person = TeamParticipation.team
                                  AND TeamParticipation.person = 790463
                                  AND Branch.private = TRUE))
  AND target.private=false or target.id IN(
                          (SELECT Branch.id
                           FROM Branch,
                                TeamParticipation
                           WHERE Branch.OWNER = TeamParticipation.team
                             AND TeamParticipation.person = 790463
                             AND Branch.private = TRUE)
                        UNION
                          (SELECT Branch.id
                           FROM Branch,
                                BranchSubscription,
                                TeamParticipation
                           WHERE BranchSubscription.branch = Branch.id
                             AND BranchSubscription.person = TeamParticipation.team
                             AND TeamParticipation.person = 790463
                             AND Branch.private = TRUE))
  AND BranchMergeProposal.queue_status IN (3,
                                           2)

looks like a significant improvement on the branch merge proposal queries - 39 and 40 in OOPS-1906G1635 - which btw are identical, so another source of potential improvement.

The big thing to fix is the validity lookups : eager load the relevant people, will save 4 seconds or so straight up. That combined with the better query for bmps should save 50% of the render time.

                                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=18024.62..18024.63 rows=1 width=0) (actual time=26.271..26.272 rows=1 loops=1)
   -> Nested Loop (cost=15086.86..18023.65 rows=388 width=0) (actual time=1.142..26.158 rows=168 loops=1)
         Join Filter: ((NOT source.private) OR ((hashed SubPlan 1) AND (NOT target.private)) OR ((hashed SubPlan 2) AND (branchmergeproposal.queue_status = ANY ('{3,2}'::integer[]))))
         -> Nested Loop (cost=6.44..1479.19 rows=397 width=13) (actual time=0.982..22.225 rows=168 loops=1)
               -> Bitmap Heap Scan on branch source (cost=6.44..561.77 rows=329 width=5) (actual time=0.658..8.537 rows=329 loops=1)
                     Recheck Cond: (product = 7432)
                     -> Bitmap Index Scan on branch__product__id__idx (cost=0.00..6.35 rows=329 width=0) (actual time=0.489..0.489 rows=329 loops=1)
                           Index Cond: (product = 7432)
               -> Index Scan using branchmergeproposal__source_branch__idx on branchmergeproposal (cost=0.00..2.78 rows=1 width=12) (actual time=0.038..0.039 rows=1 loops=329)
                     Index Cond: (branchmergeproposal.source_branch = source.id)
         -> Index Scan using branch_pkey on branch target (cost=0.00..3.67 rows=1 width=5) (actual time=0.020..0.021 rows=1 loops=168)
               Index Cond: (target.id = branchmergeproposal.target_branch)
         SubPlan 1
           -> HashAggregate (cost=7539.86..7540.14 rows=28 width=4) (never executed)
                 -> Append (cost=0.00..7539.79 rows=28 width=4) (never executed)
                       -> Nested Loop (cost=0.00..3694.03 rows=18 width=4) (never executed)
                             -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..191.83 rows=125 width=4) (never executed)
                                   Index Cond: (person = 790463)
                             -> Index Scan using branch_owner_idx on branch (cost=0.00..28.01 rows=1 width=8) (never executed)
                                   Index Cond: (public.branch.owner = public.teamparticipation.team)
                                   Filter: public.branch.private
                       -> Nested Loop (cost=0.00..3845.48 rows=10 width=4) (never executed)
                             -> Nested Loop (cost=0.00..3760.60 rows=217 width=4) (never executed)
                                   -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..191.83 rows=125 width=4) (never executed)
                                         Index Cond: (person = 790463)
                                   -> Index Scan using branchsubscription__person__branch__key on branchsubscription (cost=0.00..28.26 rows=23 width=8) (never executed)
                                         Index Cond: (public.branchsubsc...

(about 30 times faster; a nice incremental win)

 BranchCollections make this excruciating - we need an alias on an expression they have already serialised.

SELECT COUNT(*)
FROM BranchMergeProposal join branch on branch.id=branchmergeproposal.source_branch and branch.product=7432 join branch as target on target.id=branchmergeproposal.target_branch
WHERE branch.private = false or branch.id in(
                               (SELECT Branch.id
                                FROM Branch,
                                     TeamParticipation
                                WHERE Branch.OWNER = TeamParticipation.team
                                  AND TeamParticipation.person = 790463
                                  AND Branch.private = TRUE)
                             UNION
                               (SELECT Branch.id
                                FROM Branch,
                                     BranchSubscription,
                                     TeamParticipation
                                WHERE BranchSubscription.branch = Branch.id
                                  AND BranchSubscription.person = TeamParticipation.team
                                  AND TeamParticipation.person = 790463
                                  AND Branch.private = TRUE))
  AND target.private=false or target.id IN(
                          (SELECT Branch.id
                           FROM Branch,
                                TeamParticipation
                           WHERE Branch.OWNER = TeamParticipation.team
                             AND TeamParticipation.person = 790463
                             AND Branch.private = TRUE)
                        UNION
                          (SELECT Branch.id
                           FROM Branch,
                                BranchSubscription,
                                TeamParticipation
                           WHERE BranchSubscription.branch = Branch.id
                             AND BranchSubscription.person = TeamParticipation.team
                             AND TeamParticipation.person = 790463
                             AND Branch.private = TRUE))
  AND BranchMergeProposal.queue_status IN (3, 2);

will work though.

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

bah, didn't /actually/ get the plan desired. Will have to beat storm on the head some more.

Its ok to deploy - its no worse AFAICT, but its certainly not fixed.

Possibly out of scope for this but other issues obvserved:
validpersonchecks
additional branch lookups repeat the branch visibility check (they may need to) but perhaps it would be better to rephrase the page and only show merge proposal counts for branches shown on the page; then we could determine visible branches into a temp table and do all the selects and aggregation against that.

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

Bah,.. fine detail. My candidate replacement query had this:
public branch or (subscribed/owner) and (public branch) or subscribed/owner

which is of course not the same as (public or subscribed) and (public or subscribed)

(so the landed query is slightly cheaper than we had before, but not radically cheaper)

Ok, *this* looks like it performs well - I've thrown a bunch of different projects at it with a 2 second worst case cold query.

with teams as (SELECT team from teamparticipation where person=2),
scope_branches as (SELECT Branch.id, private, owner from Branch where product=10294),
private_branches as (SELECT scope_Branches.id FROM scope_branches where
 scope_branches.private and ((scope_branches.owner in (select team from teams) OR
exists(select true from BranchSubscription, teams where branchsubscription.branch = scope_branches.id and branchsubscription.person = teams.team)))),
candidate_branches as ((select id from private_branches) union (select id from scope_branches where not private))
SELECT COUNT(*)
FROM BranchMergeProposal WHERE source_branch in (select id from candidate_branches) and target_branch in (select id from candidate_branches)
  AND BranchMergeProposal.queue_status IN (3, 2);

buuuut we can only do this for symmetric constraints: if the constraint is asymmetric - e.g. 'owned by foo', then we have to not apply that constraint only to the source branches

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

7 seconds to render on qastaging, one timeout OOPS-1914QS22 but still an improvement