Description of problem:

The following query is run when the UI is rendered for the Template screen:

SELECT resourcety0_.ID AS col_0_0_,

  (SELECT count(measuremen1_.ID)
   FROM RHQ_MEASUREMENT_DEF measuremen1_,
                            RHQ_RESOURCE_TYPE resourcety2_
   WHERE measuremen1_.RESOURCE_TYPE_ID=resourcety2_.ID
     AND measuremen1_.RESOURCE_TYPE_ID=resourcety0_.ID
     AND measuremen1_.DEFAULT_ON=1) AS col_1_0_,

  (SELECT count(measuremen3_.ID)
   FROM RHQ_MEASUREMENT_DEF measuremen3_,
                            RHQ_RESOURCE_TYPE resourcety4_
   WHERE measuremen3_.RESOURCE_TYPE_ID=resourcety4_.ID
     AND measuremen3_.RESOURCE_TYPE_ID=resourcety0_.ID
     AND measuremen3_.DEFAULT_ON=0) AS col_2_0_,

  (SELECT count(alertdefin5_.ID)
   FROM RHQ_ALERT_DEFINITION alertdefin5_,
                             RHQ_RESOURCE_TYPE resourcety6_
   WHERE alertdefin5_.RESOURCE_TYPE_ID=resourcety6_.ID
     AND alertdefin5_.RESOURCE_TYPE_ID=resourcety0_.ID
     AND alertdefin5_.DELETED=0
     AND alertdefin5_.ENABLED=1) AS col_3_0_,

  (SELECT count(alertdefin7_.ID)
   FROM RHQ_ALERT_DEFINITION alertdefin7_,
                             RHQ_RESOURCE_TYPE resourcety8_
   WHERE alertdefin7_.RESOURCE_TYPE_ID=resourcety8_.ID
     AND alertdefin7_.RESOURCE_TYPE_ID=resourcety0_.ID  
     AND alertdefin7_.DELETED=0
     AND alertdefin7_.ENABLED=0) AS col_4_0_,

  (SELECT count(driftdefin9_.ID)
   FROM RHQ_DRIFT_DEF_TEMPLATE driftdefin9_,
                               RHQ_RESOURCE_TYPE resourcety10_
   WHERE driftdefin9_.RESOURCE_TYPE_ID=resourcety10_.ID
     AND driftdefin9_.RESOURCE_TYPE_ID=resourcety0_.ID
     AND driftdefin9_.IS_USER_DEFINED=0) AS col_5_0_,

  (SELECT count(driftdefin11_.ID)
   FROM RHQ_DRIFT_DEF_TEMPLATE driftdefin11_,
                               RHQ_RESOURCE_TYPE resourcety12_
   WHERE driftdefin11_.RESOURCE_TYPE_ID=resourcety12_.ID
     AND driftdefin11_.RESOURCE_TYPE_ID=resourcety0_.ID
     AND driftdefin11_.IS_USER_DEFINED=1) AS col_6_0_
FROM RHQ_RESOURCE_TYPE resourcety0_
WHERE resourcety0_.DELETED=0

The query can take about 90 seconds on Oracle, the explain plan is as follows:

SELECT STATEMENT	0.0 %	8	546815	8	ALL_ROWS	895	6265	8			
SORT (AGGREGATE)	0.0 %					1	8	1			
RHQ.RHQ_MEASUREMENT_DEF  TABLE ACCESS (BY INDEX ROWID)	25.0 %	4	33279	4	ANALYZED	2	16	1			
RHQ.RHQ_METRIC_DEF_KEY_IDX  INDEX (RANGE SCAN)	25.0 %	2	15843	2	ANALYZED	8		1			
SORT (AGGREGATE)	0.0 %					1	8	2			
RHQ.RHQ_MEASUREMENT_DEF  TABLE ACCESS (BY INDEX ROWID)	25.0 %	4	33279	4	ANALYZED	6	48	1			
RHQ.RHQ_METRIC_DEF_KEY_IDX  INDEX (RANGE SCAN)	25.0 %	2	15843	2	ANALYZED	8		1			
SORT (AGGREGATE)	0.0 %					1	7	3			
**** RHQ.RHQ_ALERT_DEFINITION  TABLE ACCESS (FULL)	3900.0 %	312	42738529	310	ANALYZED	1	7	1			
SORT (AGGREGATE)	0.0 %					1	7	4			
**** RHQ.RHQ_ALERT_DEFINITION  TABLE ACCESS (FULL)	3900.0 %	312	42738506	310	ANALYZED	1	7	1			
SORT (AGGREGATE)	0.0 %					1	7	5			
RHQ.RHQ_DRIFT_DEF_TEMPLATE  TABLE ACCESS (BY INDEX ROWID)	12.5 %	2	15196	2	ANALYZED	1	7	1			
RHQ.RHQ_DRIFT_DEF_TEMPLATE_UNIQUE  INDEX (RANGE SCAN)	12.5 %	1	7521	1	ANALYZED	1		1			
SORT (AGGREGATE)	0.0 %					1	7	6			
RHQ.RHQ_DRIFT_DEF_TEMPLATE  TABLE ACCESS (BY INDEX ROWID)	12.5 %	2	15196	2	ANALYZED	1	7	1			
RHQ.RHQ_DRIFT_DEF_TEMPLATE_UNIQUE  INDEX (RANGE SCAN)	12.5 %	1	7521	1	ANALYZED	1		1			
RHQ.RHQ_RESOURCE_TYPE  TABLE ACCESS (FULL)	100.0 %	8	546815	8	ANALYZED	895	6265	7			

The size is approximately 75,000 rows in rhq_alert_definition.

The fix seems to be adding the following indexes:

create index idx_resource_type on rhq.rhq_alert_definition(resource_type_id)
create index idx_enabled on rhq.rhq_alert_definition(enabled)
create index idx_deleted on rhq.rhq_alert_definition(deleted)


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


How reproducible: Based on size


Steps to Reproduce:
1. Create a large number of alert definitions

Actual results: UI does not render (> 30 seconds)

Expected results: UI renders quickly

Additional info:

The query should probably be optimized to simply show if there is an alert definition (1/0) rather than the count of the definitions. Or, possibly not show a count at all.