Comment 20 for bug 421901

exists variant:

explain analyze SELECT count(*) from (SELECT DISTINCT ON (BugTask.id) BugTask.*
FROM BugTask left join product on bugtask.product = product.id, Bug
WHERE
 Bug.id = BugTask.bug
 AND exists (select true from BugMessage where Bug.id = BugMessage.bug and BugMessage.index > 0
AND BugMessage.owner = 100 )
AND BugTask.status in (10, 15, 20, 21, 22, 25)
 AND Bug.duplicateof IS NULL
AND Bug.private = FALSE
AND (bugtask.product is null or product.active)) as foo;

cold:

                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=190310.08..190310.09 rows=1 width=0) (actual time=5409.006..5409.007 rows=1 loops=1)
   -> Unique (cost=186991.52..187939.68 rows=189632 width=415) (actual time=5405.887..5408.446 rows=2032 loops=1)
         -> Sort (cost=186991.52..187465.60 rows=189632 width=415) (actual time=5405.884..5406.534 rows=2032 loops=1)
               Sort Key: bugtask.id
               Sort Method: quicksort Memory: 855kB
               -> Hash Join (cost=66001.50..139809.58 rows=189632 width=415) (actual time=810.173..5395.111 rows=2032 loops=1)
                     Hash Cond: (bugtask.bug = bug.id)
                     -> Hash Left Join (cost=9710.95..82448.99 rows=251780 width=415) (actual time=224.393..4527.200 rows=306265 loops=1)
                           Hash Cond: (bugtask.product = product.id)
                           Filter: ((bugtask.product IS NULL) OR product.active)
                           -> Bitmap Heap Scan on bugtask (cost=6573.33..75240.10 rows=276215 width=415) (actual time=173.626..3743.762 rows=308734 loops=1)
                                 Recheck Cond: (status = ANY ('{10,15,20,21,22,25}'::integer[]))
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..6504.28 rows=276215 width=0) (actual time=159.547..159.547 rows=309007 loops=1)
                                       Index Cond: (status = ANY ('{10,15,20,21,22,25}'::integer[]))
                           -> Hash (cost=2771.72..2771.72 rows=29272 width=5) (actual time=50.680..50.680 rows=29105 loops=1)
                                 -> Seq Scan on product (cost=0.00..2771.72 rows=29272 width=5) (actual time=0.019..39.297 rows=29105 loops=1)
                     -> Hash (cost=55957.26..55957.26 rows=26663 width=8) (actual time=574.459..574.459 rows=13676 loops=1)
                           -> Nested Loop (cost=36079.20..55957.26 rows=26663 width=8) (actual time=67.750..559.692 rows=13676 loops=1)
                                 -> HashAggregate (cost=36079.20..36116.95 rows=3775 width=4) (actual time=67.675..81.816 rows=14922 loops=1)
                                       -> Bitmap Heap Scan on bugmessage (cost=501.86..36012.55 rows=26663 width=4) (actual time=11.171..51.408 rows=32134 loops=1)
                                             Recheck Cond: ((owner = 100) AND (index > 0))
                                             -> Bitmap Index Scan on bugmessage__owner__index__idx (cost=0.00..495.20 rows=26663 width=0) (actual time=7.159..7.159 rows=32134 loops=1)
                                                   Index Cond: ((owner = 100) AND (index > 0))
                                 -> Index Scan using bug_pkey on bug (cost=0.00..5.24 rows=1 width=4) (actual time=0.030..0.031 rows=1 loops=14922)
                                       Index Cond: (bug.id = bugmessage.bug)
                                       Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 5411.736 ms

hot

                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=190310.08..190310.09 rows=1 width=0) (actual time=1015.203..1015.203 rows=1 loops=1)
   -> Unique (cost=186991.52..187939.68 rows=189632 width=415) (actual time=1013.383..1014.882 rows=2032 loops=1)
         -> Sort (cost=186991.52..187465.60 rows=189632 width=415) (actual time=1013.381..1013.779 rows=2032 loops=1)
               Sort Key: bugtask.id
               Sort Method: quicksort Memory: 855kB
               -> Hash Join (cost=66001.50..139809.58 rows=189632 width=415) (actual time=325.477..1008.460 rows=2032 loops=1)
                     Hash Cond: (bugtask.bug = bug.id)
                     -> Hash Left Join (cost=9710.95..82448.99 rows=251780 width=415) (actual time=132.490..692.790 rows=306265 loops=1)
                           Hash Cond: (bugtask.product = product.id)
                           Filter: ((bugtask.product IS NULL) OR product.active)
                           -> Bitmap Heap Scan on bugtask (cost=6573.33..75240.10 rows=276215 width=415) (actual time=95.737..264.375 rows=308734 loops=1)
                                 Recheck Cond: (status = ANY ('{10,15,20,21,22,25}'::integer[]))
                                 -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..6504.28 rows=276215 width=0) (actual time=82.996..82.996 rows=309007 loops=1)
                                       Index Cond: (status = ANY ('{10,15,20,21,22,25}'::integer[]))
                           -> Hash (cost=2771.72..2771.72 rows=29272 width=5) (actual time=36.664..36.664 rows=29105 loops=1)
                                 -> Seq Scan on product (cost=0.00..2771.72 rows=29272 width=5) (actual time=0.012..26.036 rows=29105 loops=1)
                     -> Hash (cost=55957.26..55957.26 rows=26663 width=8) (actual time=192.603..192.603 rows=13676 loops=1)
                           -> Nested Loop (cost=36079.20..55957.26 rows=26663 width=8) (actual time=71.729..186.031 rows=13676 loops=1)
                                 -> HashAggregate (cost=36079.20..36116.95 rows=3775 width=4) (actual time=71.695..78.681 rows=14922 loops=1)
                                       -> Bitmap Heap Scan on bugmessage (cost=501.86..36012.55 rows=26663 width=4) (actual time=11.397..56.140 rows=32134 loops=1)
                                             Recheck Cond: ((owner = 100) AND (index > 0))
                                             -> Bitmap Index Scan on bugmessage__owner__index__idx (cost=0.00..495.20 rows=26663 width=0) (actual time=7.465..7.465 rows=32134 loops=1)
                                                   Index Cond: ((owner = 100) AND (index > 0))
                                 -> Index Scan using bug_pkey on bug (cost=0.00..5.24 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=14922)
                                       Index Cond: (bug.id = bugmessage.bug)
                                       Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 1016.924 ms