Summary
=======
Determining which bugs a person commented on is very expensive due to querying across 3 multi-million-row tables. This most often shows up on the +bugs page for persons that comment on a high proportion of bugs.
Workaround
==========
None available. Retrying may work.
Status
======
A schema change to permit more efficient queries has been made. After the next db downtime the page queries need tuning to take advantage of this change. The feature flag malone.bugmessager_owner controls the use of the new schema (but setting that flag may not be enough on its own).
Details
=======
20 SELECT COUNT(*) FROM ((SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assig ... D BugMessage.index > $INT ) AND Bug.private = FALSE)) AS BugTask JOIN Bug ON BugTask.bug = Bug.id:
GET: 20 Robots: 7 Local: 0
5 https://bugs.launchpad.net/%7Epitti/+bugs (Person:+bugs)
OOPS-1921A1382, OOPS-1921C413, OOPS-1921C637, OOPS-1921J566, OOPS-1921N401
4 https://bugs.launchpad.net/%7Eapport/+bugs (Person:+bugs)
OOPS-1921J1015, OOPS-1921J1118, OOPS-1921J1300, OOPS-1921K931
3 https://bugs.launchpad.net/%7Epvillavi/+bugs (Person:+bugs)
OOPS-1921A1224, OOPS-1921C652, OOPS-1921K1381
[7 other URLs]
(from N401)
8 second query:
explain analyze SELECT COUNT(*)
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.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)
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.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)
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.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)
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.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 BugTask.id IN
(SELECT DISTINCT BugTask.id
FROM BugTask, BugMessage, Message
WHERE Message.OWNER = 100
AND Message.id = BugMessage.message
AND BugTask.bug = BugMessage.bug
AND BugMessage.INDEX > 0)
AND Bug.private = FALSE)) AS BugTask
JOIN Bug ON BugTask.bug = Bug.id;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=467408.94..467408.95 rows=1 width=0) (actual time=62118.581..62118.581 rows=1 loops=1)
-> Nested Loop (cost=447450.90..467399.75 rows=3677 width=0) (actual time=62089.979..62117.834 rows=2119 loops=1)
-> HashAggregate (cost=447450.90..447487.67 rows=3677 width=276) (actual time=62089.957..62094.172 rows=2119 loops=1)
-> Append (cost=3162.15..447211.89 rows=3677 width=276) (actual time=848.743..62067.289 rows=2718 loops=1)
-> Nested Loop (cost=3162.15..21506.62 rows=1313 width=276) (actual time=848.741..3473.458 rows=31 loops=1)
-> Hash Left Join (cost=3162.15..13733.22 rows=1313 width=276) (actual time=378.237..3466.088 rows=112 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=117.30..10670.74 rows=1440 width=276) (actual time=320.456..3407.221 rows=112 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..116.94 rows=4337 width=0) (actual time=122.220..122.220 rows=4323 loops=1)
Index Cond: (assignee = 100)
-> Hash (cost=2771.72..2771.72 rows=21851 width=5) (actual time=57.712..57.712 rows=21733 loops=1)
-> Seq Scan on product (cost=0.00..2771.72 rows=21851 width=5) (actual time=0.027..47.756 rows=21733 loops=1)
Filter: active
-> Index Scan using bug_pkey on bug (cost=0.00..5.91 rows=1 width=4) (actual time=0.059..0.060 rows=0 loops=112)
Index Cond: (public.bug.id = public.bugtask.bug)
Filter: ((public.bug.duplicateof IS NULL) AND (NOT public.bug.private))
-> Nested Loop (cost=63.95..37702.23 rows=1499 width=276) (actual time=90.524..15438.651 rows=582 loops=1)
-> Nested Loop Left Join (cost=63.95..28913.97 rows=1499 width=280) (actual time=90.431..15403.304 rows=918 loops=1)
Filter: ((public.bugtask.product IS NULL) OR public.product.active)
-> Nested Loop (cost=63.95..28403.88 rows=1644 width=280) (actual time=90.416..15333.718 rows=918 loops=1)
-> Bitmap Heap Scan on bugsubscription (cost=63.95..6725.08 rows=3944 width=4) (actual time=26.524..1481.745 rows=3922 loops=1)
Recheck Cond: (person = 100)
-> Bitmap Index Scan on bugsubscription_person_idx (cost=0.00..62.97 rows=3944 width=0) (actual time=25.478..25.478 rows=3922 loops=1)
Index Cond: (person = 100)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..5.48 rows=1 width=276) (actual time=3.269..3.530 rows=0 loops=3922)
Index Cond: (public.bugtask.bug = 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.072..0.072 rows=0 loops=918)
Index Cond: (public.bugtask.product = public.product.id)
Filter: public.product.active
-> Index Scan using bug_pkey on bug (cost=0.00..5.85 rows=1 width=4) (actual time=0.035..0.036 rows=1 loops=918)
Index Cond: (public.bug.id = public.bugtask.bug)
Filter: ((public.bug.duplicateof IS NULL) AND (NOT public.bug.private))
-> Nested Loop Left Join (cost=32.10..9565.86 rows=683 width=276) (actual time=62.144..90.007 rows=73 loops=1)
Filter: ((public.bugtask.product IS NULL) OR public.product.active)
-> Nested Loop (cost=32.10..7541.03 rows=749 width=276) (actual time=62.137..89.518 rows=73 loops=1)
-> Bitmap Heap Scan on bug (cost=32.10..2881.42 rows=749 width=4) (actual time=62.083..74.974 rows=875 loops=1)
Recheck Cond: (owner = 100)
Filter: ((duplicateof IS NULL) AND (NOT private))
-> Bitmap Index Scan on bug_owner_idx (cost=0.00..31.91 rows=1001 width=0) (actual time=61.730..61.730 rows=1023 loops=1)
Index Cond: (owner = 100)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.21 rows=1 width=276) (actual time=0.015..0.016 rows=0 loops=875)
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.69 rows=1 width=5) (actual time=0.004..0.005 rows=0 loops=73)
Index Cond: (public.bugtask.product = public.product.id)
Filter: public.product.active
-> Nested Loop (cost=305503.56..378400.41 rows=182 width=276) (actual time=38760.014..43063.254 rows=2032 loops=1)
-> Nested Loop Left Join (cost=305503.56..378026.51 rows=182 width=276) (actual time=38626.835..42983.983 rows=2722 loops=1)
Filter: ((public.bugtask.product IS NULL) OR public.product.active)
-> Hash Semi Join (cost=305503.56..377964.46 rows=200 width=276) (actual time=38626.821..42957.350 rows=2728 loops=1)
Hash Cond: (public.bugtask.id = public.bugtask.id)
-> Bitmap Heap Scan on bugtask (cost=8513.27..80064.62 rows=276215 width=276) (actual time=182.746..4598.998 rows=308733 loops=1)
Recheck Cond: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
-> BitmapOr (cost=8513.27..8513.27 rows=308734 width=0) (actual time=156.137..156.137 rows=0 loops=1)
-> Bitmap Index Scan on bugtask__status__idx (cost=0.00..4829.87 rows=184328 width=0) (actual time=103.983..103.983 rows=184491 loops=1)
Index Cond: (status = 10)
-> Bitmap Index Scan on bugtask__status__idx (cost=0.00..466.01 rows=17680 width=0) (actual time=8.011..8.011 rows=17642 loops=1)
Index Cond: (status = 15)
-> Bitmap Index Scan on bugtask__status__idx (cost=0.00..1479.89 rows=56464 width=0) (actual time=22.718..22.718 rows=56538 loops=1)
Index Cond: (status = 20)
-> Bitmap Index Scan on bugtask__status__idx (cost=0.00..794.52 rows=30282 width=0) (actual time=12.404..12.404 rows=30304 loops=1)
Index Cond: (status = 21)
-> Bitmap Index Scan on bugtask__status__idx (cost=0.00..142.56 rows=5354 width=0) (actual time=2.719..2.719 rows=5351 loops=1)
Index Cond: (status = 22)
-> Bitmap Index Scan on bugtask__status__idx (cost=0.00..386.11 rows=14627 width=0) (actual time=6.288..6.288 rows=14653 loops=1)
Index Cond: (status = 25)
-> Hash (cost=295746.16..295746.16 rows=99531 width=4) (actual time=38118.466..38118.466 rows=29835 loops=1)
-> HashAggregate (cost=293755.54..294750.85 rows=99531 width=4) (actual time=38081.451..38099.894 rows=29835 loops=1)
-> Nested Loop (cost=142973.14..293506.71 rows=99531 width=4) (actual time=15019.216..37979.238 rows=84427 loops=1)
-> Hash Join (cost=142973.14..256158.69 rows=75294 width=4) (actual time=15019.177..23263.791 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=7.042..3248.033 rows=3026226 loops=1)
Filter: (index > 0)
-> Hash (cost=142031.96..142031.96 rows=75294 width=4) (actual time=15011.968..15011.968 rows=75175 loops=1)
-> Bitmap Heap Scan on message (cost=1205.51..142031.96 rows=75294 width=4) (actual time=129.011..14917.650 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=115.022..115.022 rows=75175 loops=1)
Index Cond: (owner = 100)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.48 rows=1 width=8) (actual time=0.272..0.455 rows=3 loops=32134)
Index Cond: (public.bugtask.bug = bugmessage.bug)
-> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=5) (actual time=0.007..0.007 rows=0 loops=2728)
Index Cond: (public.bugtask.product = public.product.id)
Filter: public.product.active
-> Index Scan using bug_pkey on bug (cost=0.00..2.04 rows=1 width=4) (actual time=0.027..0.027 rows=1 loops=2722)
Index Cond: (public.bug.id = public.bugtask.bug)
Filter: ((public.bug.duplicateof IS NULL) AND (NOT public.bug.private))
-> Index Scan using bug_pkey on bug (cost=0.00..5.39 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=2119)
Index Cond: (public.bug.id = public.bugtask.bug)
Total runtime: 62125.406 ms
(93 rows)
runtime hot is:
Aggregate (cost=467408.94..467408.95 rows=1 width=0) (actual time=7239.114..7239.114 rows=1 loops=1)
Fixed in stable r10739 <http:// bazaar. launchpad. net/~launchpad- pqm/launchpad/ stable/ revision/ 10739>