We are managing 150 Hosts in our foreman environment and when we press on Hosts -> All Hosts it takes about 40 Seconds to load.
I have no enabled slow query logging in mariadb Server 5.5.53 and figured out that this query takes about 35 Seconds:

Reading mysql slow query log from ex10000.emmi.ch.slow-queries
Count: 1 Time=36.02s (36s) Lock=0.00s (0s) Rows_sent=149.0 (149), Rows_examined=78330.0 (78330), root[root]@localhost
SELECT * FROM `reports` WHERE `reports`.`host_id` IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N) GROUP BY `reports`.`host_id` ORDER BY created_at

We figured out that there seems to missing a Index on Column type in table reports, thus we have added a index with the following SQL Statement:

CREATE INDEX index_reports_on_type_and_host_id ON reports (type,host_id);

Now the query duration dropped down to 0.165 Seconds and the "All Hosts" page is loaded in 4 Seconds.
I would propose to add this index.