| 2011-11-20 17:46:31 |
Dave Walker |
bug |
|
|
added bug |
| 2011-11-20 17:49:10 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaiging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
To reproduce, please:
Follow the link "Step 1", https://wiki.ubuntu.com/ServerTeam/KnowledgeBase#Bug_Triager
This is really very critical for our incoming bug workflow, and blocking.
Thanks. |
As part of the Ubuntu server teams bug triaiging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Thanks. |
|
| 2011-11-20 17:49:42 |
Robert Collins |
launchpad: status |
New |
Triaged |
|
| 2011-11-20 17:49:44 |
Robert Collins |
launchpad: importance |
Undecided |
Critical |
|
| 2011-11-20 17:50:24 |
Robert Collins |
summary |
pre-canned bug search url has started OOPs'ing |
Timeout on Distribution:+bugs |
|
| 2011-11-20 18:00:03 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaiging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Thanks. |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query:
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 2 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, Product WHERE Product.owner IN (SELECT team FROM teams) AND BugTask.product = Product.id AND BugTask.bug = Bug.id AND Bug.security_related IS False UNION SELECT BugTask.bug FROM BugTask, ProductSeries WHERE ProductSeries.owner IN (SELECT team FROM teams) AND BugTask.productseries = ProductSeries.id AND BugTask.bug = Bug.id AND Bug.security_related IS False UNION SELECT BugTask.bug FROM BugTask, Distribution WHERE Distribution.owner IN (SELECT team FROM teams) AND BugTask.distribution = Distribution.id AND BugTask.bug = Bug.id AND Bug.security_related IS False UNION SELECT BugTask.bug FROM BugTask, DistroSeries, Distribution WHERE Distribution.owner IN (SELECT team FROM teams) AND DistroSeries.distribution = Distribution.id AND BugTask.distroseries = DistroSeries.id AND BugTask.bug = Bug.id AND Bug.security_related IS False )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0; |
|
| 2011-11-20 18:05:39 |
Robert Collins |
tags |
|
regression timeout |
|
| 2011-11-20 19:48:22 |
Robert Collins |
tags |
regression timeout |
timeout |
|
| 2011-11-20 20:07:38 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query:
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 2 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, Product WHERE Product.owner IN (SELECT team FROM teams) AND BugTask.product = Product.id AND BugTask.bug = Bug.id AND Bug.security_related IS False UNION SELECT BugTask.bug FROM BugTask, ProductSeries WHERE ProductSeries.owner IN (SELECT team FROM teams) AND BugTask.productseries = ProductSeries.id AND BugTask.bug = Bug.id AND Bug.security_related IS False UNION SELECT BugTask.bug FROM BugTask, Distribution WHERE Distribution.owner IN (SELECT team FROM teams) AND BugTask.distribution = Distribution.id AND BugTask.bug = Bug.id AND Bug.security_related IS False UNION SELECT BugTask.bug FROM BugTask, DistroSeries, Distribution WHERE Distribution.owner IN (SELECT team FROM teams) AND DistroSeries.distribution = Distribution.id AND BugTask.distroseries = DistroSeries.id AND BugTask.bug = Bug.id AND Bug.security_related IS False )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0; |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds):
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms |
|
| 2011-11-20 20:08:11 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds):
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds):
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast. |
|
| 2011-11-20 21:11:55 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds):
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast. |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds) caused by a combination of bad estimation (pg things 26K rows will be returned) and the query describing duplicate work - the bug supervisor check is filtering on task id, which is precisely what the other main filter does; lifting that work up to provide clauses on product, distribution and distribution+sourcepackagename triggers a better plan. (See comment 11).
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast. |
|
| 2011-11-20 22:57:22 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds) caused by a combination of bad estimation (pg things 26K rows will be returned) and the query describing duplicate work - the bug supervisor check is filtering on task id, which is precisely what the other main filter does; lifting that work up to provide clauses on product, distribution and distribution+sourcepackagename triggers a better plan. (See comment 11).
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast. |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds) caused by a combination of bad estimation (pg things 26K rows will be returned) and the query describing duplicate work - the bug supervisor check is filtering on task id, which is precisely what the other main filter does; lifting that work up to provide clauses on product, distribution and distribution+sourcepackagename triggers a better plan. (See comment 11).
Alternative fix from #postgresql is to change the sort to 'datecreated + interval '0'' which runs this query otherwise unmodified in 300ms on qastaging.
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast. |
|
| 2011-11-28 11:46:23 |
Julian Edwards |
launchpad: assignee |
|
Launchpad Red Squad (redsquad) |
|
| 2011-11-28 15:09:27 |
Raphaël Badin |
launchpad: assignee |
Launchpad Red Squad (redsquad) |
Raphaël Badin (rvb) |
|
| 2011-11-28 15:09:29 |
Raphaël Badin |
launchpad: status |
Triaged |
In Progress |
|
| 2011-11-28 15:55:13 |
Raphaël Badin |
branch linked |
|
lp:~rvb/launchpad/bugs-timeout-bug-892820 |
|
| 2011-11-28 22:09:50 |
Francis J. Lacoste |
tags |
timeout |
escalated timeout |
|
| 2011-11-29 22:41:33 |
Launchpad QA Bot |
tags |
escalated timeout |
escalated qa-needstesting timeout |
|
| 2011-11-29 22:41:34 |
Launchpad QA Bot |
launchpad: status |
In Progress |
Fix Committed |
|
| 2011-11-29 23:00:27 |
Raphaël Badin |
tags |
escalated qa-needstesting timeout |
escalated qa-ok timeout |
|
| 2011-12-01 09:27:21 |
Raphaël Badin |
launchpad: status |
Fix Committed |
Fix Released |
|
| 2011-12-08 08:58:58 |
Robert Collins |
launchpad: status |
Fix Released |
In Progress |
|
| 2011-12-09 18:16:26 |
Robert Collins |
summary |
Timeout on Distribution:+bugs |
Timeout on Distribution:+bugs searching by bug supervisor |
|
| 2011-12-09 18:30:21 |
Robert Collins |
launchpad: status |
In Progress |
Fix Released |
|
| 2011-12-09 18:31:22 |
Robert Collins |
description |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds) caused by a combination of bad estimation (pg things 26K rows will be returned) and the query describing duplicate work - the bug supervisor check is filtering on task id, which is precisely what the other main filter does; lifting that work up to provide clauses on product, distribution and distribution+sourcepackagename triggers a better plan. (See comment 11).
Alternative fix from #postgresql is to change the sort to 'datecreated + interval '0'' which runs this query otherwise unmodified in 300ms on qastaging.
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast. |
As part of the Ubuntu server teams bug triaging process, we have a series of pre-canned URL's. Since Friday (18/11/11), this has started OOPs'ing.
An example is, OOPS-c0981f059156b3ed4108c4c92958879c
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=&orderby=datecreated&field.status%3Alist=NEW&field.importance%3Alist=UNDECIDED&assignee_option=any&field.assignee=&field.bug_reporter=&field.bug_supervisor=ubuntu-server&field.bug_commenter=&field.subscriber=&field.component-empty-marker=1&field.status_upstream-empty-marker=1&field.omit_dupes.used=&field.omit_dupes=on&field.has_patch.used=&field.has_cve.used=&field.affects_me.used=&field.tag=-notserv&field.tags_combinator=ANY&field.has_no_package.used=&search=Search
This is really very critical for our incoming bug workflow, and blocking.
Analysis
========
Slow query (8.8 seconds) caused by a combination of bad estimation (pg things 26K rows will be returned) and the query describing duplicate work - the bug supervisor check is filtering on task id, which is precisely what the other main filter does; lifting that work up to provide clauses on product, distribution and distribution+sourcepackagename triggers a better plan. (See comment 11).
Alternative fix from #postgresql is to change the sort to 'datecreated + interval '0'' which runs this query otherwise unmodified in 300ms on qastaging.
SELECT BugTask.status, 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.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.importance IN (5) AND BugTask.distribution = 1 AND (BugTask.status IN (10)) AND Bug.duplicateof is NULL AND NOT EXISTS (SELECT TRUE FROM BugTag WHERE BugTag.bug = Bug.id AND BugTag.tag = 'notserv') AND BugTask.id IN ( SELECT BugTask.id FROM BugTask, Product WHERE BugTask.product = Product.id AND Product.bug_supervisor = 343381 UNION ALL SELECT BugTask.id FROM BugTask, StructuralSubscription WHERE BugTask.distribution = StructuralSubscription.distribution AND BugTask.sourcepackagename = StructuralSubscription.sourcepackagename AND StructuralSubscription.subscriber = 343381 UNION ALL SELECT BugTask.id FROM BugTask, Distribution WHERE BugTask.distribution = Distribution.id AND Distribution.bug_supervisor = 343381 ) AND (Bug.private = FALSE OR EXISTS ( WITH teams AS ( SELECT team from TeamParticipation WHERE person = 1042386 ) SELECT BugSubscription.bug FROM BugSubscription WHERE BugSubscription.person IN (SELECT team FROM teams) AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask WHERE BugTask.assignee IN (SELECT team FROM teams) AND BugTask.bug = Bug.id )) ORDER BY BugTask.datecreated LIMIT 76 OFFSET 0;
Plan:
Limit (cost=16707.17..140839.63 rows=76 width=921) (actual time=177865.846..179420.653 rows=5 loops=1)
-> Nested Loop Anti Join (cost=16707.17..45547185.77 rows=27876 width=921) (actual time=177865.845..179420.646 rows=5 loops=1)
-> Nested Loop Semi Join (cost=16707.17..45536237.50 rows=27876 width=921) (actual time=177865.785..179420.434 rows=5 loops=1)
Join Filter: (public.bugtask.id = public.bugtask.id)
-> Nested Loop (cost=0.00..15171891.61 rows=55751 width=921) (actual time=458.705..6943.130 rows=44267 loops=1)
-> Index Scan using bugtask__datecreated__idx on bugtask (cost=0.00..99933.00 rows=73926 width=161) (actual time=264.252..4141.236 rows=152189 loops=1)
Filter: ((importance = 5) AND (distribution = 1) AND (status = 10))
-> Index Scan using bug_pkey on bug (cost=0.00..203.87 rows=1 width=760) (actual time=0.017..0.017 rows=0 loops=152189)
Index Cond: (bug.id = public.bugtask.bug)
Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 2)))
SubPlan 2
-> Unique (cost=201.93..201.94 rows=2 width=4) (actual time=0.202..0.202 rows=1 loops=3131)
CTE teams
-> Index Scan using teamparticipation_person_idx on teamparticipation (cost=0.00..184.67 rows=137 width=4) (actual time=0.069..0.591 rows=157 loops=1)
Index Cond: (person = 1042386)
-> Sort (cost=17.27..17.27 rows=2 width=4) (actual time=0.197..0.197 rows=1 loops=3131)
Sort Key: bugsubscription.bug
Sort Method: quicksort Memory: 25kB
-> Append (cost=4.45..17.26 rows=2 width=4) (actual time=0.108..0.183 rows=1 loops=3131)
-> Hash Semi Join (cost=4.45..8.85 rows=1 width=4) (actual time=0.084..0.092 rows=1 loops=3131)
Hash Cond: (bugsubscription.person = teams.team)
-> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..4.39 rows=3 width=8) (actual time=0.065..0.084 rows=4 loops=3131)
Index Cond: (bug = $1)
-> Hash (cost=2.74..2.74 rows=137 width=4) (actual time=0.726..0.726 rows=157 loops=1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.072..0.661 rows=157 loops=1)
-> Nested Loop Semi Join (cost=0.00..8.39 rows=1 width=4) (actual time=0.087..0.087 rows=0 loops=3131)
Join Filter: (public.bugtask.assignee = teams.team)
-> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..3.94 rows=1 width=8) (actual time=0.021..0.022 rows=1 loops=3131)
Index Cond: (bug = $1)
-> CTE Scan on teams (cost=0.00..2.74 rows=137 width=4) (actual time=0.001..0.031 rows=157 loops=3260)
-> Materialize (cost=16707.17..16949.10 rows=24193 width=4) (actual time=0.000..1.646 rows=12164 loops=44267)
-> Append (cost=2.48..16682.98 rows=24193 width=4) (actual time=0.190..46.945 rows=12165 loops=1)
-> Nested Loop (cost=2.48..99.60 rows=11 width=4) (actual time=0.037..0.037 rows=0 loops=1)
-> Index Scan using product__bug_supervisor__idx on product (cost=0.00..3.87 rows=1 width=4) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Bitmap Heap Scan on bugtask (cost=2.48..95.09 rows=52 width=8) (never executed)
Recheck Cond: (public.bugtask.product = product.id)
-> Bitmap Index Scan on bugtask__product__bug__key (cost=0.00..2.47 rows=52 width=0) (never executed)
Index Cond: (public.bugtask.product = product.id)
-> Nested Loop (cost=3.50..922.99 rows=6651 width=4) (actual time=0.151..44.760 rows=12165 loops=1)
-> Bitmap Heap Scan on structuralsubscription (cost=3.50..185.88 rows=187 width=8) (actual time=0.094..0.539 rows=187 loops=1)
Recheck Cond: (subscriber = 343381)
-> Bitmap Index Scan on structuralsubscription__subscriber__idx (cost=0.00..3.45 rows=187 width=0) (actual time=0.071..0.071 rows=188 loops=1)
Index Cond: (subscriber = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..3.93 rows=1 width=12) (actual time=0.015..0.222 rows=65 loops=187)
Index Cond: ((public.bugtask.distribution = structuralsubscription.distribution) AND (public.bugtask.sourcepackagename = structuralsubscription.sourcepackagename))
-> Nested Loop (cost=0.00..15418.46 rows=17531 width=4) (actual time=0.032..0.032 rows=0 loops=1)
-> Index Scan using distribution__bug_supervisor__idx on distribution (cost=0.00..3.87 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=1)
Index Cond: (bug_supervisor = 343381)
-> Index Scan using bugtask__distribution_sourcepackage__heat__idx on bugtask (cost=0.00..15168.06 rows=19723 width=8) (never executed)
Index Cond: (public.bugtask.distribution = distribution.id)
-> Index Scan using bugtag__bug__idx on bugtag (cost=0.00..0.39 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=5)
Index Cond: (bugtag.bug = bug.id)
Filter: (bugtag.tag = 'notserv'::text)
Total runtime: 179421.810 ms
Workarounds
===========
* Run the query as an anonymous user: the plan for just-public bugs is good and it runs fast.
* Leave the new bug listing beta : the overhead of the performance bugs in the bug listings work is enough to break this. |
|
