2 SELECT COUNT(*) FROM ((SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assig ... L OR Product.active = TRUE) AND Bug.private = FALSE)) AS BugTask JOIN Bug ON BugTask.bug = Bug.id:
GET: 2 Robots: 2 Local: 0
2 https://launchpad.net/%7Eubuntu-server/+patches (Person:+patches)
OOPS-1969CE313, OOPS-1969I159
One 8.9 second query.
SELECT COUNT(*)
FROM (
(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.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active, Bug
WHERE Bug.id = BugTask.bug
AND BugTask.assignee = 343381
AND ((BugTask.status = 10)
OR (BugTask.status = 15)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof IS NULL
AND Bug.latest_patch_uploaded IS NOT NULL
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND Bug.private = FALSE)
UNION
(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.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active, Bug, BugSubscription
WHERE Bug.id = BugTask.bug
AND ((BugTask.status = 10)
OR (BugTask.status = 15)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof IS NULL
AND Bug.latest_patch_uploaded IS NOT NULL
AND Bug.id = BugSubscription.bug
AND BugSubscription.person = 343381
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND Bug.private = FALSE)
UNION
(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.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active, Bug
WHERE Bug.id = BugTask.bug
AND BugTask.OWNER = 343381
AND ((BugTask.status = 10)
OR (BugTask.status = 15)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof IS NULL
AND Bug.latest_patch_uploaded IS NOT NULL
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND BugTask.bug = Bug.id
AND Bug.OWNER = 343381
AND Bug.private = FALSE)
UNION
(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.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active, Bug
WHERE Bug.id = BugTask.bug
AND ((BugTask.status = 10)
OR (BugTask.status = 15)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof IS NULL
AND Bug.latest_patch_uploaded IS NOT NULL
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND Bug.id IN
(SELECT DISTINCT bug
FROM Bugmessage
WHERE BugMessage.INDEX > 0
AND BugMessage.OWNER = 343381)
AND Bug.private = FALSE)
UNION
(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.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active
JOIN StructuralSubscription ON BugTask.product = StructuralSubscription.product
OR BugTask.productseries = StructuralSubscription.productseries
OR Product.project = StructuralSubscription.project
AND BugTask.product = Product.id
OR BugTask.distribution = StructuralSubscription.distribution
AND (BugTask.sourcepackagename = StructuralSubscription.sourcepackagename
OR StructuralSubscription.sourcepackagename IS NULL)
OR BugTask.distroseries = StructuralSubscription.distroseries
OR BugTask.milestone = StructuralSubscription.milestone, Bug
WHERE Bug.id = BugTask.bug
AND ((BugTask.status = 10)
OR (BugTask.status = 15)
OR (BugTask.status = 20)
OR (BugTask.status = 21)
OR (BugTask.status = 22)
OR (BugTask.status = 25))
AND Bug.duplicateof IS NULL
AND Bug.latest_patch_uploaded IS NOT NULL
AND StructuralSubscription.subscriber = 343381
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND Bug.private = FALSE)) AS BugTask
JOIN Bug ON BugTask.bug = Bug.id;
The structural subscription adds substantial overhead - probably do to a pg bug or some such. recasting as a wide query (see comment 11) addresses this.
Aggregate (cost=816866. 73..816866. 74 rows=1 width=0) 62..816816. 29 rows=20177 width=0) 62..706128. 39 rows=20177 width=280) 00..704564. 67 rows=20177 width=280)
-> Nested Loop Left Join (cost=0.00..71.68 rows=3 width=280)
Filter: ((public. bugtask. product IS NULL) OR public. product. active)
- > Nested Loop (cost=0.00..52.83 rows=3 width=280)
-> Index Scan using bugtask_ _assignee_ _idx on bugtask (cost=0.00..32.64 rows=3 width=280)
Index Cond: (assignee = 343381)
Filter: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
-> Index Scan using bug_pkey on bug (cost=0.00..6.72 rows=1 width=4)
Index Cond: (public.bug.id = public.bugtask.bug)
Filter: ((public. bug.duplicateof IS NULL) AND (public. bug.latest_ patch_uploaded IS NOT NULL) AND (NOT public. bug.private) )
- > Index Scan using product_pkey on product (cost=0.00..6.27 rows=1 width=5)
Index Cond: (public. bugtask. product = public.product.id)
Filter: public. product. activeh, BugTask. date_assigned, BugTask. date_closed, BugTask. date_confirmed, BugTask. date_fix_ committed, BugTask. date_fix_ released, BugTask. date_incomplete , BugTask. date_inprogress , B -> Nested Loop (cost=0.00..1304.50 rows=4 width=280) sk.datecreated, BugTask. distribution, BugTask. distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask -> Nested Loop Left Join (cost=0.00..1008.40 rows=44 width=284) k.targetnamecac he
Filter: ((public. bugtask. product IS NULL) OR public. product. active)
-> Nested Loop (cost=0.00..993.50 rows=48 width=284)
-> Index Scan using bugsubscription _person_ idx on bugsubscription (cost=0.00..255.56 rows=115 width=4)
Index Cond: (person = 343381)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.40 rows=1 width=280)
Index Cond: (public.bugtask.bug = bugsubscription .bug)
Filter: ((public. bugtask. status = 10) OR (public. bugtask. status = 15) OR (public. bugtask. status = 20) OR (public. bugtask. status = 21) OR (public. bugtask. status = 22) OR (public. bugtask. status = 25)) Bug ON BugTask.bug = Bug.id;E)) AS BugTaskr = 343381lestone, Bugsepackagenam eroductgTask. targetnamecache on, BugTask. distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.OWNER, BugTask.product, BugTask
-> Index Scan using product_pkey on product...
-> Nested Loop (cost=705926.
-> HashAggregate (cost=705926.
-> Append (cost=0.