Comment 16 for bug 421901

SELECT DISTINCT ON (BugTask.id) BugTask.*
FROM BugTask left join product on (bugtask.product = product.id and product.active), Bug, BugMessage, Message
WHERE
 Bug.id = BugTask.bug
 AND Bug.id = BugMessage.bug
AND BugMessage.message = Message.id
AND BugTask.status in (10, 15, 20, 21, 22, 25)
 AND Bug.duplicateof IS NULL
AND Bug.private = FALSE
AND BugMessage.index > 0
 AND Message.owner = 100
and (bugtask.product is null or product.active)
ORDER BY BugTask.id;

is actually a higher estimated cost than the current query, by 10%; still, at 4.8seconds its way too long.

                                                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique (cost=327803.87..327954.53 rows=30132 width=415) (actual time=4840.832..4843.424 rows=2032 loops=1)
   -> Sort (cost=327803.87..327879.20 rows=30132 width=415) (actual time=4840.830..4841.649 rows=5285 loops=1)
         Sort Key: bugtask.id
         Sort Method: quicksort Memory: 2316kB
         -> Nested Loop (cost=146017.99..320705.20 rows=30132 width=415) (actual time=169.558..4820.895 rows=5285 loops=1)
               -> Hash Left Join (cost=146017.99..297520.86 rows=30132 width=419) (actual time=168.398..4767.768 rows=6724 loops=1)
                     Hash Cond: (bugtask.product = product.id)
                     Filter: ((bugtask.product IS NULL) OR product.active)
                     -> Nested Loop (cost=142973.14..294071.41 rows=33056 width=419) (actual time=133.727..4722.996 rows=6733 loops=1)
                           -> Hash Join (cost=142973.14..256158.69 rows=75294 width=4) (actual time=122.161..4409.276 rows=32134 loops=1)
                                 Hash Cond: (bugmessage.message = message.id)
                                 -> Seq Scan on bugmessage (cost=0.00..70798.85 rows=3027910 width=8) (actual time=0.021..1354.923 rows=3026226 loops=1)
                                       Filter: (index > 0)
                                 -> Hash (cost=142031.96..142031.96 rows=75294 width=4) (actual time=122.059..122.059 rows=75175 loops=1)
                                       -> Bitmap Heap Scan on message (cost=1205.51..142031.96 rows=75294 width=4) (actual time=22.913..92.674 rows=75175 loops=1)
                                             Recheck Cond: (owner = 100)
                                             -> Bitmap Index Scan on message_owner_idx (cost=0.00..1186.69 rows=75294 width=0) (actual time=17.076..17.076 rows=75175 loops=1)
                                                   Index Cond: (owner = 100)
                           -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.49 rows=1 width=415) (actual time=0.008..0.009 rows=0 loops=32134)
                                 Index Cond: (bugtask.bug = bugmessage.bug)
                                 Filter: (bugtask.status = ANY ('{10,15,20,21,22,25}'::integer[]))
                     -> Hash (cost=2771.72..2771.72 rows=21851 width=5) (actual time=34.629..34.629 rows=21733 loops=1)
                           -> Seq Scan on product (cost=0.00..2771.72 rows=21851 width=5) (actual time=0.011..26.591 rows=21733 loops=1)
                                 Filter: active
               -> Index Scan using bug_pkey on bug (cost=0.00..0.76 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=6724)
                     Index Cond: (bug.id = bugtask.bug)
                     Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 4845.360 ms