Comment 3 for bug 787294

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