SELECT COUNT(*) FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug AND bugtask.status in (10,15,20,21,22,25) AND Bug.duplicateof is 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 = 100) AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id ))

gives

launchpad_qastaging=> explain analyze SELECT COUNT(*) FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug AND bugtask.status in (10,15,20,21,22,25) AND Bug.duplicateof is 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 = 100) AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id ));
                                                                                         QUERY PLAN                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=65169.96..65169.97 rows=1 width=0) (actual time=282.109..282.110 rows=1 loops=1)
   ->  Nested Loop  (cost=35978.83..65169.50 rows=182 width=0) (actual time=120.248..281.635 rows=2037 loops=1)
         ->  Nested Loop Left Join  (cost=35978.83..56788.78 rows=182 width=8) (actual time=120.228..259.906 rows=2722 loops=1)
               Filter: ((public.bugtask.product IS NULL) OR product.active)
               ->  Nested Loop  (cost=35978.83..56726.71 rows=200 width=12) (actual time=120.222..254.139 rows=2728 loops=1)
                     ->  HashAggregate  (cost=35978.83..36016.24 rows=3741 width=4) (actual time=120.103..128.789 rows=14922 loops=1)
                           ->  Bitmap Heap Scan on bugmessage  (cost=597.90..35912.93 rows=26360 width=4) (actual time=22.410..92.976 rows=32134 loops=1)
                                 Recheck Cond: ((owner = 100) AND (index > 0))
                                 ->  Bitmap Index Scan on bugmessage__owner__index__idx  (cost=0.00..591.31 rows=26360 width=0) (actual time=13.509..13.509 rows=32134 loops=1)
                                       Index Cond: ((owner = 100) AND (index > 0))
                     ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..5.51 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=14922)
                           Index Cond: (public.bugtask.bug = bugmessage.bug)
                           Filter: (public.bugtask.status = ANY ('{10,15,20,21,22,25}'::integer[]))
               ->  Index Scan using product_pkey on product  (cost=0.00..0.30 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=2728)
                     Index Cond: (public.bugtask.product = product.id)
                     Filter: product.active
         ->  Index Scan using bug_pkey on bug  (cost=0.00..46.04 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=2722)
               Index Cond: (bug.id = public.bugtask.bug)
               Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 1)))
               SubPlan 1
                 ->  Unique  (cost=39.33..39.34 rows=2 width=4) (actual time=0.060..0.060 rows=0 loops=34)
                       ->  Sort  (cost=39.33..39.33 rows=2 width=4) (actual time=0.058..0.058 rows=0 loops=34)
                             Sort Key: bugsubscription.bug
                             Sort Method:  quicksort  Memory: 25kB
                             ->  Append  (cost=0.00..39.32 rows=2 width=4) (actual time=0.046..0.050 rows=0 loops=34)
                                   ->  Nested Loop  (cost=0.00..26.48 rows=1 width=4) (actual time=0.035..0.038 rows=0 loops=34)
                                         ->  Index Scan using bugsubscription_bug_idx on bugsubscription  (cost=0.00..7.26 rows=3 width=8) (actual time=0.010..0.012 rows=4 loops=34)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=129)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   ->  Nested Loop  (cost=0.00..12.82 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=34)
                                         ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..6.42 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=34)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=41)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
 Total runtime: 282.459 ms
(36 rows)