2011-05-24 02:01:48 |
Robert Collins |
description |
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 |
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; |
|
2011-05-24 06:52:18 |
Robert Collins |
description |
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; |
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. |
|