Comment 10 for bug 787294

88 seconds:

 HashAggregate (cost=112166.83..112397.51 rows=23068 width=4)
   -> Nested Loop (cost=61556.62..112109.16 rows=23068 width=4)
         Join Filter: ((bugtask.product = ss.product) OR (bugtask.productseries = ss.productseries) OR (product.project = ss.project) OR ((bugtask.distribution = ss.distribution) AND ((bugtask.sourcepackagename = ss.sourcepackagename) OR (ss.sourcepackagename IS NULL))) OR (bugtask.distroseries = ss.distroseries) OR (bugtask.milestone = ss.milestone))
         -> Bitmap Heap Scan on structuralsubscription ss (cost=4.50..196.24 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.50 rows=167 width=0)
                     Index Cond: (subscriber = 343381)
         -> Hash Left Join (cost=61552.12..111278.55 rows=23068 width=32)
               Hash Cond: (bugtask.product = product.id)
               -> Hash Join (cost=58241.23..107655.41 rows=23068 width=28)
                     Hash Cond: (bugtask.bug = bug.id)
                     -> Bitmap Heap Scan on bugtask (cost=6436.30..52503.98 rows=311582 width=28)
                           Recheck Cond: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
                           -> BitmapOr (cost=6436.30..6436.30 rows=348787 width=0)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..3610.45 rows=211349 width=0)
                                       Index Cond: (status = 10)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..346.62 rows=20171 width=0)
                                       Index Cond: (status = 15)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..1085.74 rows=63520 width=0)
                                       Index Cond: (status = 20)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..554.49 rows=32287 width=0)
                                       Index Cond: (status = 21)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..99.87 rows=5671 width=0)
                                       Index Cond: (status = 22)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..271.75 rows=15789 width=0)
                                       Index Cond: (status = 25)
                     -> Hash (cost=51516.58..51516.58 rows=23068 width=4)
                           -> Bitmap Heap Scan on bug (cost=665.02..51516.58 rows=23068 width=4)
                                 Recheck Cond: ((latest_patch_uploaded IS NOT NULL) AND (duplicateof IS NULL))
                                 Filter: (NOT private)
                                 -> Bitmap Index Scan on bug__new_patches__idx (cost=0.00..659.26 rows=25088 width=0)
               -> Hash (cost=3023.71..3023.71 rows=22974 width=8)
                     -> Seq Scan on product (cost=0.00..3023.71 rows=22974 width=8)
                           Filter: active

and the minimal version (2 seconds) is

 HashAggregate (cost=131312.95..133906.53 rows=259358 width=4)
   -> Hash Join (cost=61747.57..129892.40 rows=568219 width=4)
         Hash Cond: (bugtask.distribution = ss.distribution)
         Join Filter: ((bugtask.sourcepackagename = ss.sourcepackagename) OR (ss.sourcepackagename IS NULL))
         -> Hash Left Join (cost=61552.12..111278.55 rows=23068 width=12)
               Hash Cond: (bugtask.product = product.id)
               -> Hash Join (cost=58241.23..107655.41 rows=23068 width=16)
                     Hash Cond: (bugtask.bug = bug.id)
                     -> Bitmap Heap Scan on bugtask (cost=6436.30..52503.98 rows=311582 width=16)
                           Recheck Cond: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
                           -> BitmapOr (cost=6436.30..6436.30 rows=348787 width=0)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..3610.45 rows=211349 width=0)
                                       Index Cond: (status = 10)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..346.62 rows=20171 width=0)
                                       Index Cond: (status = 15)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..1085.74 rows=63520 width=0)
                                       Index Cond: (status = 20)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..554.49 rows=32287 width=0)
                                       Index Cond: (status = 21)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..99.87 rows=5671 width=0)
                                       Index Cond: (status = 22)
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..271.75 rows=15789 width=0)
                                       Index Cond: (status = 25)
                     -> Hash (cost=51516.58..51516.58 rows=23068 width=4)
                           -> Bitmap Heap Scan on bug (cost=665.02..51516.58 rows=23068 width=4)
                                 Recheck Cond: ((latest_patch_uploaded IS NOT NULL) AND (duplicateof IS NULL))
                                 Filter: (NOT private)
                                 -> Bitmap Index Scan on bug__new_patches__idx (cost=0.00..659.26 rows=25088 width=0)
               -> Hash (cost=3023.71..3023.71 rows=22974 width=4)
                     -> Seq Scan on product (cost=0.00..3023.71 rows=22974 width=4)
                           Filter: active
         -> Hash (cost=193.36..193.36 rows=167 width=8)
               -> Bitmap Heap Scan on structuralsubscription ss (cost=4.55..193.36 rows=167 width=8)
                     Recheck Cond: (subscriber = 343381)
                     -> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..4.50 rows=167 width=0)
                           Index Cond: (subscriber = 343381)