Structural sub lookup is the culprit:
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)
->
Nested Loop (cost=43392.43..702903.59 rows=20168 width=280)
Join Filter: ((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))
-> Bitmap Heap Scan on structuralsubscription (cost=4.48..188.09 rows=1 width=28)
Recheck Cond: (subscriber = 343381)
Filter: (((subscriber = 343381) AND (product IS NOT NULL)) OR ((subscriber = 343381) AND (productseries IS NOT NULL)) OR ((subscriber = 343381) AND (project IS NOT NULL)) OR ((subscriber = 343381) AND (distribution IS NOT NULL) AND (sourcepackagename IS NOT NULL)) OR ((subscriber = 343381) AND (distribution IS NOT NULL) AND (sourcepackagename IS NULL)) OR ((subscriber = 343381) AND (distroseries IS NOT NULL)) OR ((subscriber = 343381) AND (milestone IS NOT NULL)))
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..4.48 rows=164 width=0) Index Cond: (subscriber = 343381)
-> Nested Loop Left Join (cost=43387.94..702110.46 rows=20168 width=288)
Filter: ((bugtask.product IS NULL) OR product.active)
-> Nested Loop (cost=43387.94..695244.24 rows=22125 width=280)
-> Bitmap Heap Scan on bug (cost=43387.94..614073.75 rows=22125 width=4) Recheck Cond: (duplicateof IS NULL) Filter: ((latest_patch_uploaded IS NOT NULL) AND (NOT private)) -> Bitmap Index Scan on bug_duplicateof_idx (cost=0.00..43382.41 rows=577824 width=0) Index Cond: (duplicateof IS NULL)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.66 rows=1 width=280) Index Cond: (bugtask.bug = bug.id) Filter: ((bugtask.status = 10) OR (bugtask.status = 15) OR (bugtask.status = 20) OR (bugtask.status = 21) OR (bugtask.status = 22) OR (bugtask.status = 25))
-> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=9) Index Cond: (bugtask.product = product.id) Filter: product.active
Structural sub lookup is the culprit: 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. sourcepackagena me, BugTask.status, BugTask. statusexplanati on, BugTask. targetnamecache ription ON BugTask.product = StructuralSubsc ription. product productseries = StructuralSubsc ription. productseries ription. project distribution = StructuralSubsc ription. distribution sourcepackagena me = StructuralSubsc ription. sourcepackagena me ription. sourcepackagena me IS NULL) distroseries = StructuralSubsc ription. distroseries ription. milestone, Bug patch_uploaded IS NOT NULL ription. subscriber = 343381
SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.
FROM BugTask
LEFT JOIN Product ON BugTask.product = Product.id
AND Product.active
JOIN StructuralSubsc
OR BugTask.
OR Product.project = StructuralSubsc
AND BugTask.product = Product.id
OR BugTask.
AND (BugTask.
OR StructuralSubsc
OR BugTask.
OR BugTask.milestone = StructuralSubsc
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_
AND StructuralSubsc
AND (Bugtask.product IS NULL
OR Product.active = TRUE)
AND Bug.private = FALSE)
->
Nested Loop (cost=43392. 43..702903. 59 rows=20168 width=280) ription. product) OR (bugtask. productseries = structuralsubsc ription. productseries) OR ((product.project = structuralsubsc ription. project) AND (bugtask.product = product.id)) OR ((bugtask. distribution = structuralsubsc ription. distribution) AND ((bugtask. sourcepackagena me = structuralsubsc ription. sourcepackagena me) OR (structuralsubs cription. sourcepackagena me IS NULL))) OR (bugtask. distroseries = structuralsubsc ription. distroseries) OR (bugtask.milestone = structuralsubsc ription. milestone) ) ription (cost=4.48..188.09 rows=1 width=28) ription_ _subscriber_ _idx (cost=0.00..4.48 rows=164 width=0)
Index Cond: (subscriber = 343381) 94..702110. 46 rows=20168 width=288) 94..695244. 24 rows=22125 width=280) 94..614073. 75 rows=22125 width=4)
Recheck Cond: (duplicateof IS NULL)
Filter: ((latest_ patch_uploaded IS NOT NULL) AND (NOT private))
-> Bitmap Index Scan on bug_duplicateof_idx (cost=0. 00..43382. 41 rows=577824 width=0)
Index Cond: (duplicateof IS NULL)
Index Cond: (bugtask.bug = bug.id)
Filter: ((bugtask.status = 10) OR (bugtask.status = 15) OR (bugtask.status = 20) OR (bugtask.status = 21) OR (bugtask.status = 22) OR (bugtask.status = 25))
Index Cond: (bugtask.product = product.id)
Filter: product.active
Join Filter: ((bugtask.product = structuralsubsc
-> Bitmap Heap Scan on structuralsubsc
Recheck Cond: (subscriber = 343381)
Filter: (((subscriber = 343381) AND (product IS NOT NULL)) OR ((subscriber = 343381) AND (productseries IS NOT NULL)) OR ((subscriber = 343381) AND (project IS NOT NULL)) OR ((subscriber = 343381) AND (distribution IS NOT NULL) AND (sourcepackagename IS NOT NULL)) OR ((subscriber = 343381) AND (distribution IS NOT NULL) AND (sourcepackagename IS NULL)) OR ((subscriber = 343381) AND (distroseries IS NOT NULL)) OR ((subscriber = 343381) AND (milestone IS NOT NULL)))
-> Bitmap Index Scan on structuralsubsc
-> Nested Loop Left Join (cost=43387.
Filter: ((bugtask.product IS NULL) OR product.active)
-> Nested Loop (cost=43387.
-> Bitmap Heap Scan on bug (cost=43387.
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.66 rows=1 width=280)
-> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=9)