launchpad-dev team mailing list archive

[Tim Penhey <tim.penhey@xxxxxxxxxxxxx>]

Hi Stuart (and Jeroen),

With the recent explosion of source package branches, some of our queries are 
now taking a lot longer.  Combined with the current inefficiencies in the menu 
rendering on production and edge, we are seeing a lot of timeouts for branch 
listings.  These are primarily caused by the counting of pending reviews and 
approved merges.  A query like:

SELECT COUNT(*) FROM 
BranchMergeProposal 
JOIN CodeReviewVote 
  ON CodeReviewVote.branch_merge_proposal = BranchMergeProposal.id 
LEFT JOIN CodeReviewMessage 
  ON CodeReviewVote.vote_message = CodeReviewMessage.id 
WHERE 
  CodeReviewVote.reviewer = %s 
AND BranchMergeProposal.source_branch IN (
    SELECT Branch.id 
    FROM Branch 
    WHERE Branch.id IN (
        (SELECT Branch.id FROM Branch WHERE Branch.private = %s)
        UNION 
        (SELECT Branch.id FROM Branch, TeamParticipation 
        WHERE Branch.owner = TeamParticipation.team 
        AND TeamParticipation.person = %s) 
        UNION
       (SELECT Branch.id FROM Branch, BranchSubscription, TeamParticipation
         WHERE BranchSubscription.branch = Branch.id 
         AND BranchSubscription.person = TeamParticipation.team 
         AND TeamParticipation.person = %s AND Branch.private = %s))) 
AND BranchMergeProposal.target_branch IN (
    SELECT Branch.id 
    FROM Branch 
    WHERE Branch.id IN (
        (SELECT Branch.id FROM Branch WHERE Branch.private = %s)
        UNION 
        (SELECT Branch.id FROM Branch, TeamParticipation 
        WHERE Branch.owner = TeamParticipation.team 
        AND TeamParticipation.person = %s) 
        UNION
       (SELECT Branch.id FROM Branch, BranchSubscription, TeamParticipation
         WHERE BranchSubscription.branch = Branch.id 
         AND BranchSubscription.person = TeamParticipation.team 
         AND TeamParticipation.person = %s AND Branch.private = %s))) 
AND BranchMergeProposal.queue_status IN (%s, %s)

Actually this count is for reviews that the person has done.

The clause:

Branch.id IN (
        (SELECT Branch.id FROM Branch WHERE Branch.private = %s)
        UNION 
        (SELECT Branch.id FROM Branch, TeamParticipation 
        WHERE Branch.owner = TeamParticipation.team 
        AND TeamParticipation.person = %s) 
        UNION
       (SELECT Branch.id FROM Branch, BranchSubscription, TeamParticipation
         WHERE BranchSubscription.branch = Branch.id 
         AND BranchSubscription.person = TeamParticipation.team 
         AND TeamParticipation.person = %s AND Branch.private = %s))) 

is used a lot in the branch queries to determine if the user can see the 
branch.  The three parts to the union are:
  * the branch is public
  * the branch is owned by the user or a team the user is in
  * the user is subscribed to the branch directly or through a team

Can you help me find out why this query is slow and what we can do to speed it 
up?  There are other queries like this but slightly different - please ask if 
you need more information.

Thanks
Tim

Attachment: signature.asc
Description: This is a digitally signed message part.