Bug 613981
| Summary: | Perf: consider putting an end_time index on rhq_availability | ||
|---|---|---|---|
| Product: | [Other] RHQ Project | Reporter: | Heiko W. Rupp <hrupp> |
| Component: | Database | Assignee: | RHQ Project Maintainer <rhq-maint> |
| Status: | CLOSED WONTFIX | QA Contact: | Mike Foley <mfoley> |
| Severity: | medium | Docs Contact: | |
| Priority: | medium | ||
| Version: | 4.0.0 | CC: | ccrouch, jshaughn, rbuck, tao |
| Target Milestone: | --- | ||
| Target Release: | --- | ||
| Hardware: | All | ||
| OS: | All | ||
| Whiteboard: | |||
| Fixed In Version: | Doc Type: | Bug Fix | |
| Doc Text: | Story Points: | --- | |
| Clone Of: | Environment: | ||
| Last Closed: | 2014-06-02 19:16:55 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: | |||
| Bug Depends On: | |||
| Bug Blocks: | 620933 | ||
Heiko, We would need more justification to add this index. I recall that the initial idea came from observations in Oracle EM not enough to go on here |
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)