These are chat archives for ManageIQ/manageiq/performance

14th
Apr 2016
Keenan Brock
@kbrock
Apr 14 2016 15:34
end result - yes, this is a problem in capablanca and botvinnik
@matthewd ManageIQ/manageiq#7847 do you want to get that in, or should I add the 2 final nails that close that one out?
Chris Arcand
@chrisarcand
Apr 14 2016 15:42
@kbrock :confused: I’m kind of blown away by that. Does no one use this feature at all? Surely I’m doing something atypical, but I don’t see how… cc/ @gtanzillo and @jrafanie for finding the convo from yesterday later.
Keenan Brock
@kbrock
Apr 14 2016 20:14
@matthewd this is what the tagging query will look like, any last thoughts?
SELECT "hosts".id
FROM "hosts"
WHERE (exists(select *
       from "taggings"
       where "taggings"."taggable_id" = "hosts"."id"
       AND "taggings"."taggable_type" = 'Host'
       AND "taggings"."tag_id" IN (1385)))
and ((select count(*)
       from "taggings"
       where "taggings"."taggable_id" = "hosts"."id"
       AND "taggings"."taggable_type" = 'Host'
       AND "taggings"."tag_id" IN (1385)) = 1)
AND (exists(select *
       from "taggings"
       where "taggings"."taggable_id" = "hosts"."id"
       AND "taggings"."taggable_type" = 'Host'
       AND "taggings"."tag_id" IN (1387)))
and ((select count(*)
       from "taggings"
       where "taggings"."taggable_id" = "hosts"."id"
       AND "taggings"."taggable_type" = 'Host'
       AND "taggings"."tag_id" IN (1387)) = 1)
still working through arel on this one
Matthew Draper
@matthewd
Apr 14 2016 20:18
You only need the count when you have more than one id
Keenan Brock
@kbrock
Apr 14 2016 20:22
yes, agreed
I was lazy in my sql example :)
only needed for AND, and > 1
Matthew Draper
@matthewd
Apr 14 2016 20:24
And only if you choose (read: it's more performant) to implement it with a single subquery lookup. You could implement it as above, as just a series of ANDed single-id exists checks.
Keenan Brock
@kbrock
Apr 14 2016 20:24
ooh
single join
Matthew Draper
@matthewd
Apr 14 2016 20:25
I would expect it to perform better, assuming the relevant index on taggable_*… but from what you said earlier, maybe that's not the case?
Keenan Brock
@kbrock
Apr 14 2016 20:26
yea, so for the [[1],[2],[3],[4],[5]]. it may be more performant for 5 exist clauses (or straight forward joins) than an exist and an all
Matthew Draper
@matthewd
Apr 14 2016 20:27
My original expectation was that [[a], b, [c,d], [e,f]] -> count(IN (a,b)) = 2 AND exists(IN (c,d)) AND exists(IN (e,f))
Oh, right, I've just worked out why the count is slow
Matthew Draper
@matthewd
Apr 14 2016 20:32
So yeah, ignore me, and try for an all-exists formulation
The only time you need the count is if you want N matches where 1 < N < all
Keenan Brock
@kbrock
Apr 14 2016 20:33
+1
Matthew Draper
@matthewd
Apr 14 2016 20:33
(and that's not something our syntax allows us to request)
Keenan Brock
@kbrock
Apr 14 2016 20:33
for all exists - do you want to convert into 5 joins?
Matthew Draper
@matthewd
Apr 14 2016 20:34
I see no benefit to doing so
Keenan Brock
@kbrock
Apr 14 2016 20:34
vs the count?
Matthew Draper
@matthewd
Apr 14 2016 20:34
If the DB's doing its job, their plans should be nigh indistinguishable anyway
vs the exists
Keenan Brock
@kbrock
Apr 14 2016 20:43
aah
Matthew Draper
@matthewd
Apr 14 2016 20:43
Actually, correction: the exists will perform better
Keenan Brock
@kbrock
Apr 14 2016 20:44
yes, I was saying that if it is all, then add multiple exists vs a single exists with a single count
Matthew Draper
@matthewd
Apr 14 2016 20:44
.. because you're likely to end up with enough joins that you hit the "stop optimizing this" limit
Keenan Brock
@kbrock
Apr 14 2016 20:44
thanks
Matthew Draper
@matthewd
Apr 14 2016 20:44
:+1:
Keenan Brock
@kbrock
Apr 14 2016 20:44
:(
Matthew Draper
@matthewd
Apr 14 2016 20:44
^ actual JOINs
Keenan Brock
@kbrock
Apr 14 2016 20:44
+1
exists= JOIN
Matthew Draper
@matthewd
Apr 14 2016 20:45
~= :)
Keenan Brock
@kbrock
Apr 14 2016 20:45
+1
Keenan Brock
@kbrock
Apr 14 2016 22:57
so close