The following query should be the same and performs in <1s:
SELECT COUNT(DISTINCT BugTask.id)
FROM Bug
JOIN BugTask ON Bug.id = BugTask.bug
LEFT OUTER JOIN Product ON BugTask.product = Product.id
JOIN StructuralSubscription ON (
BugTask.product = StructuralSubscription.product
OR ( BugTask.distribution = StructuralSubscription.distribution
AND ( BugTask.sourcepackagename IS NULL
OR BugTask.sourcepackagename
= StructuralSubscription.sourcepackagename
)
)
OR BugTask.distroseries = StructuralSubscription.distroseries
OR BugTask.milestone = StructuralSubscription.milestone
OR BugTask.productseries = StructuralSubscription.productseries
OR Product.project = StructuralSubscription.project
)
WHERE
BugTask.importance = 40
AND BugTask.distribution = 1
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.distroseries is NULL
AND BugTask.productseries is NULL
AND StructuralSubscription.subscriber = 458651
AND (Bug.private = FALSE OR EXISTS (
SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation
WHERE TeamParticipation.person = 972620
AND BugSubscription.person = TeamParticipation.team
AND BugSubscription.bug = Bug.id
))
It is still horrible though - the data model makes joining BugTask with StructuralSubscription problematic. I suspect we need to define a BugTarget (or just Target) and link StructuralSubscription and BugTask to that instead of (distribution OR distroseries OR (distribution AND sourcepackagename) OR Project OR Product OR Milestone OR productseries).
The following query should be the same and performs in <1s:
SELECT COUNT(DISTINCT BugTask.id) ription ON ( ription. product
BugTask. distribution = StructuralSubsc ription. distribution
BugTask. sourcepackagena me IS NULL sourcepackagena me ription. sourcepackagena me distroseries = StructuralSubsc ription. distroseries ription. milestone productseries = StructuralSubsc ription. productseries ription. project importance = 40 distribution = 1
(BugTask. status = 10)
Bug. date_last_ message IS NOT NULL date_incomplete <= Bug.date_ last_message
Bug. date_last_ message IS NULL date_incomplete > Bug.date_ last_message) distroseries is NULL productseries is NULL ription. subscriber = 458651
TeamPartic ipation. person = 972620 .person = TeamParticipati on.team
FROM Bug
JOIN BugTask ON Bug.id = BugTask.bug
LEFT OUTER JOIN Product ON BugTask.product = Product.id
JOIN StructuralSubsc
BugTask.product = StructuralSubsc
OR (
AND (
OR BugTask.
= StructuralSubsc
)
)
OR BugTask.
OR BugTask.milestone = StructuralSubsc
OR BugTask.
OR Product.project = StructuralSubsc
)
WHERE
BugTask.
AND BugTask.
AND (
OR (BugTask.status = 15) AND (
AND BugTask.
)
OR (BugTask.status = 15) AND (
OR BugTask.
OR (BugTask.status = 20) OR (BugTask.status = 21)
OR (BugTask.status = 22) OR (BugTask.status = 25)
)
AND Bug.duplicateof is NULL
AND BugTask.
AND BugTask.
AND StructuralSubsc
AND (Bug.private = FALSE OR EXISTS (
SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation
WHERE
AND BugSubscription
AND BugSubscription.bug = Bug.id
))
It is still horrible though - the data model makes joining BugTask with StructuralSubsc ription problematic. I suspect we need to define a BugTarget (or just Target) and link StructuralSubsc ription and BugTask to that instead of (distribution OR distroseries OR (distribution AND sourcepackagename) OR Project OR Product OR Milestone OR productseries).