It seems moving Branch.private = False out of the union gives a 10x speed improvement.
Submitting that fix. On Thu, 13 Aug 2009 17:12:41 Tim Penhey wrote: > 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 _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
