+++ This bug was initially created as a clone of Bug #656469 +++ SELECT MAX(time_stamp) FROM RHQ_MEASUREMENT_DATA_NUM_1D is very expensive with a huge number of data -- basically Oracle does a full index scan as the existing index is (schedule_id, time_stamp). Putting an index on time_stamp only would remove the FIS burden and speed up this operation. This also follows the similar index on the 1h and 6h tables.
commit 15b395b1294158dc65979d3b896e5eca6db421f1 Author: Lukas Krejci <lkrejci> Date: Thu Dec 16 12:11:25 2010 +0100 BZ 656471 - add index on the timestamp of the RHQ_MEASUREMENT_DATA_NUM_1D table to speed up the data compression
Tested on JON-2.4.1 build #47 (build number: 10963:e45ddc8) Steps: 1) Installed JON-2.4.1 on new (don't use upgrade) database 2) Check table description jon241=# \d "rhq_measurement_data_num_1d" Table "public.rhq_measurement_data_num_1d" Column | Type | Modifiers -------------+------------------+----------- time_stamp | bigint | not null schedule_id | integer | not null value | double precision | minvalue | double precision | maxvalue | double precision | Indexes: "rhq_meas_data_1d_id_time_pk" PRIMARY KEY, btree (schedule_id, time_stamp) 3) Check database schema version jon241=# select property_value from rhq_system_config where property_key = 'DB_SCHEMA_VERSION'; property_value ---------------- 2.92.4 (1 row) Observation: There isn't index on the timestamp of the RHQ_MEASUREMENT_DATA_NUM_1D table. As discuss with Lukas, need to add the definition to dbsetup, currently it works with upgrade.
commit 4f29065c937833a03f96d5d09f2f36d9fd5781f9 Author: Lukas Krejci <lkrejci> Date: Mon Dec 20 10:58:40 2010 +0100 BZ 656471 - adding missing index definition in db setup.
Verified on JON-2.4.1 build #49 (build number: 10966:4f29065) There is index on the timestamp of the RHQ_MEASUREMENT_DATA_NUM_1D table. jon241=# \d "rhq_measurement_data_num_1d" Table "public.rhq_measurement_data_num_1d" Column | Type | Modifiers -------------+------------------+----------- time_stamp | bigint | not null schedule_id | integer | not null value | double precision | minvalue | double precision | maxvalue | double precision | Indexes: "rhq_meas_data_1d_id_time_pk" PRIMARY KEY, btree (schedule_id, time_stamp) "rhq_meas_data_1d_time_idx" btree (time_stamp)
Bookkeeping - closing bug - fixed in recent release.