Description of problem:
Having many Host Collections with many Hosts associated to them, getting a Host details takes tens of seconds.

E.g. having 5k Hosts and 100 Host Collections where five biggest Collections have 4k Hosts each, querying a Host (which is in all the five biggest Collections) takes tens of seconds.

The more Host Collections (and the bigger they are) the Host is assigned to, the longer the query takes.


Version-Release number of selected component (if applicable):
Sat6.13


How reproducible:
100%


Steps to Reproduce:
1. Have thousands of Hosts (e.g. the 5k I had). You can populate your Satellite via a very few real Hosts, by repeatedly running on them something like:

uuid=$(uuidgen)
echo "{\"dmi.system.uuid\": \"${uuid}\"}" > /etc/rhsm/facts/uuid.facts
hostnamectl set-hostname host-${uuid%%-*}.some.domain.com
subscription-manager clean
subscription-manager register --activationkey ak_test --org RedHat

(each iteration of above commands creates one Host)

2. Fetch a Host details few times, like:
# for i in $(seq 1 10); do time curl -ks -u admin:redhat 'https://localhost/api/v2/hosts/2520' > /dev/null; done 2>&1 | grep real
real	0m0.269s
real	0m0.258s
real	0m0.255s
real	0m0.253s
real	0m0.252s
real	0m0.266s
real	0m0.667s
real	0m0.255s
real	0m0.246s
real	0m0.256s
#

3. Create 100 Host Collections, empty so far:

collections=100

( for i in $(seq 1 $collections); do
	echo "host-collection create --organization RedHat --name Host_Collection_${i} --unlimited-hosts"
done ) | time hammer shell

4. Fetch the Host repeatedly again, to ensure it is still same fast.
5. Add many Hosts to the Host Collections, like e.g.:

( for c in $(seq 1 $collections); do
    hostids=$(su - postgres -c "psql foreman -c \"COPY (SELECT id FROM hosts WHERE id > $((10*c))) TO STDOUT\"" | tr '\n' ',' | sed "s/,$//g")
    echo "host-collection add-host --organization RedHat --name Host_Collection_${c} --host-ids ${hostids}"
done ) | time hammer shell

(each Host Collection will differ by 10 Hosts)

6. Fetch the Host repeatedly again; optionally chose different Hosts depending on how many Host Collections they belong to (and how "big" Collections there are).

E.g.:

su - postgres -c "psql foreman -c \"SELECT COUNT(*),host_id FROM katello_host_collection_hosts GROUP BY host_id ORDER BY count DESC LIMIT 5;\""

tells you Host IDs that are associated to the most Collections.


Actual results:
2. and 4. shows pretty ow times within one second.
6. shows tens of seconds like:

# for i in $(seq 1 10); do time curl -ks -u admin:redhat 'https://localhost/api/v2/hosts/2520' > /dev/null; done 2>&1 | grep real
real	0m24.980s
real	0m35.901s
real	0m26.356s
real	0m23.446s
real	0m26.306s
real	0m26.919s
real	0m36.831s
real	1m10.623s
real	0m35.503s
real	0m19.224s
#

While a Host in just very few Host Collections is OK:

# for i in $(seq 1 10); do time curl -ks -u admin:redhat 'https://localhost/api/v2/hosts/51' > /dev/null; done 2>&1 | grep real
real	0m4.446s
real	0m1.202s
real	0m1.704s
real	0m0.978s
real	0m1.007s
real	0m1.190s
real	0m1.831s
real	0m0.907s
real	0m1.049s
real	0m2.015s
#


Expected results:
All the times should be within a few seconds.


Additional info:
Enabling psql debugs, here is the source of slowness:

2023-11-10T17:28:47 [I|app|c972f5f9] Started GET "/api/v2/hosts/2520" for 127.0.0.1 at 2023-11-10 17:28:47 +0100
2023-11-10T17:28:47 [I|app|c972f5f9] Processing by Api::V2::HostsController#show as JSON
2023-11-10T17:28:47 [I|app|c972f5f9]   Parameters: {"apiv"=>"v2", "id"=>"2520"}
..
2023-11-10T17:28:47 [D|sql|c972f5f9]   Katello::HostCollection Load (0.6ms)  SELECT "katello_host_collections".* FROM "katello_host_collections" INNER JOIN "katello_host_collection_hosts" ON "katello_host_collections"."id" = "katello_host_collection_hosts"."host_collection_id" WHERE "katello_host_collection_hosts"."host_id" = $1  [["host_id", 2520]]
2023-11-10T17:28:47 [D|sql|c972f5f9]   Host::Managed Load (14.7ms)  SELECT "hosts".* FROM "hosts" INNER JOIN "katello_host_collection_hosts" ON "hosts"."id" = "katello_host_collection_hosts"."host_id" WHERE "hosts"."type" = $1 AND "katello_host_collection_hosts"."host_collection_id" = $2  [["type", "Host::Managed"], ["host_collection_id", 201]]
2023-11-10T17:28:47 [D|sql|c972f5f9]   Host::Managed Load (15.1ms)  SELECT "hosts".* FROM "hosts" INNER JOIN "katello_host_collection_hosts" ON "hosts"."id" = "katello_host_collection_hosts"."host_id" WHERE "hosts"."type" = $1 AND "katello_host_collection_hosts"."host_collection_id" = $2  [["type", "Host::Managed"], ["host_collection_id", 202]]
..
2023-11-10T17:29:13 [D|sql|c972f5f9]   Host::Managed Load (13.4ms)  SELECT "hosts".* FROM "hosts" INNER JOIN "katello_host_collection_hosts" ON "hosts"."id" = "katello_host_collection_hosts"."host_id" WHERE "hosts"."type" = $1 AND "katello_host_collection_hosts"."host_collection_id" = $2  [["type", "Host::Managed"], ["host_collection_id", 300]]
2023-11-10T17:29:13 [D|sql|c972f5f9]   FactValue Load (0.9ms)  SELECT "fact_values".* FROM "fact_values" WHERE "fact_values"."host_id" = $1  [["host_id", 2520]]
..
2023-11-10T17:29:13 [I|app|c972f5f9] Completed 200 OK in 25707ms (Views: 23842.3ms | ActiveRecord: 1751.7ms | Allocations: 7109377)


The queries (for each Host Collection the Host belongs to) "give me all (managed) Hosts from the Host Collection" are the culprit; why do we need them at all..?