Go to https://bugs.launchpad.net/bugs/+bugs?advanced=1
Search for tags "pcert" and "blockshwcert", select "Any", then search.
Error ID: OOPS-1920V775
33. 748 11217ms SQL-launchpad-main-slave
SELECT BugTask.assignee,
BugTask.bug,
BugTask.bugwatch,
BugTask.date_assigned,
BugTask.date_closed,
BugTask.date_confirmed,
BugTask.date_fix_committed,
BugTask.date_fix_released,
BugTask.date_incomplete,
BugTask.date_inprogress,
BugTask.date_left_closed,
BugTask.date_left_new,
BugTask.date_triaged,
BugTask.datecreated,
BugTask.distribution,
BugTask.distroseries,
BugTask.id,
BugTask.importance,
BugTask.milestone,
BugTask.OWNER, BugTask.product,
BugTask.productseries,
BugTask.sourcepackagename,
BugTask.status,
BugTask.statusexplanation,
BugTask.targetnamecache,
Bug.date_last_message,
Bug.date_last_updated,
Bug.date_made_private,
Bug.datecreated,
Bug.description,
Bug.duplicateof,
Bug.heat,
Bug.heat_last_updated,
Bug.id,
Bug.latest_patch_uploaded,
Bug.message_count,
Bug.name,
Bug.number_of_duplicates,
Bug.OWNER, Bug.private,
Bug.security_related,
Bug.title,
Bug.users_affected_count,
Bug.users_unaffected_count,
Bug.who_made_private
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active
JOIN Bug ON BugTask.bug = Bug.id
WHERE Bug.id = BugTask.bug
AND ((BugTask.status = 10)
OR (BugTask.status = 15)
AND (Bug.date_last_message IS NOT NULL
AND BugTask.date_incomplete <= Bug.date_last_message)
OR (BugTask.status = 15)
AND (Bug.date_last_message IS NULL
OR BugTask.date_incomplete > Bug.date_last_message)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof IS NULL
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND EXISTS
(SELECT TRUE
FROM BugTag
WHERE BugTag.bug = Bug.id
AND BugTag.tag IN ('flibert',
'pcert'))
AND (Bug.private = FALSE
OR EXISTS
(SELECT BugSubscription.bug
FROM BugSubscription,
TeamParticipation
WHERE TeamParticipation.person = 567070
AND TeamParticipation.team = BugSubscription.person
AND BugSubscription.bug = Bug.id
UNION SELECT BugTask.bug
FROM BugTask,
TeamParticipation
WHERE TeamParticipation.person = 567070
AND TeamParticipation.team = BugTask.assignee
AND BugTask.bug = Bug.id))
ORDER BY BugTask.importance DESC, BugTask.id LIMIT 76
OFFSET 0;
cold:
Limit (cost=2547.29..3606.11 rows=76 width=1017) (actual time=2697.807..28479.455 rows=54 loops=1)
-> Nested Loop (cost=2547.29..1583735.63 rows=113494 width=1017) (actual time=2697.806..28479.372 rows=54 loops=1)
Join Filter: ((public.bugtask.bug = bug.id) AND ((public.bugtask.status = 10) OR ((public.bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (public.bugtask.date_incomplete <= bug.date_last_message)) OR ((public.bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (public.bugtask.date_incomplete > bug.date_last_message))) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
-> Nested Loop Left Join (cost=0.00..311068.02 rows=252008 width=276) (actual time=15.467..13566.829 rows=306253 loops=1)
Filter: ((public.bugtask.product IS NULL) OR product.active)
-> Index Scan Backward using bugtask_importance_idx on bugtask (cost=0.00..225300.43 rows=276466 width=276) (actual time=15.458..12244.891 rows=308723 loops=1)
Filter: ((status = 10) OR ((date_incomplete IS NOT NULL) AND (status = 15)) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
-> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=5) (actual time=0.002..0.003 rows=0 loops=308723)
Index Cond: (public.bugtask.product = product.id)
Filter: product.active
-> Materialize (cost=2547.29..2548.41 rows=112 width=745) (actual time=0.000..0.015 rows=84 loops=306253)
-> Nested Loop (cost=307.19..2547.18 rows=112 width=745) (actual time=66.552..70.926 rows=84 loops=1)
-> HashAggregate (cost=307.19..307.68 rows=49 width=4) (actual time=66.456..66.513 rows=107 loops=1)
-> Bitmap Heap Scan on bugtag (cost=7.46..306.91 rows=112 width=4) (actual time=55.965..66.368 rows=107 loops=1)
Recheck Cond: (tag = ANY ('{flibert,pcert}'::text[]))
-> Bitmap Index Scan on bugtag__tag__bug__key (cost=0.00..7.43 rows=112 width=0) (actual time=55.925..55.925 rows=107 loops=1)
Index Cond: (tag = ANY ('{flibert,pcert}'::text[]))
-> Index Scan using bug_pkey on bug (cost=0.00..45.69 rows=1 width=741) (actual time=0.039..0.040 rows=1 loops=107)
Index Cond: (bug.id = bugtag.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 1)))
SubPlan 1
-> Unique (cost=39.33..39.34 rows=2 width=4) (actual time=0.169..0.169 rows=0 loops=6)
-> Sort (cost=39.33..39.33 rows=2 width=4) (actual time=0.168..0.168 rows=0 loops=6)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.00..39.32 rows=2 width=4) (actual time=0.153..0.153 rows=0 loops=6)
-> Nested Loop (cost=0.00..26.48 rows=1 width=4) (actual time=0.095..0.095 rows=0 loops=6)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (actual time=0.043..0.051 rows=3 loops=6)
Index Cond: (bug = $0)
-> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=16)
Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 567070))
-> Nested Loop (cost=0.00..12.82 rows=1 width=4) (actual time=0.056..0.056 rows=0 loops=6)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.42 rows=1 width=8) (actual time=0.040..0.040 rows=1 loops=6)
Index Cond: (bug = $0)
-> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=6)
Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 567070))
Total runtime: 28480.320 ms
(37 rows)
hot:
Limit (cost=2547.29..3606.11 rows=76 width=1017) (actual time=568.974..15495.529 rows=54 loops=1)
-> Nested Loop (cost=2547.29..1583735.63 rows=113494 width=1017) (actual time=568.971..15495.477 rows=54 loops=1)
Join Filter: ((public.bugtask.bug = bug.id) AND ((public.bugtask.status = 10) OR ((public.bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (public.bugtask.date_incomplete <= bug.date_last_message)) OR ((public.bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (public.bugtask.date_incomplete > bug.date_last_message))) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
-> Nested Loop Left Join (cost=0.00..311068.02 rows=252008 width=276) (actual time=0.194..4114.883 rows=306253 loops=1)
Filter: ((public.bugtask.product IS NULL) OR product.active)
-> Index Scan Backward using bugtask_importance_idx on bugtask (cost=0.00..225300.43 rows=276466 width=276) (actual time=0.186..3185.118 rows=308723 loops=1)
Filter: ((status = 10) OR ((date_incomplete IS NOT NULL) AND (status = 15)) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
-> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=308723)
Index Cond: (public.bugtask.product = product.id)
Filter: product.active
-> Materialize (cost=2547.29..2548.41 rows=112 width=745) (actual time=0.000..0.011 rows=84 loops=306253)
-> Nested Loop (cost=307.19..2547.18 rows=112 width=745) (actual time=2.243..8.248 rows=84 loops=1)
-> HashAggregate (cost=307.19..307.68 rows=49 width=4) (actual time=2.178..2.298 rows=107 loops=1)
-> Bitmap Heap Scan on bugtag (cost=7.46..306.91 rows=112 width=4) (actual time=0.260..2.030 rows=107 loops=1)
Recheck Cond: (tag = ANY ('{flibert,pcert}'::text[]))
-> Bitmap Index Scan on bugtag__tag__bug__key (cost=0.00..7.43 rows=112 width=0) (actual time=0.201..0.201 rows=107 loops=1)
Index Cond: (tag = ANY ('{flibert,pcert}'::text[]))
-> Index Scan using bug_pkey on bug (cost=0.00..45.69 rows=1 width=741) (actual time=0.052..0.054 rows=1 loops=107)
Index Cond: (bug.id = bugtag.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 1)))
SubPlan 1
-> Unique (cost=39.33..39.34 rows=2 width=4) (actual time=0.178..0.178 rows=0 loops=6)
-> Sort (cost=39.33..39.33 rows=2 width=4) (actual time=0.175..0.175 rows=0 loops=6)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.00..39.32 rows=2 width=4) (actual time=0.163..0.163 rows=0 loops=6)
-> Nested Loop (cost=0.00..26.48 rows=1 width=4) (actual time=0.113..0.113 rows=0 loops=6)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (actual time=0.052..0.063 rows=3 loops=6)
Index Cond: (bug = $0)
-> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=16)
Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 567070))
-> Nested Loop (cost=0.00..12.82 rows=1 width=4) (actual time=0.046..0.046 rows=0 loops=6)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.42 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=6)
Index Cond: (bug = $0)
-> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=6)
Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 567070))
Total runtime: 15496.047 ms
We had a timeout on ubuntu/launchpad scope bug searches previously with tag combining; this may be a case where the needed query for global vs constrained searches is different. I hope not :)