The get hosts for org query is showing surprisingly large database impact. This task is to investigate that and propose changes that can be made to improve it. 

For reference the query that is showing large impact is

select h1_0.id,
       h1_0.billing_account_id,
       h1_0.billing_provider,
       b1_0.host_id,
       b1_0.as_hypervisor,
       b1_0.billing_account_id,
       b1_0.billing_provider,
       b1_0.product_id,
       b1_0.sla,
       b1_0.usage,
       b1_0.cores,
       b1_0.measurement_type,
       b1_0.sockets,
       b1_0.version,
       h1_0.cloud_provider,
       h1_0.display_name,
       h1_0.is_guest,
       h1_0.hardware_type,
       h1_0.hypervisor_uuid,
       h1_0.insights_id,
       h1_0.instance_id,
       h1_0.instance_type,
       h1_0.inventory_id,
       h1_0.is_hypervisor,
       h1_0.is_unmapped_guest,
       laerdbst1_0.host_id,
       laerdbst1_0.service_type,
       laerdbst1_0.last_applied_event_record_date,
       h1_0.last_seen,
       m1_0.host_id,
       m1_0.metric_id,
       m1_0.value,
       mt1_0.host_id,
       mt1_0.metric_id,
       mt1_0.month,
       mt1_0.value,
       h1_0.num_of_guests,
       h1_0.org_id,
       h1_0.subscription_manager_id
from hosts h1_0
         left join instance_measurements m1_0 on h1_0.id = m1_0.host_id
         left join host_tally_buckets b1_0 on h1_0.id = b1_0.host_id
         left join instance_monthly_totals mt1_0 on h1_0.id = mt1_0.host_id
         left join host_tally_service_type laerdbst1_0 on h1_0.id = laerdbst1_0.host_id
where h1_0.org_id = $1
  and h1_0.instance_type = 'HBI_HOST'
order by coalesce(h1_0.hypervisor_uuid, h1_0.subscription_manager_id), h1_0.hypervisor_uuid, h1_0.inventory_id, h1_0.id
 

On first examination there is no filtering being done on instance_monthly_totals and we currently have systems with as many as 48 months of usage

More generally it seems odd to need to join 4 tables in order to pull one systems information. 

Acceptance Criteria (AC)

1. Root Cause Analysis (RCA)

•   @ElementCollection(fetch = FetchType.EAGER) needs to be looked at: https://github.com/RedHatInsights/rhsm-subscriptions/blob/d88fe0aa7ebe6b672e63fe33e05edbe33b0abc4e/swatch-core/src/main/java/org/candlepin/subscriptions/db/model/Host.java#L97
• This is doing something similar and should be looked at https://github.com/RedHatInsights/rhsm-subscriptions/blob/d88fe0aa7ebe6b672e63fe33e05edbe33b0abc4e/swatch-core/src/main/java/org/candlepin/subscriptions/db/HostRepository.java#L571
• Identify exactly why the instance_monthly_totals join is pulling the entire history of data and confirm if it is needed, or under what conditions it is needed. 
• Provide an "Explain Plan" (a technical report showing how the DB processes the query) to identify which specific "Join" or "Order By" clause is the most expensive.

2. Proposal for Optimization

• Propose a solution to limit the data fetched (e.g., "lazy loading" the history only when a user clicks a host, or adding a date filter to the query).

• Evaluate if the query can be split into smaller, faster queries instead of one massive 4-table join.

3. Performance Benchmarking

• Document current query execution time vs. the projected execution time after the proposed changes.

• Ensure the proposed solution does not break the "Sorting/Ordering" requirements (the order by clause in the query).

4. Implementation Plan

• Provide a breakdown of tasks needed to implement the fix (e.g., API changes, Database index updates, or UI logic changes).