https://bugs.launchpad.net/launchpad/+bugs?search=Search&field.affects_me=on&orderby=-date_last_updated
for instance, times out with eg OOPS-2103AP45
it was working ok this morning (utc+11) but is worse now launchpad's in a higher-load phase.
when I tested this as part of bug 858618 it was also showing variable and somewhat high query times, which led to me not showing the count on the overview page. this url already existed so it's not a new regression due to my work, but it is more visible.
example statement:
LaunchpadTimeoutError: Statement: '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.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 BugTask JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug AND BugTask.distribution = 1 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 \n BugTask.id IN (\n SELECT BugTask.id FROM BugTask, BugAffectsPerson\n WHERE BugTask.bug = BugAffectsPerson.bug\n AND BugAffectsPerson.person = 67034\n AND BugAffectsPerson.affected = TRUE\n )\n AND \n (Bug.private = FALSE OR EXISTS (\n SELECT BugSubscription.bug\n FROM BugSubscription, TeamParticipation\n WHERE TeamParticipation.person = 67034 AND\n TeamParticipation.team = BugSubscription.person AND\n BugSubscription.bug = Bug.id\n UNION\n SELECT BugTask.bug\n FROM BugTask, TeamParticipation\n WHERE TeamParticipation.person = 67034 AND\n TeamParticipation.team = BugTask.assignee AND\n BugTask.bug = Bug.id\n \n UNION\n SELECT BugTask.bug\n FROM BugTask, TeamParticipation, Product\n WHERE TeamParticipation.person = 67034 AND\n TeamParticipation.team = Product.owner AND\n BugTask.product = Product.id AND\n BugTask.bug = Bug.id AND\n Bug.security_related IS False\n UNION\n SELECT BugTask.bug\n FROM BugTask, TeamParticipation, ProductSeries\n WHERE TeamParticipation.person = 67034 AND\n TeamParticipation.team = ProductSeries.owner AND\n BugTask.productseries = ProductSeries.id AND\n BugTask.bug = Bug.id AND\n Bug.security_related IS False\n UNION\n SELECT BugTask.bug\n FROM BugTask, TeamParticipation, Distribution\n WHERE TeamParticipation.person = 67034 AND\n TeamParticipation.team = Distribution.owner AND\n BugTask.distribution = Distribution.id AND\n BugTask.bug = Bug.id AND\n Bug.security_related IS False\n UNION\n SELECT BugTask.bug\n FROM BugTask, TeamParticipation, DistroSeries, Distribution\n WHERE TeamParticipation.person = 67034 AND\n TeamParticipation.team = Distribution.owner AND\n DistroSeries.distribution = Distribution.id AND\n BugTask.distroseries = DistroSeries.id AND\n BugTask.bug = Bug.id AND\n Bug.security_related IS False\n \n ))\n ORDER BY Bug.date_last_updated DESC LIMIT 76 OFFSET 0'<br /> Parameters:()<br /> Original error: QueryCanceledError('canceling statement due to statement timeout\n',)<br />
launchpad_prod_3=# explain analyze SELECT BugTask.*, Bug.* n.bug n.person = 78 n.affected = TRUE) last_updated DESC LIMIT 76 OFFSET 0;
QUERY PLAN ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --- 09..7546. 34 rows=76 width=1400) (actual time=155. 107..9665. 562 rows=58 loops=1) 09..660246. 29 rows=8940 width=1400) (actual time=155. 106..9665. 506 rows=58 loops=1) 00..556112. 00 rows=8940 width=1400) (actual time=65. 912..8744. 850 rows=5772 loops=1) last_updated_ _idx on bug (cost=0. 00..263671. 56 rows=677083 width=1116) (actual time=0. 146..5893. 546 rows=677338 loops=1)
Filter: (duplicateof IS NULL) _product_ _bug__key on bugtask (cost=0.00..0.42 rows=1 width=284) (actual time=0.004..0.004 rows=0 loops=677338)
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)) 09..1955. 17 rows=508 width=4) (actual time=0.003..0.065 rows=480 loops=5772)
-> Bitmap Heap Scan on bugaffectsperson (cost=6.70..571.40 rows=357 width=4) (actual time=14.601..23.301 rows=345 loops=1)
Recheck Cond: (person = 78)
Filter: affected
- > Bitmap Index Scan on bugaffectsperso n__person_ _idx (cost=0.00..6.62 rows=361 width=0) (actual time=14.410..14.410 rows=346 loops=1)
...
launchpad_prod_3-# FROM BugTask JOIN Bug ON BugTask.bug = Bug.id
launchpad_prod_3-# WHERE Bug.id = BugTask.bug
launchpad_prod_3-# AND BugTask.product = 10294
launchpad_prod_3-# AND (
launchpad_prod_3(# (BugTask.status = 10)
launchpad_prod_3(# OR (BugTask.status = 15) OR (BugTask.status = 20)
launchpad_prod_3(# OR (BugTask.status = 21) OR (BugTask.status = 22)
launchpad_prod_3(# OR (BugTask.status = 25))
launchpad_prod_3-# AND Bug.duplicateof is NULL AND
launchpad_prod_3-# BugTask.id IN (
launchpad_prod_3(# SELECT BugTask.id FROM BugTask, BugAffectsPerson
launchpad_prod_3(# WHERE
launchpad_prod_3(# BugTask.bug = BugAffectsPerso
launchpad_prod_3(# AND BugAffectsPerso
launchpad_prod_3(# AND BugAffectsPerso
launchpad_prod_3-# ORDER BY Bug.date_
-------
Limit (cost=1950.
-> Nested Loop Semi Join (cost=1950.
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.
-> Index Scan Backward using bug__date_
-> Index Scan using bugtask_
-> Materialize (cost=1950.
-> Nested Loop (cost=6.70..1949.58 rows=508 width=4) (actual time=14.713..42.650 rows=482 loops=1)