MaloneApplication:+bugs timeouts in ~2% of requests

The %package% isn't the cause of the performance here - taking it out doesn't alter things.

                                                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=13737674.36..13737674.55 rows=76 width=2316) (actual time=3518.337..3518.372 rows=76 loops=1)
   -> Sort (cost=13737674.36..13737675.32 rows=384 width=2316) (actual time=3518.335..3518.355 rows=76 loops=1)
         Sort Key: bugtask.importance, bugtask.id
         Sort Method: top-N heapsort Memory: 187kB
         -> Nested Loop Left Join (cost=0.00..13737660.44 rows=384 width=2316) (actual time=10.401..3515.553 rows=184 loops=1)
               -> Nested Loop Left Join (cost=0.00..13737540.71 rows=384 width=1022) (actual time=10.395..3513.918 rows=184 loops=1)
                     -> Nested Loop (cost=0.00..13737430.42 rows=384 width=1005) (actual time=10.378..3512.234 rows=184 loops=1)
                           Join Filter: (((bug.fti)::tsvector @@ '''someth'' & ''strang'''::tsquery) OR ((bugtask.fti)::tsvector @@ '''someth'' & ''strang'''::tsquery))
                           -> Seq Scan on bugtask (cost=0.00..48352.10 rows=245470 width=398) (actual time=0.028..512.107 rows=278783 loops=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..55.75 rows=1 width=1109) (actual time=0.007..0.007 rows=1 loops=278783)
                                 Index Cond: (bug.id = bugtask.bug)
                                 Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (subplan)))
                                 SubPlan
                                   -> Nested Loop (cost=0.00..53.88 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=13951)
                                         -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..9.10 rows=7 width=8) (actual time=0.007..0.008 rows=3 loops=13951)
                                               Index Cond: (bug = $0)
                                         -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.38 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=35789)
                                               Index Cond: ((teamparticipation.team = bugsubscription.person) AND (teamparticipation.person = 2))
                     -> Index Scan using sourcepackagename_pkey on sourcepackagename (cost=0.00..0.27 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=184)
                           Index Cond: (bugtask.sourcepackagename = sourcepackagename.id)
               -> Index Scan using product_pkey on product (cost=0.00..0.30 rows=1 width=1294) (actual time=0.004..0.004 rows=0 loops=184)
                     Index Cond: (bugtask.product = product.id)
 Total runtime: 3518.964 ms

- its having to sort the results

one thing we're missing is an index on (bugtask.importance, bugtask,id); we'll need to make the sort have id desc there, or use a function index.

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, Product.answers_usage, Product.blueprints_usage, Product.owner, Product.translations_usage, Product.active, Product.autoupdate, Product.bug_reported_acknowledgement, Product.bug_reporting_guidelines, Product.bug_supervisor, Product.bugtracker, Product.date_next_suggest_packaging, Product.datecreated, Product.description, Product.development_focus, Product.displayname, Product.downloadurl, Product.driver, Product.enable_bug_expiration, Product.freshmeatproject, Product.homepage_content, Product.homepageurl, Product.icon, Product.id, Product.lastdoap, Product.license_approved, Product.license_info, Product.reviewed, Product.logo, Product.max_bug_heat, Product.mugshot, Product.name, Product.official_answers, Product.official_blueprints, Product.official_malone, Product.official_rosetta, Product.private_bugs, Product.programminglang, Product.project, Product.registrant, Product.remote_product, Product.reviewer_whiteboard, Product.screenshotsurl, Product.security_contact, Product.sourceforgeproject, Product.summary, Product.title, Product.translation_focus, Product.translationgroup, Product.translationpermission, Product.wikiurl, SourcePackageName.id, SourcePackageName.name, 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 LEFT JOIN Bug ON BugTask.bug = Bug.id LEFT JOIN Product ON BugTask.product = Product.id LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE (1=1) AND Bug.id = BugTask.bug AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND (Bug.fti @@ ftq('something strange in the') OR BugTask.fti @@ ftq('something strange in the') OR BugTask.targetnamecache ILIKE '%' || 'something strange in the' || '%') AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 2 AND BugSubscription.person = TeamParticipation.team AND BugSubscription.bug = Bug.id)) ORDER BY BugTask.id LIMIT 76 OFFSET 0

- sorting by id - twice as fast.

ok, adding this index:
CREATE INDEX bugtask_importance_idx on bugtask (importance, id desc nulls first);
drops the search to 1800ms stable on staging; will put a patch together.

There is still 660ms in the fti for some reason; but thats another topic.

New index is live and the difference is visible in oops reports. There may be remaining issues, new bugs will be filed if so.

Fixed in db-stable r9801 (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/9801) by a commit, but not testable.