launchpad-dev team mailing list archive

[Tim Penhey <tim.penhey@xxxxxxxxxxxxx>]

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