Bug 678279 - Perf: Add an index on rhq_resource (inventory_status)
Perf: Add an index on rhq_resource (inventory_status)
Status: NEW
Product: RHQ Project
Classification: Other
Component: Database (Show other bugs)
4.0.0
Unspecified Unspecified
low Severity medium (vote)
: ---
: ---
Assigned To: RHQ Project Maintainer
Mike Foley
:
Depends On:
Blocks: rhq-perf
  Show dependency treegraph
 
Reported: 2011-02-17 07:29 EST by Heiko W. Rupp
Modified: 2015-02-01 18:29 EST (History)
3 users (show)

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


Attachments (Terms of Use)

  None (edit)
Description Heiko W. Rupp 2011-02-17 07:29:55 EST
After more digging into the sources and queries, I propose to add an index like this:

CREATE INDEX "RHQ_RES_I_STAT_IDX" ON "RHQ_RESOURCE" ("INVENTORY_STATUS") TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) NOLOGGING LOCAL

While it may not help a lot on this specific query, there is a large number of queries in the system that uses the inventory_status column.

-----

In the longer run, it may make sense to turn the column into something integer based.
Comment 1 Jay Shaughnessy 2011-09-15 14:34:56 EDT
Given the low cardinality of the field would this index really be helpful or would the index just add overhead to insert/deletes on resources?
Comment 2 Ian Springer 2011-09-15 14:44:48 EDT
Heiko,

Are there particular queries you know are slow because of no index here? We need more justification for adding this.
Comment 3 Heiko W. Rupp 2012-03-05 03:20:33 EST
I don't recall. Original idea came from observations in OracleEM.

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