Launchpad itself

Person:+branches timeout

Bug #745310 reported by Tim Cole
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
Robert Collins
Launchpad itself 11.04

Bug Description

My branch listing page (https://code.launchpad.net/~tcole, and https://code.edge.launchpad.net/~tcole) consistently times out for me. Oopses include:

OOPS-1914M1799
OOPS-1914M1810

I'm not sure if this is a regression of bug #396593, or a novel bug.

SELECT COUNT(*)
FROM ((WITH scope_branches AS
         (SELECT Branch.id, Branch.private, Branch.OWNER
          FROM Branch), teams AS
         (SELECT TeamParticipation.team
          FROM TeamParticipation
          WHERE TeamParticipation.person = 2287822), 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 BranchMergeProposal.commit_message, BranchMergeProposal.date_created, BranchMergeProposal.date_merged, BranchMergeProposal.date_queued, BranchMergeProposal.date_review_requested, BranchMergeProposal.date_reviewed, BranchMergeProposal.description, BranchMergeProposal.id, BranchMergeProposal.merge_reporter, BranchMergeProposal.merged_revno, BranchMergeProposal.dependent_branch, BranchMergeProposal.merge_diff, BranchMergeProposal.queue_position, BranchMergeProposal.queue_status, BranchMergeProposal.queued_revision_id, BranchMergeProposal.queuer, BranchMergeProposal.registrant, BranchMergeProposal.review_diff, BranchMergeProposal.reviewed_revision_id, BranchMergeProposal.reviewer, BranchMergeProposal.root_message_id, BranchMergeProposal.source_branch, BranchMergeProposal.superseded_by, BranchMergeProposal.target_branch, BranchMergeProposal.whiteboard
       FROM BranchMergeProposal, Branch
       WHERE (source_branch IN
                (SELECT id
                 FROM candidate_branches)
              AND target_branch IN
                (SELECT id
                 FROM candidate_branches))
         AND Branch.OWNER = 2287822
         AND BranchMergeProposal.source_branch = Branch.id
         AND BranchMergeProposal.queue_status IN (3, 2))
      UNION
        (SELECT BranchMergeProposal.commit_message, BranchMergeProposal.date_created, BranchMergeProposal.date_merged, BranchMergeProposal.date_queued, BranchMergeProposal.date_review_requested, BranchMergeProposal.date_reviewed, BranchMergeProposal.description, BranchMergeProposal.id, BranchMergeProposal.merge_reporter, BranchMergeProposal.merged_revno, BranchMergeProposal.dependent_branch, BranchMergeProposal.merge_diff, BranchMergeProposal.queue_position, BranchMergeProposal.queue_status, BranchMergeProposal.queued_revision_id, BranchMergeProposal.queuer, BranchMergeProposal.registrant, BranchMergeProposal.review_diff, BranchMergeProposal.reviewed_revision_id, BranchMergeProposal.reviewer, BranchMergeProposal.root_message_id, BranchMergeProposal.source_branch, BranchMergeProposal.superseded_by, BranchMergeProposal.target_branch, BranchMergeProposal.whiteboard
         FROM BranchMergeProposal
         JOIN CodeReviewVote ON CodeReviewVote.branch_merge_proposal = BranchMergeProposal.id
         LEFT JOIN CodeReviewMessage ON CodeReviewVote.vote_message = CodeReviewMessage.id
         WHERE CodeReviewVote.reviewer = 2287822
           AND BranchMergeProposal.source_branch IN
             (SELECT Branch.id
              FROM Branch
              WHERE (Branch.private = FALSE
                     OR Branch.id IN (
                                        (SELECT Branch.id
                                         FROM Branch, TeamParticipation
                                         WHERE Branch.OWNER = TeamParticipation.team
                                           AND TeamParticipation.person = 2287822
                                           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 = 2287822
                                           AND Branch.private = TRUE))))
           AND BranchMergeProposal.target_branch IN
             (SELECT Branch.id
              FROM Branch
              WHERE Branch.private = FALSE
                OR Branch.id IN (
                                   (SELECT Branch.id
                                    FROM Branch, TeamParticipation
                                    WHERE Branch.OWNER = TeamParticipation.team
                                      AND TeamParticipation.person = 2287822
                                      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 = 2287822
                                      AND Branch.private = TRUE)))
           AND BranchMergeProposal.queue_status IN (3, 2))) AS "_tmp"

                                                                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=2835782.17..2835782.18 rows=1 width=0) (actual time=9510.385..9510.385 rows=1 loops=1)
   -> HashAggregate (cost=2835767.65..2835774.10 rows=645 width=777) (actual time=9510.362..9510.367 rows=1 loops=1)
         -> Append (cost=2766212.03..2835727.34 rows=645 width=777) (actual time=8283.469..9510.318 rows=1 loops=1)
               -> Hash Join (cost=2766212.03..2766319.66 rows=4 width=777) (actual time=8283.468..8439.953 rows=1 loops=1)
                     Hash Cond: (public.branchmergeproposal.source_branch = public.branch.id)
                     CTE scope_branches
                       -> Seq Scan on branch (cost=0.00..20146.73 rows=383073 width=9) (actual time=0.062..442.494 rows=383098 loops=1)
                     CTE teams
                       -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..82.72 rows=31 width=4) (actual time=23.021..97.156 rows=24 loops=1)
                             Index Cond: (person = 2287822)
                     CTE private_branches
                       -> CTE Scan on scope_branches (cost=0.70..2712724.49 rows=143652 width=4) (actual time=1988.573..2742.450 rows=5578 loops=1)
                             Filter: (private AND ((hashed SubPlan 5) OR (alternatives: SubPlan 6 or hashed SubPlan 7)))
                             SubPlan 5
                               -> CTE Scan on teams (cost=0.00..0.62 rows=31 width=4) (actual time=23.024..97.199 rows=24 loops=1)
                             SubPlan 6
                               -> Hash Join (cost=6.31..7.06 rows=1 width=0) (never executed)
                                     Hash Cond: (teams.team = public.branchsubscription.person)
                                     -> CTE Scan on teams (cost=0.00..0.62 rows=31 width=4) (never executed)
                                     -> Hash (cost=6.30..6.30 rows=1 width=4) (never executed)
                                           -> Index Scan using branchsubscription__branch__idx on branchsubscription (cost=0.00..6.30 rows=1 width=4) (never executed)
                                                 Index Cond: (branch = $5)
                             SubPlan 7
                               -> Nested Loop (cost=0.00..1808.11 rows=708 width=4) (actual time=27.170..1881.943 rows=6022 loops=1)
                                     -> CTE Scan on teams (cost=0.00..0.62 rows=31 width=4) (actual time=0.002..0.034 rows=24 loops=1)
                                     -> Index Scan using branchsubscription__person__branch__key on branchsubscription (cost=0.00..58.02 rows=23 width=8) (actual time=3.194..78.252 rows=251 loops=24)
                                           Index Cond: (public.branchsubscription.person = teams.team)
                     CTE candidate_branches
                       -> HashAggregate (cost=14724.35..18076.23 rows=335188 width=4) (actual time=3156.691..3341.168 rows=372851 loops=1)
                             -> Append (cost=0.00..13886.38 rows=335188 width=4) (actual time=1988.578..2946.730 rows=372851 loops=1)
                                   -> CTE Scan on private_branches (cost=0.00..2873.04 rows=143652 width=4) (actual time=1988.576..2746.551 rows=5578 loops=1)
                                   -> CTE Scan on scope_branches (cost=0.00..7661.46 rows=191536 width=4) (actual time=0.035..124.441 rows=367273 loops=1)
                                         Filter: (NOT private)
                     -> Nested Loop (cost=7541.73..7648.39 rows=200 width=777) (actual time=3757.436..6017.455 rows=1821 loops=1)
                           -> HashAggregate (cost=7541.73..7543.73 rows=200 width=4) (actual time=3740.930..3994.534 rows=372851 loops=1)
                                 -> CTE Scan on candidate_branches (cost=0.00..6703.76 rows=335188 width=4) (actual time=3156.697..3541.918 rows=372851 loops=1)
                           -> Index Scan using branchmergeproposal__target_branch__idx on branchmergeproposal (cost=0.00..0.51 rows=1 width=777) (actual time=0.004..0.005 rows=0 loops=372851)
                                 Index Cond: (public.branchmergeproposal.target_branch = candidate_branches.id)
                                 Filter: (public.branchmergeproposal.queue_status = ANY ('{3,2}'::integer[]))
                     -> Hash (cost=7637.64..7637.64 rows=200 width=8) (actual time=2421.455..2421.455 rows=387 loops=1)
                           -> Nested Loop (cost=7541.73..7637.64 rows=200 width=8) (actual time=291.127..2420.649 rows=387 loops=1)
                                 -> HashAggregate (cost=7541.73..7543.73 rows=200 width=4) (actual time=274.646..402.428 rows=372851 loops=1)
                                       -> CTE Scan on candidate_branches (cost=0.00..6703.76 rows=335188 width=4) (actual time=0.052..80.237 rows=372851 loops=1)
                                 -> Index Scan using branch_pkey on branch (cost=0.00..0.46 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=372851)
                                       Index Cond: (public.branch.id = candidate_branches.id)
                                       Filter: (public.branch.owner = 2287822)
               -> Nested Loop Left Join (cost=61353.56..69401.24 rows=641 width=777) (actual time=1070.360..1070.360 rows=0 loops=1)
                     -> Hash Semi Join (cost=61353.56..67161.41 rows=641 width=781) (actual time=1070.358..1070.358 rows=0 loops=1)
                           Hash Cond: (public.branchmergeproposal.source_branch = public.branch.id)
                           -> Hash Semi Join (cost=30682.39..34883.42 rows=641 width=781) (actual time=251.200..251.200 rows=0 loops=1)
                                 Hash Cond: (public.branchmergeproposal.target_branch = public.branch.id)
                                 -> Nested Loop (cost=11.22..2605.43 rows=641 width=781) (actual time=251.198..251.198 rows=0 loops=1)
                                       -> Bitmap Heap Scan on codereviewvote (cost=11.22..430.06 rows=641 width=8) (actual time=31.515..199.932 rows=641 loops=1)
                                             Recheck Cond: (reviewer = 2287822)
                                             -> Bitmap Index Scan on codereviewvote__reviewer__idx (cost=0.00..11.06 rows=641 width=0) (actual time=25.082..25.082 rows=641 loops=1)
                                                   Index Cond: (reviewer = 2287822)
                                       -> Index Scan using branchmergeproposal_pkey on branchmergeproposal (cost=0.00..3.38 rows=1 width=777) (actual time=0.079..0.079 rows=0 loops=641)
                                             Index Cond: (public.branchmergeproposal.id = codereviewvote.branch_merge_proposal)
                                             Filter: (public.branchmergeproposal.queue_status = ANY ('{3,2}'::integer[]))
                                 -> Hash (cost=24515.67..24515.67 rows=375160 width=4) (never executed)
                                       -> Seq Scan on branch (cost=3411.26..24515.67 rows=375160 width=4) (never executed)
                                             Filter: ((NOT private) OR (hashed SubPlan 2))
                                             SubPlan 2

                                               -> HashAggregate (cost=3411.20..3411.25 rows=5 width=4) (never executed)
                                                     -> Append (cost=0.00..3411.18 rows=5 width=4) (never executed)
                                                           -> Nested Loop (cost=0.00..1500.56 rows=3 width=4) (never executed)
                                                                 -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..82.72 rows=31 width=4) (never executed)
                                                                       Index Cond: (person = 2287822)
                                                                 -> Index Scan using branch_owner_idx on branch (cost=0.00..45.72 rows=1 width=8) (never executed)
                                                                       Index Cond: (public.branch.owner = public.teamparticipation.team)
                                                                       Filter: public.branch.private
                                                           -> Nested Loop (cost=0.00..1910.57 rows=2 width=4) (never executed)
                                                                 -> Nested Loop (cost=0.00..1890.21 rows=46 width=4) (never executed)
                                                                       -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..82.72 rows=31 width=4) (never executed)
                                                                             Index Cond: (person = 2287822)
                                                                       -> Index Scan using branchsubscription__person__branch__key on branchsubscription (cost=0.00..58.02 rows=23 width=8) (never executed)
                                                                             Index Cond: (public.branchsubscription.person = public.teamparticipation.team)
                                                                 -> Index Scan using branch_pkey on branch (cost=0.00..0.43 rows=1 width=4) (never executed)
                                                                       Index Cond: (public.branch.id = public.branchsubscription.branch)
                                                                       Filter: public.branch.private
                           -> Hash (cost=24515.67..24515.67 rows=375160 width=4) (actual time=814.657..814.657 rows=372851 loops=1)
                                 -> Seq Scan on branch (cost=3411.26..24515.67 rows=375160 width=4) (actual time=0.013..634.416 rows=372851 loops=1)
                                       Filter: ((NOT private) OR (hashed SubPlan 1))
                                       SubPlan 1
                                         -> HashAggregate (cost=3411.20..3411.25 rows=5 width=4) (actual time=237.484..241.333 rows=5578 loops=1)
                                               -> Append (cost=0.00..3411.18 rows=5 width=4) (actual time=76.407..230.129 rows=6301 loops=1)
                                                     -> Nested Loop (cost=0.00..1500.56 rows=3 width=4) (actual time=76.406..155.501 rows=392 loops=1)
                                                           -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..82.72 rows=31 width=4) (actual time=0.015..0.051 rows=24 loops=1)
                                                                 Index Cond: (person = 2287822)
                                                           -> Index Scan using branch_owner_idx on branch (cost=0.00..45.72 rows=1 width=8) (actual time=6.181..6.472 rows=16 loops=24)
                                                                 Index Cond: (public.branch.owner = public.teamparticipation.team)
                                                                 Filter: public.branch.private
                                                     -> Nested Loop (cost=0.00..1910.57 rows=2 width=4) (actual time=0.171..72.139 rows=5909 loops=1)
                                                           -> Nested Loop (cost=0.00..1890.21 rows=46 width=4) (actual time=0.079..13.302 rows=6022 loops=1)
                                                                 -> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..82.72 rows=31 width=4) (actual time=0.015..0.068 rows=24 loops=1)
                                                                       Index Cond: (person = 2287822)
                                                                 -> Index Scan using branchsubscription__person__branch__key on branchsubscription (cost=0.00..58.02 rows=23 width=8) (actual time=0.011..0.393 rows=251 loops=24)
                                                                       Index Cond: (public.branchsubscription.person = public.teamparticipation.team)
                                                           -> Index Scan using branch_pkey on branch (cost=0.00..0.43 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=6022)
                                                                 Index Cond: (public.branch.id = public.branchsubscription.branch)
                                                                 Filter: public.branch.private
                     -> Index Scan using codereviewmessage_pkey on codereviewmessage (cost=0.00..3.48 rows=1 width=4) (never executed)
                           Index Cond: (codereviewvote.vote_message = codereviewmessage.id)
 Total runtime: 9558.597 ms
(104 rows)

See original description

Related branches

lp:~lifeless/launchpad/bug-745310
Steve Kowalik (community): Approve (code)
Diff: 68 lines (+40/-11)
1 file modified
lib/lp/code/model/branchcollection.py (+40/-11)
Robert Collins (lifeless)
Changed in launchpad:
status: New → Triaged
tags: added: timeout
Changed in launchpad:
importance: Undecided → Critical
Robert Collins (lifeless)
tags: added: regression
description: updated
summary: - my code page consistently times out
+ Person:+branches timeout
Revision history for this message
Robert Collins (lifeless) wrote :

The fix for bug 736008 caused this: we have inappropriate common code; first thing is to define the query we really want I think.

Revision history for this message
Robert Collins (lifeless) wrote :

The 'proposals for branches I own' subclause is 4 seconds hot.
Proposals I reviewed is 500ms hot (still slow).

One tweak: when scoped_branches is unscoped, we save a seq scan on all branches. estimated cost goes down to 165044.

Revision history for this message
Robert Collins (lifeless) wrote :

bah, I mean, when scoped_branches is unscoped we should use Branch directly - temp tables with millions of rows are not ideal.

Revision history for this message
Robert Collins (lifeless) wrote :

however, for this case - where the source branch is constrained, we actually want this form of query:
SELECT COUNT(*)
FROM BranchMergeProposal join branch as source on source.id=branchmergeproposal.source_branch and source.owner=2287822 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 = 2287822
                                  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 = 2287822
                                  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 = 2287822
                             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 = 2287822
                             AND Branch.private = TRUE))
  AND BranchMergeProposal.queue_status IN (3,
                                           2)

Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

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

Changed in launchpad:
assignee: nobody → Robert Collins (lifeless)
milestone: none → 11.04
tags: added: qa-needstesting
Changed in launchpad:
status: Triaged → Fix Committed
Robert Collins (lifeless)
tags: added: qa-ok
removed: qa-needstesting
William Grant (wgrant)
Changed in launchpad:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.