Bug 613981 - Perf: consider putting an end_time index on rhq_availability
Perf: consider putting an end_time index on rhq_availability
Status: CLOSED WONTFIX
Product: RHQ Project
Classification: Other
Component: Database (Show other bugs)
4.0.0
All All
medium Severity medium (vote)
: ---
: ---
Assigned To: RHQ Project Maintainer
Mike Foley
:
Depends On:
Blocks: rhq-perf
  Show dependency treegraph
 
Reported: 2010-07-13 08:31 EDT by Heiko W. Rupp
Modified: 2014-06-02 15:16 EDT (History)
4 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-06-02 15:16:55 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:


Attachments (Terms of Use)

  None (edit)
Description Heiko W. Rupp 2010-07-13 08:31:26 EDT
Deletion of availability data goes

delete from rhq_availability where end_time < ?

Without an index, the database is doing a full table scan which can be extremely expensive

With the index, the database can use the index and can do an index range scan.

rhq300=# explain delete from rhq_availability where end_time < 12345678
rhq300-# ;
                           QUERY PLAN                           
----------------------------------------------------------------
 Seq Scan on rhq_availability  (cost=0.00..9.76 rows=1 width=6)
   Filter: (end_time < 12345678)
(2 rows)

rhq300=# create index end_time_index ON rhq_availability (end_time );
CREATE INDEX
rhq300=# explain delete from rhq_availability where end_time < 12345678
;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Index Scan using end_time_index on rhq_availability  (cost=0.00..8.27 rows=1 width=6)
   Index Cond: (end_time < 12345678)
(2 rows)
Comment 1 Ian Springer 2011-09-15 14:54:49 EDT
Heiko,

We would need more justification to add this index.
Comment 2 Heiko W. Rupp 2012-03-05 03:18:20 EST
I recall that the initial idea came from observations in Oracle EM
Comment 3 Jay Shaughnessy 2014-06-02 15:16:55 EDT
not enough to go on here

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