| 2011-03-29 23:07:34 |
Robert Collins |
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. |
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) |
|
