Hello Tim Speetjens,
I'd like you to do a code review. Please visit
http://gerrit.ovirt.org/34387
to review the following change.
Change subject: userportal: optimize GetAllFromVms query
......................................................................
userportal: optimize GetAllFromVms query
Rewrote the stored function to optimize the query inside GetAllFromVms
Splitting out the boolean for simplicity
Joining instead of using exists on a complicated view
Removing distinct, as it's already inside the joined view
Change-Id: I541c9172c3ba9bcf989f9660baf06ec2e1fd02d7
Bug-Url: https://bugzilla.redhat.com/1151477
Signed-off-by: Tim Speetjens <[email protected]>
---
M packaging/dbscripts/vms_sp.sql
1 file changed, 11 insertions(+), 6 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/87/34387/1
diff --git a/packaging/dbscripts/vms_sp.sql b/packaging/dbscripts/vms_sp.sql
index 4331011..83dc5b5 100644
--- a/packaging/dbscripts/vms_sp.sql
+++ b/packaging/dbscripts/vms_sp.sql
@@ -851,12 +851,17 @@
Create or replace FUNCTION GetAllFromVms(v_user_id UUID, v_is_filtered
boolean) RETURNS SETOF vms STABLE
AS $procedure$
BEGIN
-RETURN QUERY SELECT DISTINCT vms.*
- FROM vms
- WHERE (NOT v_is_filtered OR EXISTS (SELECT 1
- FROM user_vm_permissions_view
- WHERE user_id = v_user_id AND entity_id
= vm_guid))
- ORDER BY vm_guid;
+IF v_is_filtered THEN
+ RETURN QUERY SELECT vms.*
+ FROM vms INNER JOIN user_vm_permissions_view ON vms.vm_guid =
user_vm_permissions_view.entity_id
+ WHERE user_id = v_user_id
+ ORDER BY vm_guid;
+ELSE
+ RETURN QUERY SELECT DISTINCT vms.*
+ FROM vms
+ ORDER BY vm_guid;
+END IF;
+
END; $procedure$
LANGUAGE plpgsql;
--
To view, visit http://gerrit.ovirt.org/34387
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I541c9172c3ba9bcf989f9660baf06ec2e1fd02d7
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-engine
Gerrit-Branch: ovirt-engine-3.5
Gerrit-Owner: Oved Ourfali <[email protected]>
Gerrit-Reviewer: Tim Speetjens <[email protected]>
_______________________________________________
Engine-patches mailing list
[email protected]
http://lists.ovirt.org/mailman/listinfo/engine-patches
