Bug 960831

Summary: QueryData2 generates slow SQL for AuditLog with no parameters
Product: Red Hat Enterprise Virtualization Manager Reporter: Liran Zelkha <lzelkha>
Component: ovirt-engineAssignee: Eli Mesika <emesika>
Status: CLOSED UPSTREAM QA Contact: Pavel Stehlik <pstehlik>
Severity: medium Docs Contact:
Priority: unspecified    
Version: 3.3.0CC: acathrow, bazulay, iheim, jkt, lpeer, pstehlik, Rhev-m-bugs, yeylon, yzaslavs
Target Milestone: ---Keywords: Triaged
Target Release: 3.3.0   
Hardware: All   
OS: All   
Whiteboard: infra
Fixed In Version: is9 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2013-08-08 06:15:39 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: Infra RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Liran Zelkha 2013-05-08 05:45:18 UTC 
Description of problem:
When the UI queries the AuditLog table, it doesn't require a filter (only getting the first 100 records). The generated query is:
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and audit_log_id IN (SELECT audit_log.audit_log_id FROM  audit_log  ) and not deleted)  ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100

when in fact it should be
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not deleted)  ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100;


Version-Release number of selected component (if applicable):


How reproducible:
Call SearchQuery.searchAuditLogEvents.

Steps to Reproduce:
1.
2.
3.
  
Actual results:
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and audit_log_id IN (SELECT audit_log.audit_log_id FROM  audit_log  ) and not deleted)  ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100


Expected results:
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not deleted)  ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100;


Additional info:
Comment 1 Itamar Heim 2013-05-12 12:04:15 UTC 
the main events tab (which has paging) or the various subtabs? or the bottom list of events (showing the last one only by default)?


> SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not deleted)  ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100;

why ORDER BY audit_log_id DESC ,audit_log_id DESC
(audit_log_id DESC, twice)?
Comment 2 Eli Mesika 2013-07-31 12:58:49 UTC 
(In reply to Itamar Heim from comment #1)
> the main events tab (which has paging) or the various subtabs? or the bottom
> list of events (showing the last one only by default)?
> 
> 
> > SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not deleted)  ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 100;
> 
> why ORDER BY audit_log_id DESC ,audit_log_id DESC
> (audit_log_id DESC, twice)?

This is a bug of course (altough it has no harm since the PG optimizer will filter this redundancy) and was addressed as a part of teh suggested patch.
Comment 3 Eli Mesika 2013-08-01 11:14:41 UTC 
fixed in commit: b922343