Bug 1266261
| Summary: | Slow bug searching when searching on short_desc | ||
|---|---|---|---|
| Product: | [Community] Bugzilla | Reporter: | Matt Tyson 🤬 <mtyson> |
| Component: | Query/Bug List | Assignee: | Jeff Fearn 🐞 <jfearn> |
| Status: | CLOSED CURRENTRELEASE | QA Contact: | tools-bugs <tools-bugs> |
| Severity: | medium | Docs Contact: | |
| Priority: | medium | ||
| Version: | 4.4 | CC: | meli, qgong |
| Target Milestone: | 5.0-RH10 | ||
| Target Release: | --- | ||
| Hardware: | Unspecified | ||
| OS: | Unspecified | ||
| Whiteboard: | |||
| Fixed In Version: | 5.0.4-RH40 | Doc Type: | Bug Fix |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2020-04-01 12:20:16 UTC | Type: | Bug |
| Regression: | --- | Mount Type: | --- |
| Documentation: | --- | CRM: | |
| Verified Versions: | Category: | --- | |
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |
| Cloudforms Team: | --- | Target Upstream Version: | |
| Embargoed: | |||
|
Description
Matt Tyson 🤬
2015-09-24 22:27:08 UTC
> Postgres seems to really struggle with the bugs.short_desc ILIKE '%bar%'
> clause.
> The query can take up to 5 minutes to complete. If it is removed then the
> query will finish in a matter of seconds.
That wasn't worded too well.
The like query itself is quick to complete, as are the bug_id queries.
It's the joining of the LIKE query to the bug_id queries that causes the problem.
In isolation, all three components of the query execute quite quickly.
It sounds like we need to try to undo some of the MySQL optimizations, or provide pg alternatives so that the code can go upstream. *** Bug 1264851 has been marked as a duplicate of this bug. *** Moving this to 5.x as we need to change the way the middle query is run. If you fetch the bugs the same as the other two parts and present 3 bugs.bug_id IN statements then it's very fast. Intrusive change so should be postponed to after 5.0. This is a search that times out in Production and takes 850 seconds to run on a dev system with timeouts disabled. buglist.cgi?f1=keywords&f2=short_desc&f3=bug_group&j_top=OR&list_id=10602895&o1=casesubstring&o2=substring&o3=equals&query_format=advanced&v1=Regression&v2=bug&v3=redhat Time : 11.500512 seconds That's just the SQL time, not bad given the search is pretty broad and returns over 200K results if allowed to, which is isn't! Test environment: https://bz-web.host.qe.eng.pek2.redhat.com Steps to verify: 1. Timing the visit for page https://bz-web.host.qe.eng.pek2.redhat.com/buglist.cgi?f1=keywords&f2=short_desc&f3=bug_group&j_top=OR&list_id=10609496&o1=casesubstring&o2=substring&o3=equals&query_format=advanced&v1=Regression&v2=bug&v3=redhat Result: It takes 22 seconds in qe instance, because this search is pretty broad with lots of data, so I can accept it. Actual Result: PASS This change is now live. If there are any issues, do not reopen this bug. Instead, you should create a new bug and reference this bug. |