Launchpad itself

Distribution:+bugs-index time outs

Bug #618406 reported by Robert Collins on 2010-08-15

This bug affects 3 people

Affects		Status	Importance	Assigned to	Milestone
	Launchpad itself	Fix Released	Critical	Robert Collins	Launchpad itself 11.04

Bug Description

OOPS-1839G1380
https://bugs.launchpad.net/ubuntu/+bugs-index

11. 207 14060ms SQL-launchpad-main-slave
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,
                      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 Bug.private = FALSE
ORDER BY Bug.heat DESC, BugTask.id LIMIT 40
OFFSET 0

See original description

Tags:

Related branches

lp:~lifeless/launchpad/bug-618406

Merged into lp:launchpad/db-devel at revision 10374

Jonathan Lange (community): Abstain on 2011-03-31

Stuart Bishop (community): Approve on 2011-03-31

Rejected for merging into lp:launchpad

Launchpad code reviewers: Pending requested 2011-03-29

Diff: 5399 lines (+2369/-742)

98 files modified

configs/development/launchpad-lazr.conf (+0/-4)
configs/testrunner/launchpad-lazr.conf (+0/-3)
database/sampledata/current-dev.sql (+149/-113)
database/sampledata/current.sql (+144/-109)
database/schema/comments.sql (+4/-3)
database/schema/launchpad_session.sql (+25/-0)
database/schema/patch-2208-51-0.sql (+18/-0)
database/schema/patch-2208-52-0.sql (+14/-0)
database/schema/patch-2208-53-0.sql (+20/-0)
database/schema/patch-2208-54-0.sql (+6/-0)
database/schema/patch-2208-55-0.sql (+24/-0)
database/schema/patch-2208-56-0.sql (+33/-0)
database/schema/patch-2208-57-1.sql (+14/-0)
database/schema/patch-2208-59-0.sql (+38/-0)
database/schema/security.cfg (+6/-0)
database/schema/security.py (+182/-99)
database/schema/trusted.sql (+14/-0)
lib/canonical/config/schema-lazr.conf (+5/-8)
lib/canonical/launchpad/database/message.py (+2/-0)
lib/canonical/launchpad/interfaces/message.py (+2/-0)
lib/canonical/launchpad/pagetests/basics/notfound-traversals.txt (+1/-1)
lib/canonical/launchpad/pagetests/webservice/xx-hide-comments.txt (+2/-2)
lib/canonical/launchpad/security.py (+23/-8)
lib/canonical/launchpad/zcml/message.zcml (+4/-3)
lib/lp/app/stories/launchpad-root/site-search.txt (+5/-3)
lib/lp/archivepublisher/config.py (+8/-7)
lib/lp/archivepublisher/deathrow.py (+2/-7)
lib/lp/archivepublisher/interfaces/publisherconfig.py (+58/-0)
lib/lp/archivepublisher/model/publisherconfig.py (+68/-0)
lib/lp/archivepublisher/publishing.py (+1/-6)
lib/lp/archivepublisher/tests/test_publisherconfig.py (+92/-0)
lib/lp/archivepublisher/zcml/configure.zcml (+25/-1)
lib/lp/bugs/browser/bugcomment.py (+1/-1)
lib/lp/bugs/configure.zcml (+5/-1)
lib/lp/bugs/doc/bugmessage-visibility.txt (+9/-8)
lib/lp/bugs/doc/bugnotification-sending.txt (+9/-3)
lib/lp/bugs/enum.py (+4/-4)
lib/lp/bugs/interfaces/bug.py (+1/-1)
lib/lp/bugs/interfaces/bugmessage.py (+0/-2)
lib/lp/bugs/mail/bugnotificationrecipients.py (+11/-0)
lib/lp/bugs/model/bug.py (+1/-1)
lib/lp/bugs/model/bugmessage.py (+0/-1)
lib/lp/bugs/model/bugnotification.py (+11/-0)
lib/lp/bugs/model/structuralsubscription.py (+4/-3)
lib/lp/bugs/security.py (+2/-7)
lib/lp/bugs/stories/bugs/xx-bug-hidden-comments.txt (+1/-1)
lib/lp/bugs/tests/test_bug_messages_webservice.py (+1/-1)
lib/lp/code/model/tests/test_recipebuilder.py (+2/-2)
lib/lp/registry/adapters.py (+10/-0)
lib/lp/registry/browser/configure.zcml (+8/-8)
lib/lp/registry/browser/distribution.py (+75/-2)
lib/lp/registry/browser/distroseries.py (+70/-28)
lib/lp/registry/browser/tests/test_distribution_views.py (+167/-0)
lib/lp/registry/browser/tests/test_series_views.py (+127/-8)
lib/lp/registry/configure.zcml (+8/-1)
lib/lp/registry/doc/distroseries.txt (+8/-4)
lib/lp/registry/interfaces/distribution.py (+7/-2)
lib/lp/registry/interfaces/distroseries.py (+7/-2)
lib/lp/registry/interfaces/distroseriesdifference.py (+7/-3)
lib/lp/registry/model/distribution.py (+10/-5)
lib/lp/registry/model/distroseries.py (+8/-3)
lib/lp/registry/model/distroseriesdifference.py (+13/-7)
lib/lp/registry/stories/distribution/xx-distribution-launchpad-usage.txt (+24/-1)
lib/lp/registry/stories/distribution/xx-distribution-overview.txt (+3/-1)
lib/lp/registry/stories/distroseries/xx-distroseries-index.txt (+3/-1)
lib/lp/registry/stories/distroseries/xx-reassign-distroseries.txt (+0/-72)
lib/lp/registry/stories/webservice/xx-distribution.txt (+1/-0)
lib/lp/registry/stories/webservice/xx-distroseries.txt (+2/-1)
lib/lp/registry/templates/distribution-index.pt (+2/-2)
lib/lp/registry/templates/distroseries-index.pt (+2/-5)
lib/lp/registry/templates/distroseries-localdifferences.pt (+9/-0)
lib/lp/registry/templates/packagesearch-macros.pt (+3/-6)
lib/lp/registry/tests/test_distribution.py (+111/-3)
lib/lp/registry/tests/test_distributionsourcepackage.py (+2/-1)
lib/lp/registry/tests/test_distroseries.py (+9/-0)
lib/lp/registry/tests/test_pillarname_triggers.py (+10/-7)
lib/lp/scripts/garbo.py (+86/-8)
lib/lp/scripts/tests/test_garbo.py (+161/-4)
lib/lp/scripts/utilities/sanitizedb.py (+16/-3)
lib/lp/services/configure.zcml (+1/-0)
lib/lp/services/session/adapters.py (+40/-0)
lib/lp/services/session/configure.zcml (+12/-0)
lib/lp/services/session/interfaces.py (+15/-0)
lib/lp/services/session/model.py (+47/-0)
lib/lp/services/session/tests/test_session.py (+32/-0)
lib/lp/soyuz/doc/archive-dependencies.txt (+73/-73)
lib/lp/soyuz/doc/archive.txt (+8/-5)
lib/lp/soyuz/doc/distroseriesqueue-translations.txt (+30/-30)
lib/lp/soyuz/doc/publishing.txt (+5/-2)
lib/lp/soyuz/interfaces/distroarchseries.py (+3/-1)
lib/lp/soyuz/model/archive.py (+5/-4)
lib/lp/soyuz/scripts/tests/test_publishdistro.py (+8/-3)
lib/lp/soyuz/stories/distroseries/add-architecture.txt (+10/-0)
lib/lp/soyuz/tests/test_packageupload.py (+16/-8)
lib/lp/testing/factory.py (+31/-5)
lib/lp/testing/tests/test_standard_test_template.py (+5/-4)
lib/lp/translations/browser/tests/test_sharing_information.py (+6/-4)
utilities/soyuz-sampledata-setup.py (+2/-2)

Robert Collins (lifeless) on 2010-10-14

tags:

added: pg83

Robert Collins (lifeless) on 2011-01-12

Changed in launchpad:
importance:	High → Critical

Revision history for this message

Micah Gersten (micahg) wrote on 2011-01-13:

Just got a timeout on https://bugs.launchpad.net/ubuntu
OOPS-1839G1380

Robert Collins (lifeless) on 2011-01-13

description:

updated

Robert Collins (lifeless) on 2011-02-06

description:

updated

Revision history for this message

Robert Collins (lifeless) wrote on 2011-02-23:

Download full text (5.6 KiB)

https://bugs.launchpad.net/ubuntu is down to 7.21 seconds first hit, then 8, 9, 12, 8. The 12 is GIL contention on the server I think, given its capable of rendering much more quickly. The page id is still in my timeout-candidate PPR report - https://devpad.canonical.com/~lpqateam/ppr/lpnet/latest-daily-timeout-candidates.html - but I fear we'll need to wait and capture a fresher oops still since we've improved bug search substantially over the last week.

The bug heat search query is slowwww though:
Time: 52081.905 ms

The bug heat search query is slowwww though:
Time: 52081.905 ms

explain analyze SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache, Bug.date_last_message, Bug.date_last_updated, Bug.date_made_private, Bug.datecreated, Bug.description, Bug.duplicateof, Bug.heat, Bug.heat_last_updated, Bug.id, Bug.latest_patch_uploaded, Bug.message_count, Bug.name, Bug.number_of_duplicates, Bug.owner, Bug.private, Bug.security_related, Bug.title, Bug.users_affected_count, Bug.users_unaffected_count, Bug.who_made_private FROM 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  (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugSubscription.person AND BugSubscription.bug = Bug.id UNION SELECT BugTask.bug FROM BugTask, TeamParticipation WHERE TeamParticipation.person = 2 AND TeamParticipation.team = BugTask.assignee AND BugTask.bug = Bug.id )) ORDER BY Bug.heat DESC, BugTask.id LIMIT 40 OFFSET 0;
                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6957590.92..6957591.02 rows=40 width=1017) (actual time=4311.330..4311.343 rows=40 loops=1)
   ->  Sort  (cost=6957590.92..6958004.71 rows=165515 width=1017) (actual time=4311.328..4311.335 rows=40 loops=1)
         Sort Key: bug.heat, public.bugtask.id
         Sort Method:  top-N heapsort  Memory: 92kB
         ->  Nested Loop  (cost=0.00..6952359.05 rows=165515 width=1017) (actual time=0.076..4067.521 rows=88655 loops=1)
               ->  Seq Scan on bugtask  (cost=0.00..54723.29 rows=165515 width=276) (actual time=0.046..847.575 rows=190392 loops=1)
                     Filter: ((distribution = 1) AND ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25)))
               ->  Index Scan using bug_pkey on bug  (cost=0.00..41.66 rows=1 width=741) (actual time=0.016..0.016 rows=0 loops=190392)
                     Index Cond: (bug.id = public.bugtask.bug)
                     Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 1)))
                     SubPlan 1
                       ->  Unique  (cost=39.25..39.26 rows=2 width=4) (actual time=0.061..0.061 rows=1 loops=7934)
                             ->  Sort  (cost=39.25..39.25 rows=2 width=4) (actual time=0.060..0.060 rows=1 loops=7934)
                                   Sort Key: bugsubscription.bug
                                   Sort Method:  quicksort  Memory: 25kB
                                   ->  Append  (cost=0.00..39.24 rows=2 width=4) (actual time=0.038..0.055 rows=1 loops=7934)
                                         ->  Nested Loop  (cost=0.00..26.48 rows=1 width=4) (actual time=0.036..0.043 rows=1 loops=7934)
                                               ->  Index Scan using bugsubscription_bug_idx on bugsubscription  (cost=0.00..7.26 rows=3 width=8) (actual time=0.014..0.020 rows=3 loops=7934)
                                                     Index Cond: (bug = $0)
                                               ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=27736)
                                                     Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                         ->  Nested Loop  (cost=0.00..12.74 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=7934)
                                               ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..6.34 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=7934)
                                                     Index Cond: (bug = $0)
                                               ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=8233)
                                                     Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
 Total runtime: 4311.861 ms

Revision history for this message

Robert Collins (lifeless) wrote on 2011-02-23:

Dropping bugtask.id from the sort order makes this a 3ms query:

Limit (cost=0.00..6939.56 rows=40 width=1017) (actual time=0.144..2.664 rows=40 loops=1)
   -> Nested Loop (cost=0.00..28715046.16 rows=165515 width=1017) (actual time=0.142..2.647 rows=40 loops=1)
         -> Index Scan Backward using bug__heat__idx on bug (cost=0.00..28409422.84 rows=555261 width=741) (actual time=0.075..0.473 rows=64 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 -> Unique (cost=39.25..39.26 rows=2 width=4) (never executed)
                       -> Sort (cost=39.25..39.25 rows=2 width=4) (never executed)
                             Sort Key: bugsubscription.bug
                             -> Append (cost=0.00..39.24 rows=2 width=4) (never executed)
                                   -> Nested Loop (cost=0.00..26.48 rows=1 width=4) (never executed)
                                         -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   -> Nested Loop (cost=0.00..12.74 rows=1 width=4) (never executed)
                                         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.34 rows=1 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.54 rows=1 width=276) (actual time=0.026..0.031 rows=1 loops=64)
               Index Cond: (public.bugtask.bug = bug.id)
               Filter: ((public.bugtask.distribution = 1) AND ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
Total runtime: 3.002 ms

Anything sorting by heat shouldn't care about bugtask ids, so this is a no brainer - though we /may/ have some test fallout.

Dropping bugtask.id from the sort order makes this a 3ms query:

Limit  (cost=0.00..6939.56 rows=40 width=1017) (actual time=0.144..2.664 rows=40 loops=1)
   ->  Nested Loop  (cost=0.00..28715046.16 rows=165515 width=1017) (actual time=0.142..2.647 rows=40 loops=1)
         ->  Index Scan Backward using bug__heat__idx on bug  (cost=0.00..28409422.84 rows=555261 width=741) (actual time=0.075..0.473 rows=64 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 ->  Unique  (cost=39.25..39.26 rows=2 width=4) (never executed)
                       ->  Sort  (cost=39.25..39.25 rows=2 width=4) (never executed)
                             Sort Key: bugsubscription.bug
                             ->  Append  (cost=0.00..39.24 rows=2 width=4) (never executed)
                                   ->  Nested Loop  (cost=0.00..26.48 rows=1 width=4) (never executed)
                                         ->  Index Scan using bugsubscription_bug_idx on bugsubscription  (cost=0.00..7.26 rows=3 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   ->  Nested Loop  (cost=0.00..12.74 rows=1 width=4) (never executed)
                                         ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..6.34 rows=1 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..0.54 rows=1 width=276) (actual time=0.026..0.031 rows=1 loops=64)
               Index Cond: (public.bugtask.bug = bug.id)
               Filter: ((public.bugtask.distribution = 1) AND ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
 Total runtime: 3.002 ms

Anything sorting by heat shouldn't care about bugtask ids, so this is a no brainer - though we /may/ have some test fallout.

Revision history for this message

Robert Collins (lifeless) wrote on 2011-02-23:

It should be totally safe to use this for Ubuntu as a whole, and perhaps distroseries queries; I don't know if per package, per project searches have enough heat variation to behave nicely. However, I suggest we try just dropping the .id field in the order for heat ordered searches based on a feature flag. If it looks poor on real things, we can easily reinstate it, and then do a more complex index (e.g. a partial one on heat desc, importance desc, bug.id where status is an open status).

Robert Collins (lifeless) on 2011-03-14

description:	updated
description:	updated

Robert Collins (lifeless) on 2011-03-14

summary:

- Distribution:+bugs-index timing out in ~2% of requests
+ Distribution:+bugs-index time outs

Stuart Bishop (stub) on 2011-03-25

description:	updated
tags:	removed: pg83

Robert Collins (lifeless) on 2011-03-26

description:

updated

Revision history for this message

Stuart Bishop (stub) wrote on 2011-03-28:

Indexes on Bug(id, heat) or Bug(heat, id) do not help.

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-28:

Download full text (6.3 KiB)

Ugh ;( - thats surprising - this is the plan I see with (heat, id desc) + a modified query (ORDER BY HEAT DESC, Bug.ID)

                                                                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6948.98 rows=40 width=1017) (actual time=0.068..1.751 rows=40 loops=1)
   -> Nested Loop (cost=0.00..28763892.59 rows=165572 width=1017) (actual time=0.067..1.731 rows=40 loops=1)
         -> Index Scan Backward using bug_heat_test on bug (cost=0.00..28458137.93 rows=555476 width=741) (actual time=0.030..0.244 rows=67 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 -> Unique (cost=39.25..39.26 rows=2 width=4) (never executed)
                       -> Sort (cost=39.25..39.25 rows=2 width=4) (never executed)
                             Sort Key: bugsubscription.bug
                             -> Append (cost=0.00..39.24 rows=2 width=4) (never executed)
                                   -> Nested Loop (cost=0.00..26.48 rows=1 width=4) (never executed)
                                         -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.26 rows=3 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   -> Nested Loop (cost=0.00..12.74 rows=1 width=4) (never executed)
                                         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.34 rows=1 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..0.54 rows=1 width=276) (actual time=0.016..0.020 rows=1 loops=67)
               Index Cond: (public.bugtask.bug = bug.id)
               Filter: ((public.bugtask.distribution = 1) AND ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
Total runtime: 2.020 ms
(23 rows)

and for e.g. launchpad (this is...

Ugh ;( - thats surprising - this is the plan I see with (heat, id desc) + a modified query (ORDER BY HEAT DESC, Bug.ID)

QUERY PLAN                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6948.98 rows=40 width=1017) (actual time=0.068..1.751 rows=40 loops=1)
   ->  Nested Loop  (cost=0.00..28763892.59 rows=165572 width=1017) (actual time=0.067..1.731 rows=40 loops=1)
         ->  Index Scan Backward using bug_heat_test on bug  (cost=0.00..28458137.93 rows=555476 width=741) (actual time=0.030..0.244 rows=67 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 ->  Unique  (cost=39.25..39.26 rows=2 width=4) (never executed)
                       ->  Sort  (cost=39.25..39.25 rows=2 width=4) (never executed)
                             Sort Key: bugsubscription.bug
                             ->  Append  (cost=0.00..39.24 rows=2 width=4) (never executed)
                                   ->  Nested Loop  (cost=0.00..26.48 rows=1 width=4) (never executed)
                                         ->  Index Scan using bugsubscription_bug_idx on bugsubscription  (cost=0.00..7.26 rows=3 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   ->  Nested Loop  (cost=0.00..12.74 rows=1 width=4) (never executed)
                                         ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..6.34 rows=1 width=8) (never executed)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (never executed)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..0.54 rows=1 width=276) (actual time=0.016..0.020 rows=1 loops=67)
               Index Cond: (public.bugtask.bug = bug.id)
               Filter: ((public.bugtask.distribution = 1) AND ((public.bugtask.status = 10) OR (public.bugtask.status = 15) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25))) 
 Total runtime: 2.020 ms
(23 rows)

and for e.g. launchpad (this is estimated at 50% cheaper).

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..146128.04 rows=40 width=1017) (actual time=1.302..500.965 rows=40 loops=1)
   ->  Nested Loop  (cost=0.00..28736079.82 rows=7866 width=1017) (actual time=1.301..500.932 rows=40 loops=1)
         ->  Index Scan Backward using bug_heat_test on bug  (cost=0.00..28458137.93 rows=555476 width=741) (actual time=0.033..451.044 rows=9434 loops=1)
               Filter: ((duplicateof IS NULL) AND ((NOT private) OR (SubPlan 1)))
               SubPlan 1
                 ->  Unique  (cost=39.25..39.26 rows=2 width=4) (actual time=0.059..0.059 rows=0 loops=6696)
                       ->  Sort  (cost=39.25..39.25 rows=2 width=4) (actual time=0.058..0.058 rows=0 loops=6696)
                             Sort Key: bugsubscription.bug
                             Sort Method:  quicksort  Memory: 25kB
                             ->  Append  (cost=0.00..39.24 rows=2 width=4) (actual time=0.043..0.055 rows=0 loops=6696)
                                   ->  Nested Loop  (cost=0.00..26.48 rows=1 width=4) (actual time=0.032..0.038 rows=0 loops=6696)
                                         ->  Index Scan using bugsubscription_bug_idx on bugsubscription  (cost=0.00..7.26 rows=3 width=8) (actual time=0.011..0.016 rows=4 loops=6696)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=23960)
                                               Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2))
                                   ->  Nested Loop  (cost=0.00..12.74 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=6696)
                                         ->  Index Scan using bugtask__bug__idx on bugtask  (cost=0.00..6.34 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=6696)
                                               Index Cond: (bug = $0)
                                         ->  Index Scan using teamparticipation_team_key on teamparticipation  (cost=0.00..6.39 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=7303)
                                               Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2))
         ->  Index Scan using bugtask__product__bug__key on bugtask  (cost=0.00..0.49 rows=1 width=276) (actual time=0.005..0.005 rows=0 loops=9434)
               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))
 Total runtime: 501.258 ms
(24 rows)

I'm curious why we're seeing different results :(

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2011-03-31:

Fixed in db-stable r10366 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10366>.

Changed in launchpad:
assignee:	nobody → Robert Collins (lifeless)
milestone:	none → 11.04
tags:	added: qa-needstesting
Changed in launchpad:
status:	Triaged → Fix Committed

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-31:

db patch takes 5 minutes to apply; we're good to go and can iterate in the appservers to improve after the deploy.

tags:

added: qa-ok
removed: qa-needstesting

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-31:

The indices we added may be wrong; will need to run scenarios and get a good index + query.

for instance,
....
AND Bug.private = FALSE and sourcepackagename is null
ORDER BY distribution desc, sourcepackagename desc, Bugtask.heat DESC LIMIT 40

is serviced by the query we added. (And whee - thats nice and fast - 2ms).

similarly, ordering by product desc with a constraint on product works.

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-31:

#10

candidate missing indices (first one will replace bugtask__product__heat__idx)

CREATE INDEX bugtask__product__heat__id__idx ON bugtask USING btree (product, heat desc, id) WHERE product IS NOT NULL;
CREATE INDEX bugtask__distribution__head__id__idx ON bugtask USING btree(distribution, heat desc, id) WHERE bugtask IS NOT NULL;

Revision history for this message

Robert Collins (lifeless) wrote on 2011-03-31:

#11

with the test indices added:

  AND Bug.private = FALSE ORDER BY product , Bugtask.heat desc, bugtask.id LIMIT 40
OFFSET 0
;
                                                                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..500.33 rows=40 width=1024) (actual time=0.042..0.784 rows=40 loops=1)
   -> Nested Loop (cost=0.00..101328.76 rows=8101 width=1024) (actual time=0.040..0.771 rows=40 loops=1)
         -> Index Scan using bugtask__product__heat__id__idx on bugtask (cost=0.00..62426.31 rows=8101 width=281) (actual time=0.025..0.272 rows=62 loops=1)
               Index Cond: (product = 10294)
               Filter: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
         -> Index Scan using bug_pkey on bug (cost=0.00..4.79 rows=1 width=743) (actual time=0.007..0.007 rows=1 loops=62)
               Index Cond: (bug.id = bugtask.bug)
               Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
Total runtime: 0.898 ms
(9 rows)

CREATE INDEX bugtask__distribution__heat__id__idx ON bugtask USING btree(distribution, heat desc, id) WHERE distribution IS NOT NULL

  AND Bug.private = FALSE ORDER BY distribution , Bugtask.heat DESC, bugtask.id LIMIT 40
OFFSET 0
;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..124.33 rows=40 width=1024) (actual time=0.154..1.391 rows=40 loops=1)
   -> Nested Loop (cost=0.00..532274.29 rows=171251 width=1024) (actual time=0.153..1.372 rows=40 loops=1)
         -> Index Scan using bugtask__distribution__heat__id__idx on bugtask (cost=0.00..142123.90 rows=171251 width=281) (actual time=0.106..0.398 rows=42 loops=1)
               Index Cond: (distribution = 1)
               Filter: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
         -> Index Scan using bug_pkey on bug (cost=0.00..2.27 rows=1 width=743) (actual time=0.018..0.019 rows=1 loops=42)
               Index Cond: (bug.id = bugtask.bug)
               Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
Total runtime: 1.591 ms
(9 rows)

with the test indices added:

AND Bug.private = FALSE ORDER BY product , Bugtask.heat desc, bugtask.id LIMIT 40
OFFSET 0
;
                                                                          QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..500.33 rows=40 width=1024) (actual time=0.042..0.784 rows=40 loops=1)
   ->  Nested Loop  (cost=0.00..101328.76 rows=8101 width=1024) (actual time=0.040..0.771 rows=40 loops=1)
         ->  Index Scan using bugtask__product__heat__id__idx on bugtask  (cost=0.00..62426.31 rows=8101 width=281) (actual time=0.025..0.272 rows=62 loops=1)
               Index Cond: (product = 10294)
               Filter: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
         ->  Index Scan using bug_pkey on bug  (cost=0.00..4.79 rows=1 width=743) (actual time=0.007..0.007 rows=1 loops=62)
               Index Cond: (bug.id = bugtask.bug)
               Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 0.898 ms
(9 rows)

CREATE INDEX bugtask__distribution__heat__id__idx ON bugtask USING btree(distribution, heat desc, id) WHERE distribution IS NOT NULL

AND Bug.private = FALSE ORDER BY distribution , Bugtask.heat DESC, bugtask.id LIMIT 40
OFFSET 0
;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..124.33 rows=40 width=1024) (actual time=0.154..1.391 rows=40 loops=1)
   ->  Nested Loop  (cost=0.00..532274.29 rows=171251 width=1024) (actual time=0.153..1.372 rows=40 loops=1)
         ->  Index Scan using bugtask__distribution__heat__id__idx on bugtask  (cost=0.00..142123.90 rows=171251 width=281) (actual time=0.106..0.398 rows=42 loops=1)
               Index Cond: (distribution = 1)
               Filter: ((status = 10) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
         ->  Index Scan using bug_pkey on bug  (cost=0.00..2.27 rows=1 width=743) (actual time=0.018..0.019 rows=1 loops=42)
               Index Cond: (bug.id = bugtask.bug)
               Filter: ((bug.duplicateof IS NULL) AND (NOT bug.private))
 Total runtime: 1.591 ms
(9 rows)

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2011-04-01:

#12

Fixed in db-stable r10373 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10373>.

tags:

added: qa-needstesting
removed: qa-ok

Revision history for this message

Launchpad QA Bot (lpqabot) wrote on 2011-04-01:

#13

Fixed in db-stable r10374 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/10374>.

Robert Collins (lifeless) on 2011-04-01

tags:

added: qa-ok
removed: qa-needstesting

William Grant (wgrant) on 2011-04-06

Changed in launchpad:
status:	Fix Committed → Fix Released

Report a bug

This report contains Public information

Everyone can see this information.

Duplicates of this bug

Bug #716701

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.