Bug 735340 - [RFE] Generated SQL statements for search are not well optimized by PostgreSQL
Summary: [RFE] Generated SQL statements for search are not well optimized by PostgreSQL
Keywords:
Status: CLOSED DUPLICATE of bug 984973
Alias: None
Product: oVirt
Classification: Retired
Component: ovirt-engine-core
Version: unspecified
Hardware: All
OS: All
unspecified
high
Target Milestone: ---
: ---
Assignee: Liran Zelkha
QA Contact:
URL: http://ovirt.org/wiki/Searchbackend#G...
Whiteboard: infra
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2011-09-02 12:18 UTC by Laszlo Hornyak
Modified: 2014-01-06 09:36 UTC (History)
11 users (show)

Fixed In Version:
Doc Type: Enhancement
Doc Text:
Clone Of:
Environment:
Last Closed: 2014-01-06 09:36:45 UTC
oVirt Team: ---


Attachments (Terms of Use)

Description Laszlo Hornyak 2011-09-02 12:18:26 UTC
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 12:50:47 UTC
Removed need info as it was ignored, decision remains at rhevm future

Comment 3 Laszlo Hornyak 2012-02-01 13:55:17 UTC
http://ovirt.org/wiki/Searchbackend#Generated_SQL

Comment 4 Itamar Heim 2012-12-11 06:41:51 UTC
eli/juan - thoughts?

Comment 5 Eli Mesika 2012-12-11 08:42:58 UTC
(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 09:29:06 UTC
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 13:43:54 UTC
(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 09:36:45 UTC

*** 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.