Liran Zelkha has uploaded a new change for review. Change subject: core: Fix performance issues with GetHostsAndVmsForClusters ......................................................................
core: Fix performance issues with GetHostsAndVmsForClusters GetHostsAndVmsForClusters takes >30s in performance environment. This is caused by an uneeded join between vm_static and vds_static. This patch uses 2 sub-selects to eliminate the join and improve the performance. Change-Id: I185e157bb00596c10449b637bf0c9a2c3b9d21e5 Bug-Url: https://bugzilla.redhat.com/1186737 Signed-off-by: [email protected] <[email protected]> --- M packaging/dbscripts/vds_groups_sp.sql 1 file changed, 3 insertions(+), 5 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/26/37526/1 diff --git a/packaging/dbscripts/vds_groups_sp.sql b/packaging/dbscripts/vds_groups_sp.sql index 6cf1cd3..19df932 100644 --- a/packaging/dbscripts/vds_groups_sp.sql +++ b/packaging/dbscripts/vds_groups_sp.sql @@ -297,11 +297,9 @@ Create or replace FUNCTION GetHostsAndVmsForClusters(v_vds_group_ids UUID[]) RETURNS SETOF host_vm_cluster_rs STABLE AS $procedure$ BEGIN - RETURN QUERY SELECT groups.vds_group_id,COUNT(DISTINCT vds.vds_id) as host_count,COUNT(DISTINCT vms.vm_guid) as vm_count - FROM vds_groups groups - LEFT JOIN vm_static vms on vms.vds_group_id = groups.vds_group_id and vms.entity_type::text = 'VM'::text - LEFT JOIN vds_static vds on vds.vds_group_id = groups.vds_group_id - WHERE groups.vds_group_id = any(v_vds_group_ids) + RETURN QUERY SELECT groups.vds_group_id,(select COUNT(DISTINCT vds.vds_id) from vds_static vds where vds.vds_group_id = groups.vds_group_id) as host_count,(select COUNT(DISTINCT vms.vm_guid) from vm_static vms where vms.vds_group_id = groups.vds_group_id and vms.entity_type::text = 'VM'::text) as vm_count + FROM vds_groups groups + WHERE groups.vds_group_id = any(v_vds_group_ids) GROUP BY groups.vds_group_id; END; $procedure$ LANGUAGE plpgsql; -- To view, visit http://gerrit.ovirt.org/37526 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I185e157bb00596c10449b637bf0c9a2c3b9d21e5 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Liran Zelkha <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
