Comment 34 for bug 421901

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)