Fedora Account System
Red Hat Associate
Red Hat Customer
Bugzilla seems to generate a search that is incredibly slow on postgresql. It seems to happen when a search is done involving the bug summary (short_desc) An advanced search like this: f1 => OP j1 => OR f2 => keywords o2 => casesubstring v2 => foo f3 => short_desc o3 => substring v3 => bar f4 => bug_group o4 => equals v4 => groupname f5 => CP In plain english, IF keywords contains the substring 'foo' OR summary contains the substring 'bar' OR the bug is in the 'groupname' group This causes bugzilla to emit a query like so WHERE bugs.bug_id IN (1,2,3,4 .. 20000) OR bugs.short_desc ILIKE '%bar%' OR bugs.bug_id IN ( another bug_id list here) 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. A possible way to optimize this is to perform the short_desc search in a separate query and return the list of bug_ids, as the other two search terms do.
> 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.