Bug 735340 - [RFE] Generated SQL statements for search are not well optimized by PostgreSQL
[RFE] Generated SQL statements for search are not well optimized by PostgreSQL
Status: CLOSED DUPLICATE of bug 984973
Product: oVirt
Classification: Community
Component: ovirt-engine-core (Show other bugs)
unspecified
All All
unspecified Severity high
: ---
: ---
Assigned To: Liran Zelkha
http://ovirt.org/wiki/Searchbackend#G...
infra
: Improvement
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2011-09-02 08:18 EDT by Laszlo Hornyak
Modified: 2014-01-06 04:36 EST (History)
11 users (show)

See Also:
Fixed In Version:
Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-01-06 04:36:45 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Laszlo Hornyak 2011-09-02 08:18:26 EDT
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
Comment 2 Simon Grinberg 2012-02-01 07:50:47 EST
Removed need info as it was ignored, decision remains at rhevm future
Comment 3 Laszlo Hornyak 2012-02-01 08:55:17 EST
http://ovirt.org/wiki/Searchbackend#Generated_SQL
Comment 4 Itamar Heim 2012-12-11 01:41:51 EST
eli/juan - thoughts?
Comment 5 Eli Mesika 2012-12-11 03:42:58 EST
(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
Comment 6 Laszlo Hornyak 2012-12-11 04:29:06 EST
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
Comment 7 Eli Mesika 2013-08-12 09:43:54 EDT
(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
Comment 9 Liran Zelkha 2014-01-06 04:36:45 EST

*** This bug has been marked as a duplicate of bug 984973 ***

Note You need to log in before you can comment on or make changes to this bug.