Distribution:+bugs search has worrying overhead due to package name matching
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Launchpad itself |
Fix Released
|
Critical
|
Robert Collins |
Bug Description
The big problem is the ILIKE '%foo%' substring search on BugTask.
If we can't drop this clause, we will need a new index type like Wildspeed (http://
This was originally filed as a fti bug, but was largely a dupe/operational issue; the discussion is worthwhile though in the context of ILIKE, so I've repurposed the bug to keep that discussion handy.
https:/
SQL time: 13899 ms
Non-sql time: 646 ms
Total time: 14545 ms
Statement Count: 29
Distribution:
Related branches
- Robert Collins (community): Approve
-
Diff: 82 lines (+33/-3)3 files modifiedlib/lp/bugs/model/bugtask.py (+9/-3)
lib/lp/bugs/tests/test_bugtask_search.py (+20/-0)
lib/lp/services/features/flags.py (+4/-0)
description: | updated |
description: | updated |
Changed in launchpad: | |
importance: | High → Critical |
tags: | added: timeout |
tags: | added: bad-commit-12374 |
description: | updated |
tags: |
added: qa-ok removed: qa-needstesting |
Changed in launchpad: | |
status: | Fix Committed → Fix Released |
slow query: 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. sourcepackagena me, BugTask.status, BugTask. statusexplanati on, BugTask. targetnamecache , "_prejoin1".owner, "_prejoin1".active, "_prejoin1" .autoupdate, "_prejoin1" .bug_reported_ acknowledgement , "_prejoin1" .bug_reporting_ guidelines, "_prejoin1" .bug_supervisor , "_prejoin1" .bugtracker, "_prejoin1" .date_next_ suggest_ packaging, "_prejoin1" .datecreated, "_prejoin1" .description, "_prejoin1" .development_ focus, "_prejoin1" .displayname, "_prejoin1" .downloadurl, "_prejoin1".driver, "_prejoin1" .enable_ bug_expiration, "_prejoin1" .freshmeatproje ct, "_prejoin1" .homepage_ content, "_prejoin1" .homepageurl, "_prejoin1".icon, "_prejoin1".id, "_prejoin1" .lastdoap, "_prejoin1" .license_ approved, "_prejoin1" .license_ info, "_prejoin1" .reviewed, "_prejoin1".logo, "_prejoin1" .max_bug_ heat, "_prejoin1" .mugshot, "_prejoin1".name, "_prejoin1" .official_ answers, "_prejoin1" .official_ blueprints, "_prejoin1" .official_ malone, "_prejoin1" .official_ rosetta, "_prejoin1" .private_ bugs, "_prejoin1" .programminglan g, "_prejoin1" .project, "_prejoin1" .registrant, "_prejoin1" .remote_ product, "_prejoin1" .reviewer_ whiteboard, "_prejoin1" .screenshotsurl , "_prejoin1" .security_ contact, "_prejoin1" .sourceforgepro ject, "_prejoin1" .summary, "_prejoin1".title, "_prejoin1" .translation_ focus, "_prejoin1" .translationgro up, "_prejoin1" .translationper mission, "_prejoin1" .wikiurl, "_prejoin2".id, "_prejoin2".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 Bug, BugTask LEFT JOIN Product AS "_prejoin1" ON BugTask.product = "_prejoin1".id LEFT JOIN SourcePackageName AS "_prejoin2" ON BugTask. sourcepackagena me = "_prejoin2".id WHERE Bug.id = BugTask.bug AND BugTask. distribution = 1 AND ((BugTask.status = 10) OR (BugTask.status = 15) AND (Bug.date_ last_message IS NOT NULL AND BugTask. date_incomplete <= Bug.date_ last_message) OR (BugTask.status = 15) AND (Bug.date_ last_message IS NULL OR BugTask. date_incomplete > Bug.date_ last_message) 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('blu-ray') OR BugTask.fti @@ ftq('blu-ray') OR BugTask. targetnamecache ILIKE '%' || 'blu-ray' || '%') AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipati on.person = 15177 AND BugSubscription .person = TeamParticipati on.team AND BugSubscription....
-- SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.