Comment 6 for bug 618406

Ugh ;( - thats surprising - this is the plan I see with (heat, id desc) + a modified query (ORDER BY HEAT DESC, Bug.ID)

                                                                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..6948.98 rows=40 width=1017) (actual time=0.068..1.751 rows=40 loops=1)
   -> Nested Loop (cost=0.00..28763892.59 rows=165572 width=1017) (actual time=0.067..1.731 rows=40 loops=1)
         -> Index Scan Backward using bug_heat_test on bug (cost=0.00..28458137.93 rows=555476 width=741) (actual time=0.030..0.244 rows=67 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 -> Unique (cost=39.25..39.26 rows=2 width=4) (never executed)
                       -> Sort (cost=39.25..39.25 rows=2 width=4) (never executed)
                             Sort Key: bugsubscription.bug
                             -> Append (cost=0.00..39.24 rows=2 width=4) (never executed)
                                   -> Nested Loop (cost=0.00..26.48 rows=1 width=4) (never executed)
                                         -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   -> Nested Loop (cost=0.00..12.74 rows=1 width=4) (never executed)
                                         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.34 rows=1 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.54 rows=1 width=276) (actual time=0.016..0.020 rows=1 loops=67)
               Index Cond: (public.bugtask.bug = bug.id)
               Filter: ((public.bugtask.distribution = 1) AND ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
 Total runtime: 2.020 ms
(23 rows)

and for e.g. launchpad (this is estimated at 50% cheaper).

                                                                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=0.00..146128.04 rows=40 width=1017) (actual time=1.302..500.965 rows=40 loops=1)
   -> Nested Loop (cost=0.00..28736079.82 rows=7866 width=1017) (actual time=1.301..500.932 rows=40 loops=1)
         -> Index Scan Backward using bug_heat_test on bug (cost=0.00..28458137.93 rows=555476 width=741) (actual time=0.033..451.044 rows=9434 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 -> Unique (cost=39.25..39.26 rows=2 width=4) (actual time=0.059..0.059 rows=0 loops=6696)
                       -> Sort (cost=39.25..39.25 rows=2 width=4) (actual time=0.058..0.058 rows=0 loops=6696)
                             Sort Key: bugsubscription.bug
                             Sort Method: quicksort Memory: 25kB
                             -> Append (cost=0.00..39.24 rows=2 width=4) (actual time=0.043..0.055 rows=0 loops=6696)
                                   -> Nested Loop (cost=0.00..26.48 rows=1 width=4) (actual time=0.032..0.038 rows=0 loops=6696)
                                         -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (actual time=0.011..0.016 rows=4 loops=6696)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=23960)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   -> Nested Loop (cost=0.00..12.74 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=6696)
                                         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.34 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=6696)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=7303)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         -> Index Scan using bugtask__product__bug__key on bugtask (cost=0.00..0.49 rows=1 width=276) (actual time=0.005..0.005 rows=0 loops=9434)
               Index Cond: ((public.bugtask.product = 10294) AND (public.bugtask.bug = bug.id))
               Filter: ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25))
 Total runtime: 501.258 ms
(24 rows)

I'm curious why we're seeing different results :(