Just to be clarify, although when that query is not running the interface is still slow I still think the query needs to be rewritten.
The usage of the in clause should be avoided and there’s two of them.
From what I can tell something like:
SELECT kr.*
FROM katello_rpms kr
LEFT JOIN (
SELECT krr.*
FROM katello_repository_rpms krr
INNER JOIN katello_repositories kr ON krr.repository_id = kr.id
) subq ON kr.id = subq.rpm_id
WHERE subq.rpm_id IS NULL
should give the same results and is much faster. I have instances of the original query running for more than 5 hours now (we have a daily sync plan to synchronize repositories) and the newer version takes only a couple of seconds to run.
Unfortunately I do not know enough about katello and rails so I am not able to find the correct place to replace the query…
If someone is able to point me in the right direction I can test it on my instance.