Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 735340

Summary:	[RFE] Generated SQL statements for search are not well optimized by PostgreSQL
Product:	[Retired] oVirt	Reporter:	Laszlo Hornyak <lhornyak>
Component:	ovirt-engine-core	Assignee:	Liran Zelkha <lzelkha>
Status:	CLOSED DUPLICATE	QA Contact:
Severity:	high	Docs Contact:
Priority:	unspecified
Version:	unspecified	CC:	acathrow, bazulay, dfediuck, emesika, iheim, jkt, lpeer, lzelkha, mgoldboi, Rhev-m-bugs, yeylon
Target Milestone:	---	Keywords:	Improvement
Target Release:	---
Hardware:	All
OS:	All
URL:	http://ovirt.org/wiki/Searchbackend#Generated_SQL
Whiteboard:	infra
Fixed In Version:		Doc Type:	Enhancement
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2014-01-06 09:36:45 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

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