Grouper Developers Forum

[Robert Bradley <>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

On 17/04/2019 00:37, Gettes, Michael wrote:
| Carey, do you have a large number of composite groups?
| Grouper_composites.type isn’t indexed.  You could try and index it
| and see if that speeds things up.
|
| Additionally, the query has a problem which I have already
| identified in GRP-2080 where “exists” in MySQL servers can be a wee
| bit slow. Rewritten (in this case) as “and not (1 in (select 1…”
| would yield much faster response and works equally well on Oracle.
| Seems like we need to have an evaluation of all uses of exists in
| queries.  Also, the "subject_source <> ‘g:gsa’" should be rewritten
| as “not subject_source = ‘g:gsa’”.
|
| Exactly where in grouper this query is being done - I am not sure
| but this is what I recommend as a fix for us MySQL users.

This sounds a lot like the issues I saw with Grouper and Postgres 9.1
a while back:

~ https://lists.internet2.edu/sympa/arc/grouper-users/2014-11/msg00014.ht
ml

If building from source is an option, I'd try applying the changes
from that post and see what difference (if any) it makes.  Extra
indexes are probably a good thing too.  My extra indexes for this
particular query currently look like:

grouper_memberships:
- - (owner_id, field_id)
- - (member_id, enabled, owner_id, field_id)

If the problem's with an actual "union" composite group though, I'd
try replacing it with a normal group with two group members.

- -- 
Dr Robert Bradley
Identity and Access Management Team, IT Services, University of Oxford
-----BEGIN PGP SIGNATURE-----

iQIzBAEBCAAdFiEEgF3NFfO9FqlA+ME+lGGnynav474FAly3R14ACgkQlGGnynav
477D9w//SnliQPn+MRK78cdZqwPS6h8AA4uo/DlLCXkzDOouIe59VL2C2gK2wYCi
VjCztnGdkYckBmbpphfJT6wSzRCSfE1FmRVsNIoi3YmIUhhDn7aVX41Pc0XKJzxD
U+3Oy3QlH0f08L6G9qfA9HaotRntKyscZd5DSyOdc5cH/Ds/oNTRr9XvkjizX3za
M82h5CdIJR9LOU4tIdAAZRivEZKsA0tVwqIQMZZKXkd8UzBiq5EU4RaqxlNZqx0P
WuWcYnRIAlVBQV0DfMET/9WkkrpCU17xsZqrgHoUKyNOoTsu2Nb6PmeKrv0F347E
Y0GhMxuzqh1l3lqHMIffans1/U2iBb8DLUeU1aFcljc/SO5k/rFDgaCv60skbF8M
zLn5nbPAi/jUM7kkQ5754DsU4tYVwsuzxB7DO2vts+z1UjwWBnyaD5jzPByjDMHz
UbnKGO/NZyancokyjXhi1rE9WmR1afW8KWC1u9hkG02i5d46U4pzsLbKpFYczFhI
jo5vD2UX+jeI+BsApIm8NcaHDWJnDI7mPccNDQaWgxYpXJ8ofnlWA9jh+QbD13jQ
i3oSDINgqTWJMRvKxi8kjg5Na9+jZbVOLETES7jJnNtLnt6Asv1P63ivJJmvkyYp
Z+zl2Zd2ePZNIZBrNlMh5A5WZmmnH7kSLhKLQD49VmI53oFgcy0=
=JOq2
-----END PGP SIGNATURE-----