Description of problem: The queries generated by the search logic are very hard to optimize by PostgreSQL and it seems they end up having multiple seqscans, joining the same table again several times. An improved query generator could add a nice performance gain. Version-Release number of selected component (if applicable): How reproducible: Always Steps to Reproduce: 1. Check query statements in postgersql log 2. run explain query Actual results: several seq_scans Expected results: a few index_scans
Removed need info as it was ignored, decision remains at rhevm future
http://ovirt.org/wiki/Searchbackend#Generated_SQL
eli/juan - thoughts?
(In reply to comment #4) > eli/juan - thoughts? Both suggestions are good and will improve performance although we will have to check all values with regexp to check if the value is UUID. I recommend to implement
Maybe we can break down to 3 bugs: - One of the problems might be the generated outer query. I could not figure out why is it there, but it is everywhere and it seems postgresql's query optimizer has some problem optimizing it. In most cases it is not needed at all, maybe it is some historic workaround? - another problem is the lack of indexes for most of the frequent search criterieas, I am not sure we should add indexes to all possible search queries, but at least a major review seemed to be useful when I tested this - and a major problem was/is the use of 'like' instead of '=' in some cases. Maybe the solution could be a new operator, e.g. '==' or 'is' in the search query, that would generate '=' rather than 'like' in the sql statement
(In reply to Laszlo Hornyak from comment #6) > Maybe we can break down to 3 bugs: > - One of the problems might be the generated outer query. I could not figure > out why is it there, but it is everywhere and it seems postgresql's query > optimizer has some problem optimizing it. In most cases it is not needed at > all, maybe it is some historic workaround? > - another problem is the lack of indexes for most of the frequent search > criterieas, I am not sure we should add indexes to all possible search > queries, but at least a major review seemed to be useful when I tested this > - and a major problem was/is the use of 'like' instead of '=' in some cases. > Maybe the solution could be a new operator, e.g. '==' or 'is' in the search > query, that would generate '=' rather than 'like' in the sql statement For easy tracking and handling this bug please split it as you had suggested to 3 bugs
*** This bug has been marked as a duplicate of bug 984973 ***