Person:+branches timeout

Bug #745310 reported by Tim Cole on 2011-03-29
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Critical
Robert Collins

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)

Related branches

Changed in launchpad:
status: New → Triaged
tags: added: timeout
Changed in launchpad:
importance: Undecided → Critical
tags: added: regression
description: updated
summary: - my code page consistently times out
+ Person:+branches timeout
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.

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.

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.

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)

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

Other bug subscribers