Dropping bugtask.id from the sort order makes this a 3ms query:
Limit (cost=0.00..6939.56 rows=40 width=1017) (actual time=0.144..2.664 rows=40 loops=1) -> Nested Loop (cost=0.00..28715046.16 rows=165515 width=1017) (actual time=0.142..2.647 rows=40 loops=1) -> Index Scan Backward using bug__heat__idx on bug (cost=0.00..28409422.84 rows=555261 width=741) (actual time=0.075..0.473 rows=64 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.026..0.031 rows=1 loops=64) 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: 3.002 ms
Anything sorting by heat shouldn't care about bugtask ids, so this is a no brainer - though we /may/ have some test fallout.
Dropping bugtask.id from the sort order makes this a 3ms query:
Limit (cost=0.00..6939.56 rows=40 width=1017) (actual time=0.144..2.664 rows=40 loops=1) 00..28715046. 16 rows=165515 width=1017) (actual time=0.142..2.647 rows=40 loops=1) 00..28409422. 84 rows=555261 width=741) (actual time=0.075..0.473 rows=64 loops=1)
Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
SubPlan 1
-> 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 teamparticipati on_team_ key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
Index Cond: ((public. teamparticipati on.team = bugsubscription .person) AND (public. teamparticipati on.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 teamparticipati on_team_ key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
Index Cond: ((public. teamparticipati on.team = public. bugtask. assignee) AND (public. teamparticipati on.person = 2))
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)))
-> Nested Loop (cost=0.
-> Index Scan Backward using bug__heat__idx on bug (cost=0.
-> Unique (cost=39.25..39.26 rows=2 width=4) (never executed)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.54 rows=1 width=276) (actual time=0.026..0.031 rows=1 loops=64)
Total runtime: 3.002 ms
Anything sorting by heat shouldn't care about bugtask ids, so this is a no brainer - though we /may/ have some test fallout.