[This is a summary of https://lists.fedorahosted.org/pipermail/rhq-devel/2012-October/002220.html]

ClusterManagerBean#buildQuery (which is used by ClusterManagerBean#getAutoClusterResources) contains the following code:

        // this is an authorization-related query, so use implicitResource (not explicitResources)
        if (1 == size) {
            query
                .append("SELECT rgir FROM ResourceGroup rg JOIN rg.implicitResources rgir WHERE rgir.inventoryStatus = 'COMMITTED' AND rg = "
                + clusterKey.getClusterGroupId());
        } else {
            buildQuery(query, params, clusterKey, nodes.subList(0, size - 1));
        }

We have a situation where the query constructed by this method causes a performance issue. This occurs for depth 3 resources related to the JVM (e.g. as JVM > Memory Subsystem > MarkSweepCompact) in a compatible group. When trying to open the summary for such a resource, the request times out because the database takes too much time to complete the query. Note that other resources at depth 3 are not affected by this problem. My guess is that this is related to the fact that the keys of the resources in the hierarchy ('JVM', 'java.lang:type=Memory' and 'java.lang:name=MarkSweepCompact,type=GarbageCollector' in the example) for these resources are "less unique" than for other resources (we have 39 JVMs in the inventory).

ClusterManagerBean#getAutoClusterResources gets a cluster key as input and is expected to return the corresponding resources. In my case, the cluster key refers to a group of WebSphere servers and its hierarchy is ("JVM", "java.lang:type=Memory", "java.lang:name=MarkSweepCompact,type=GarbageCollector"). The query searches for resources satisfying all of the following conditions:

* The key of the resource is 'java.lang:name=MarkSweepCompact,type=GarbageCollector'.
* The resource key of the parent is 'java.lang:type=Memory '.
* The resource key of the grandparent is 'JVM'.
* The grandgrandparent is an implicit member of the group of WebSphere servers.

That makes sense, except for the usage of the implicit membership in the last condition. In fact, the grandgrandparent can only be a root level member (a WebSphere server in my case), i.e. it is always part of the explicit membership. Therefore the query should use explicitResources instead of implicitResources.

Note that the comment in the code shown above doesn't make sense because authorization is not involved here.

I replaced implicitResources by explicitResources and patched an RHQ 4.5.1 installation with that change. I didn't identify any regressions and the change significantly reduces the time taken by getAutoClusterResources.