-
Task
-
Resolution: Unresolved
-
Normal
-
None
-
None
-
5
-
False
-
-
False
-
subs-swatch-thunder
-
-
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.
