Not great.

1. SELECT * is generally bad even if you really need all or most of the columns, it is even worse if you only need some small number that could be covered by the index
2. Depending on the optimization the subquery itself might be evaluated many times - once per main table row. But it might be optimized by joining or by materializing too. Execution plan can show you.
3. The subquery contains OR and that cannot use indexes efficiently (it can do index merge, but not with your index only)

For index merge two indexes could work together

• (contractId, groupId)
• (predecessorContractId, groupId) (or maybe their reverses, depending on the actual plan)

Better imho would be to get rid of the OR - do two conditions with two separate subqueries instead, use EXISTS instead of IN and switch the indexes to have groupId first. That way each subquery will have specific groupId and will only check the second column in the index fast.

Another way is to get rid of the subquery entirely, you can JOIN the table to itself (but possibly the optimizer does that for you already?)

1. SELECT * is generally bad even if you really need all or most of the columns, it is even worse if you only need some small number that could be covered by the index
2. Depending on the optimization the subquery itself might be evaluated many times - once per main table row. But it might be optimized by joining or by materializing too. Execution plan can show you.
3. The subquery contains OR and that cannot use indexes efficiently (it can do index merge, but not with your index only)

For index merge two indexes could work together

• (contractId, groupId)
• (predecessorContractId, groupId) (or maybe their reverses, depending on the actual plan)

Better imho would be to get rid of the OR - do two conditions with two separate subqueries instead, use EXISTS instead of IN and switch the indexes to have groupId first. That way each subquery will have specific groupId and will only check the second column in the index fast.

Another way is to get rid of the subquery entirely, you can JOIN the table to itself (but possibly the optimizer does that for you already?)