For things like uninventory of resources, the resource id and parent id are queried. Putting an index on this will considerably speed up things. For the long term, some more clever (pre)computation is needed instead of the relatively expensive queries we have today. See also https://fedorahosted.org/pipermail/rhq-devel/2010-June/000204.html https://fedorahosted.org/pipermail/rhq-devel/2010-July/000211.html
We have individual indexes on ID and PARENT_ID. I think this is sufficient.