explain analyze SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache, Bug.date_last_message, Bug.date_last_updated, Bug.date_made_private, Bug.datecreated, Bug.description, Bug.duplicateof, Bug.heat, Bug.heat_last_updated, Bug.id, Bug.latest_patch_uploaded, Bug.message_count, Bug.name, Bug.number_of_duplicates, Bug.owner, Bug.private, Bug.security_related, Bug.title, Bug.users_affected_count, Bug.users_unaffected_count, Bug.who_made_private FROM ((SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active, Bug WHERE Bug.id = BugTask.bug AND BugTask.assignee = 100 AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND (Bugtask.product IS NULL OR Product.active = TRUE) AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id )) ) UNION (SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active, Bug, BugSubscription WHERE Bug.id = BugTask.bug AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND Bug.id = BugSubscription.bug AND BugSubscription.person = 100 AND (Bugtask.product IS NULL OR Product.active = TRUE) AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id )) ) UNION (SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active, Bug WHERE Bug.id = BugTask.bug AND BugTask.owner = 100 AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND (Bugtask.product IS NULL OR Product.active = TRUE) AND BugTask.bug = Bug.id AND Bug.owner = 100 AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id )) ) UNION (SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.heat, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache FROM BugTask LEFT JOIN Product ON BugTask.product = Product.id AND Product.active, Bug WHERE Bug.id = BugTask.bug AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND (Bugtask.product IS NULL OR Product.active = TRUE) AND EXISTS (SELECT True FROM BugMessage WHERE Bug.id = BugMessage.bug AND BugMessage.index > 0 AND BugMessage.owner = 100) AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id )) )) AS BugTask JOIN Bug ON BugTask.bug = Bug.id ORDER BY BugTask.importance DESC, BugTask.id LIMIT 51 OFFSET 0; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=894311.31..894311.44 rows=51 width=951) (actual time=9668.701..9668.722 rows=51 loops=1) -> Sort (cost=894311.31..894818.15 rows=202737 width=951) (actual time=9668.699..9668.707 rows=51 loops=1) Sort Key: public.bugtask.importance, public.bugtask.id Sort Method: top-N heapsort Memory: 84kB -> Hash Join (cost=786345.87..887547.57 rows=202737 width=951) (actual time=5453.133..9659.554 rows=2265 loops=1) Hash Cond: (public.bugtask.bug = public.bug.id) -> Unique (cost=550133.36..564324.95 rows=202737 width=280) (actual time=1383.112..1387.952 rows=2265 loops=1) -> Sort (cost=550133.36..550640.20 rows=202737 width=280) (actual time=1383.109..1383.650 rows=2882 loops=1) Sort Key: public.bugtask.assignee, public.bugtask.bug, public.bugtask.bugwatch, public.bugtask.date_assigned, public.bugtask.date_closed, public.bugtask.date_confirmed, public.bugtask.date_fix_committed, public.bugtask.date_fix_released, public.bugtask.date_incomplete, public.bugtask.date_inprogress, public.bugtask.date_left_closed, public.bugtask.date_left_new, public.bugtask.date_triaged, public.bugtask.datecreated, public.bugtask.distribution, public.bugtask.distroseries, public.bugtask.heat, public.bugtask.id, public.bugtask.importance, public.bugtask.milestone, public.bugtask.owner, public.bugtask.product, public.bugtask.productseries, public.bugtask.sourcepackagename, public.bugtask.status, public.bugtask.statusexplanation, public.bugtask.targetnamecache Sort Method: quicksort Memory: 784kB -> Append (cost=3356.88..509690.85 rows=202737 width=280) (actual time=38.599..1370.365 rows=2882 loops=1) -> Nested Loop (cost=3356.88..75606.60 rows=1348 width=280) (actual time=38.598..49.042 rows=20 loops=1) -> Hash Left Join (cost=3356.88..14149.66 rows=1348 width=280) (actual time=38.562..48.088 rows=102 loops=1) Hash Cond: (public.bugtask.product = public.product.id) Filter: ((public.bugtask.product IS NULL) OR public.product.active) -> Bitmap Heap Scan on bugtask (cost=129.96..10904.55 rows=1480 width=280) (actual time=2.320..11.682 rows=102 loops=1) Recheck Cond: (assignee = 100) Filter: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25)) -> Bitmap Index Scan on bugtask__assignee__idx (cost=0.00..129.59 rows=4420 width=0) (actual time=1.428..1.428 rows=4433 loops=1) Index Cond: (assignee = 100) -> Hash (cost=2947.65..2947.65 rows=22342 width=5) (actual time=36.177..36.177 rows=22332 loops=1) -> Seq Scan on product (cost=0.00..2947.65 rows=22342 width=5) (actual time=0.011..28.128 rows=22332 loops=1) Filter: active -> Index Scan using bug_pkey on bug (cost=0.00..45.58 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=102) Index Cond: (public.bug.id = public.bugtask.bug) Filter: ((public.bug.duplicateof IS NULL) AND ((NOT public.bug.private) OR (SubPlan 4))) SubPlan 4 -> Unique (cost=39.33..39.34 rows=2 width=4) (never executed) -> Sort (cost=39.33..39.33 rows=2 width=4) (never executed) Sort Key: public.bugsubscription.bug -> Append (cost=0.00..39.32 rows=2 width=4) (never executed) -> Nested Loop (cost=0.00..26.47 rows=1 width=4) (never executed) -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.25 rows=3 width=8) (never executed) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (never executed) Index Cond: ((public.teamparticipation.team = public.bugsubscription.person) AND (public.teamparticipation.person = 2)) -> Nested Loop (cost=0.00..12.82 rows=1 width=4) (never executed) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.41 rows=1 width=8) (never executed) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (never executed) Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2)) -> Nested Loop (cost=0.00..98346.30 rows=1509 width=280) (actual time=0.085..49.260 rows=610 loops=1) -> Nested Loop Left Join (cost=0.00..29594.39 rows=1509 width=284) (actual time=0.072..40.798 rows=963 loops=1) Filter: ((public.bugtask.product IS NULL) OR public.product.active) -> Nested Loop (cost=0.00..29079.78 rows=1656 width=284) (actual time=0.067..38.540 rows=963 loops=1) -> Index Scan using bugsubscription_person_idx on bugsubscription (cost=0.00..7524.02 rows=3922 width=4) (actual time=0.023..5.819 rows=3997 loops=1) Index Cond: (person = 100) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..5.48 rows=1 width=280) (actual time=0.007..0.007 rows=0 loops=3997) Index Cond: (public.bugtask.bug = public.bugsubscription.bug) 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)) -> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=963) Index Cond: (public.bugtask.product = public.product.id) Filter: public.product.active -> Index Scan using bug_pkey on bug (cost=0.00..45.55 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=963) Index Cond: (public.bug.id = public.bugtask.bug) Filter: ((public.bug.duplicateof IS NULL) AND ((NOT public.bug.private) OR (SubPlan 3))) SubPlan 3 -> Unique (cost=39.33..39.34 rows=2 width=4) (actual time=0.064..0.064 rows=0 loops=20) -> Sort (cost=39.33..39.33 rows=2 width=4) (actual time=0.063..0.063 rows=0 loops=20) Sort Key: public.bugsubscription.bug Sort Method: quicksort Memory: 25kB -> Append (cost=0.00..39.32 rows=2 width=4) (actual time=0.049..0.057 rows=0 loops=20) -> Nested Loop (cost=0.00..26.47 rows=1 width=4) (actual time=0.036..0.043 rows=0 loops=20) -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.25 rows=3 width=8) (actual time=0.006..0.009 rows=6 loops=20) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=122) Index Cond: ((public.teamparticipation.team = public.bugsubscription.person) AND (public.teamparticipation.person = 2)) -> Nested Loop (cost=0.00..12.82 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=20) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.41 rows=1 width=8) (actual time=0.003..0.005 rows=7 loops=20) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=141) Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2)) -> Nested Loop Left Join (cost=0.00..50211.46 rows=720 width=280) (actual time=0.551..12.016 rows=77 loops=1) Filter: ((public.bugtask.product IS NULL) OR public.product.active) -> Nested Loop (cost=0.00..48065.84 rows=790 width=280) (actual time=0.541..11.770 rows=77 loops=1) -> Index Scan using bug_owner_idx on bug (cost=0.00..43164.04 rows=790 width=4) (actual time=0.035..3.840 rows=898 loops=1) Index Cond: (owner = 100) Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 2))) SubPlan 2 -> Unique (cost=39.33..39.34 rows=2 width=4) (actual time=0.035..0.035 rows=0 loops=35) -> Sort (cost=39.33..39.33 rows=2 width=4) (actual time=0.034..0.034 rows=0 loops=35) Sort Key: public.bugsubscription.bug Sort Method: quicksort Memory: 25kB -> Append (cost=0.00..39.32 rows=2 width=4) (actual time=0.028..0.031 rows=0 loops=35) -> Nested Loop (cost=0.00..26.47 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=35) -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.25 rows=3 width=8) (actual time=0.008..0.009 rows=2 loops=35) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=87) Index Cond: ((public.teamparticipation.team = public.bugsubscription.person) AND (public.teamparticipation.person = 2)) -> Nested Loop (cost=0.00..12.82 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=35) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.41 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=35) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=38) Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2)) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.19 rows=1 width=280) (actual time=0.008..0.008 rows=0 loops=898) Index Cond: (public.bugtask.bug = public.bug.id) Filter: ((public.bugtask.owner = 100) 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))) -> Index Scan using product_pkey on product (cost=0.00..2.70 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=77) Index Cond: (public.bugtask.product = public.product.id) Filter: public.product.active -> Hash Join (cost=204717.61..283499.11 rows=199160 width=280) (actual time=362.803..1259.051 rows=2175 loops=1) Hash Cond: (public.bugtask.bug = public.bug.id) -> Hash Left Join (cost=13134.20..90799.93 rows=264510 width=280) (actual time=121.625..852.193 rows=322617 loops=1) Hash Cond: (public.bugtask.product = public.product.id) Filter: ((public.bugtask.product IS NULL) OR public.product.active) -> Bitmap Heap Scan on bugtask (cost=9907.27..84005.95 rows=290364 width=280) (actual time=82.747..339.722 rows=325001 loops=1) Recheck Cond: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25)) -> BitmapOr (cost=9907.27..9907.27 rows=324947 width=0) (actual time=63.085..63.085 rows=0 loops=1) -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..5648.89 rows=193927 width=0) (actual time=36.211..36.211 rows=194026 loops=1) Index Cond: (status = 10) -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..581.77 rows=19911 width=0) (actual time=4.354..4.354 rows=19952 loops=1) Index Cond: (status = 15) -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..1705.49 rows=58540 width=0) (actual time=11.408..11.408 rows=58511 loops=1) Index Cond: (status = 20) -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..921.04 rows=31547 width=0) (actual time=6.584..6.584 rows=31465 loops=1) Index Cond: (status = 21) -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..161.71 rows=5503 width=0) (actual time=1.272..1.272 rows=5521 loops=1) Index Cond: (status = 22) -> Bitmap Index Scan on bugtask__status__idx (cost=0.00..452.83 rows=15519 width=0) (actual time=3.250..3.250 rows=15526 loops=1) Index Cond: (status = 25) -> Hash (cost=2947.65..2947.65 rows=22342 width=5) (actual time=38.798..38.798 rows=22332 loops=1) -> Seq Scan on product (cost=0.00..2947.65 rows=22342 width=5) (actual time=0.018..30.488 rows=22332 loops=1) Filter: active -> Hash (cost=191247.28..191247.28 rows=26891 width=8) (actual time=240.897..240.897 rows=14206 loops=1) -> Nested Loop (cost=25633.19..191247.28 rows=26891 width=8) (actual time=78.235..233.560 rows=14206 loops=1) -> HashAggregate (cost=25633.19..25671.24 rows=3805 width=4) (actual time=78.199..85.932 rows=15376 loops=1) -> Bitmap Heap Scan on bugmessage (cost=618.37..25565.96 rows=26891 width=4) (actual time=11.041..62.797 rows=33030 loops=1) Recheck Cond: ((owner = 100) AND (index > 0)) -> Bitmap Index Scan on bugmessage__owner__index__idx (cost=0.00..611.65 rows=26891 width=0) (actual time=7.257..7.257 rows=33030 loops=1) Index Cond: ((owner = 100) AND (index > 0)) -> Index Scan using bug_pkey on bug (cost=0.00..43.50 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=15376) Index Cond: (public.bug.id = bugmessage.bug) Filter: ((public.bug.duplicateof IS NULL) AND ((NOT public.bug.private) OR (SubPlan 1))) SubPlan 1 -> Unique (cost=39.33..39.34 rows=2 width=4) (actual time=0.051..0.051 rows=0 loops=323) -> Sort (cost=39.33..39.33 rows=2 width=4) (actual time=0.050..0.050 rows=0 loops=323) Sort Key: public.bugsubscription.bug Sort Method: quicksort Memory: 25kB -> Append (cost=0.00..39.32 rows=2 width=4) (actual time=0.039..0.047 rows=0 loops=323) -> Nested Loop (cost=0.00..26.47 rows=1 width=4) (actual time=0.028..0.031 rows=0 loops=323) -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.25 rows=3 width=8) (actual time=0.009..0.012 rows=3 loops=323) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1072) Index Cond: ((public.teamparticipation.team = public.bugsubscription.person) AND (public.teamparticipation.person = 2)) -> Nested Loop (cost=0.00..12.82 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=323) -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.41 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=323) Index Cond: (bug = $0) -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.40 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=383) Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2)) -> Hash (cost=157509.34..157509.34 rows=740734 width=743) (actual time=3984.328..3984.328 rows=740734 loops=1) -> Seq Scan on bug (cost=0.00..157509.34 rows=740734 width=743) (actual time=0.036..1481.110 rows=740734 loops=1) Total runtime: 9669.648 ms