Bug 113673 - Versions.getTxns is slow on a moderately loaded database
Versions.getTxns is slow on a moderately loaded database
Status: CLOSED DUPLICATE of bug 113480
Product: Red Hat Web Application Framework
Classification: Retired
Component: other (Show other bugs)
All Linux
medium Severity medium
: ---
: ---
Assigned To: Vadim Nasardinov
Jon Orris
Depends On:
Blocks: 106481
  Show dependency treegraph
Reported: 2004-01-16 07:22 EST by Daniel Berrange
Modified: 2007-04-18 13:01 EDT (History)
0 users

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2006-02-21 14:00:47 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
Explain plans for two queries (5.12 KB, text/plain)
2004-01-16 07:24 EST, Daniel Berrange
no flags Details

  None (edit)
Description Daniel Berrange 2004-01-16 07:22:28 EST
Description of problem:

The getTxns method generates a query that looks like

select vt.id as c_1,
       vt.modifying_ip as c_2,
       vt.timestamp as c_3
  from vcx_txns vt
       left join vcx_obj_changes voc on voc.txn_id = vt.id
       left join vcx_obj_changes changes__voc on changes__voc.id = voc.id
 where (changes__voc.obj_id = 19628)
 order by vt.id desc;

This takes 100ms to execute on my current server populated with 4000

Re-formatting it as

  select vt.id, 
    from vcx_txns vt, 
         vcx_obj_changes voc, 
         vcx_obj_changes changes__voc 
   where voc.txn_id = vt.id 
     and changes__voc.id = voc.id
     and changes__voc.obj_id = 19628 

Reduces it to 7ms

This is on PG 7.3.2 / RHEL 2.1. Attaching explain plans and other
interesting info.

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

How reproducible:

Steps to Reproduce:
Actual results:

Expected results:

Additional info:
Comment 1 Daniel Berrange 2004-01-16 07:24:55 EST
Created attachment 97052 [details]
Explain plans for two queries
Comment 2 Daniel Berrange 2004-01-20 07:22:10 EST
I was smoking something when I posted this. The two queries I
mentioned aren't functionally equivalent - one is an outer jouin the
other not.
This is also a duplicate of bug 113480 since getCreationTxn and
getLastTxn just delgate to this method.

*** This bug has been marked as a duplicate of 113480 ***
Comment 3 Red Hat Bugzilla 2006-02-21 14:00:47 EST
Changed to 'CLOSED' state since 'RESOLVED' has been deprecated.

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