Comment 18 for bug 421901

Testing that change:

SELECT count(*) from (SELECT DISTINCT ON (BugTask.id) BugTask.*
FROM BugTask, Bug, BugMessage
WHERE
 Bug.id = BugTask.bug
 AND Bug.id = BugMessage.bug
AND BugTask.status in (10, 15, 20, 21, 22, 25)
 AND Bug.duplicateof IS NULL
AND Bug.private = FALSE
AND BugMessage.index > 0
AND BugMessage.owner = 100) as foo;

cold

                                                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=198303.34..198303.35 rows=1 width=0) (actual time=16683.662..16683.663 rows=1 loops=1)
   -> Unique (cost=198098.06..198156.71 rows=11730 width=415) (actual time=16680.762..16683.359 rows=2038 loops=1)
         -> Sort (cost=198098.06..198127.39 rows=11730 width=415) (actual time=16680.760..16681.563 rows=5294 loops=1)
               Sort Key: bugtask.id
               Sort Method: quicksort Memory: 2317kB
               -> Nested Loop (cost=501.86..197305.24 rows=11730 width=415) (actual time=56.892..16653.912 rows=5294 loops=1)
                     -> Nested Loop (cost=501.86..128582.62 rows=11730 width=419) (actual time=56.820..16522.481 rows=6733 loops=1)
                           -> Bitmap Heap Scan on bugmessage (cost=501.86..36012.55 rows=26663 width=4) (actual time=26.446..356.463 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=18.258..18.258 rows=32134 loops=1)
                                       Index Cond: ((owner = 100) AND (index > 0))
                           -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.46 rows=1 width=415) (actual time=0.470..0.502 rows=0 loops=32134)
                                 Index Cond: (bugtask.bug = bugmessage.bug)
                                 Filter: (bugtask.status = ANY ('{10,15,20,21,22,25}'::integer[]))
                     -> Index Scan using bug_pkey on bug (cost=0.00..5.85 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=6733)
                           Index Cond: (bug.id = bugtask.bug)
                           Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 16684.306 ms

hot

                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate (cost=198303.34..198303.35 rows=1 width=0) (actual time=522.657..522.658 rows=1 loops=1)
   -> Unique (cost=198098.06..198156.71 rows=11730 width=415) (actual time=517.456..522.067 rows=2038 loops=1)
         -> Sort (cost=198098.06..198127.39 rows=11730 width=415) (actual time=517.453..518.947 rows=5294 loops=1)
               Sort Key: bugtask.id
               Sort Method: quicksort Memory: 2317kB
               -> Nested Loop (cost=501.86..197305.24 rows=11730 width=415) (actual time=13.111..499.625 rows=5294 loops=1)
                     -> Nested Loop (cost=501.86..128582.62 rows=11730 width=419) (actual time=13.091..440.793 rows=6733 loops=1)
                           -> Bitmap Heap Scan on bugmessage (cost=501.86..36012.55 rows=26663 width=4) (actual time=12.973..70.324 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.633..7.633 rows=32134 loops=1)
                                       Index Cond: ((owner = 100) AND (index > 0))
                           -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.46 rows=1 width=415) (actual time=0.010..0.011 rows=0 loops=32134)
                                 Index Cond: (bugtask.bug = bugmessage.bug)
                                 Filter: (bugtask.status = ANY ('{10,15,20,21,22,25}'::integer[]))
                     -> Index Scan using bug_pkey on bug (cost=0.00..5.85 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=6733)
                           Index Cond: (bug.id = bugtask.bug)
                           Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 523.483 ms