DS Repo performance issues with large number of role members

We have a customer testing IDM 6.5.0.3 with DS as repo. They are testing roles with a large number of users (up to 40k users in a role - for multiple roles) - and although this approach is questionable from a solution perspective - the performance of the underlying DS as opposed to using PostgresSQL seems to be dramatically different.

Testing with 4 roles each with 40k members - it seems to be at least 10x less performant at this level and would probably degrade further with more entries in the relationship OU (which in my test env has approximately 400k entries). The issue seems to be that once a role has greater than the index entry limit (4k) members, it will result in an unindexed search on the relationship table and depending on the number of entries in the relationship table - longer and longer response times.

When using DS as the repo, the search filter when retrieving a role is the following: 

(|(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceCollection eq "managed/role")
    (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceId eq "a9b9855d-c3cf-4996-b2cf-a9cb6de3975e")
    (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstPropertyName eq "members"))
  (&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceCollection eq "managed/role")
    (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceId eq "a9b9855d-c3cf-4996-b2cf-a9cb6de3975e")
    (fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondPropertyName eq "members")))

As the index has > 4k members this results in an unindexed search.

Note that this will be the case for any relationship that has >4k relations - which may be quite common depending on IDM object model design. 

This would seem to make DS as a repo unscalable in these situations.

A backend stat of this environment can be found here:
https://drive.google.com/file/d/1VgDwKjYSzZeYtNT7NWyEYDvjSa-_1puI/view?usp=sharing

Testing

Role with 40k members:

IDM access log:

,"server":{"ip":"192.168.1.82","port":8443},"http":{"request":

,"headers":

,"cookies":{}}},"request":{"protocol":"CREST","operation":"QUERY"},"eventName":"access","userId":"openidm-admin","response":

,"timestamp":"2020-10-09T09:29:56.014Z","_id":"999d5420-e01a-444c-9609-565f1867a746-128211”}

DS ldap access log:

,"server":{"ip":"127.0.0.1","port":31636},"request":{"protocol":"LDAPS","operation":"SEARCH","connId":0,"msgId":43,"dn":"ou=relationships,dc=openidm,dc=forgerock,dc=com","scope":"one","filter":"(|(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceId eq \"a9b9855d-c3cf-4996-b2cf-a9cb6de3975e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstPropertyName eq \"members\"))(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceId eq \"a9b9855d-c3cf-4996-b2cf-a9cb6de3975e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondPropertyName eq \"members\")))","attrs":["objectClass","uid","etag","createTimestamp","modifyTimestamp","fr-idm-relationship-json"]},"transactionId":"999d5420-e01a-444c-9609-565f1867a746-128175/0","response":

,"timestamp":"2020-10-09T09:29:56.012Z","_id":"2b2550d1-aad2-4a54-bb3b-9f1b4cce88cf-144”}

Role with 2732 members (under index entry limit):

IDM access log:

,"server":{"ip":"192.168.1.82","port":8443},"http":{"request":

,"headers":

,"cookies":{}}},"request":{"protocol":"CREST","operation":"QUERY"},"eventName":"access","userId":"openidm-admin","response":

,"timestamp":"2020-10-09T09:31:13.089Z","_id":"999d5420-e01a-444c-9609-565f1867a746-128385"}

DS LDAP access log:

,"server":{"ip":"127.0.0.1","port":31636},"request":{"protocol":"LDAPS","operation":"SEARCH","connId":1,"msgId":108,"dn":"ou=relationships,dc=openidm,dc=forgerock,dc=com","scope":"one","filter":"(|(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstResourceId eq \"de581a16-cc91-4cac-84c3-d6c29af4228e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/firstPropertyName eq \"members\"))(&(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceCollection eq \"managed/role\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondResourceId eq \"de581a16-cc91-4cac-84c3-d6c29af4228e\")(fr-idm-relationship-json:caseIgnoreJsonQueryMatchRelationship:=/secondPropertyName eq \"members\")))","attrs":["objectClass","uid","etag","createTimestamp","modifyTimestamp","fr-idm-relationship-json"]},"transactionId":"999d5420-e01a-444c-9609-565f1867a746-128691/0","response":

,"timestamp":"2020-10-09T09:33:32.955Z","_id":"2b2550d1-aad2-4a54-bb3b-9f1b4cce88cf-728"}

PostgreSQL comparison:

IDM access log (role has 40k members, and relationship table has 400k entries):

,"server":{"ip":"192.168.1.82","port":8443},"http":{"request":

,"headers":

,"cookies":{}}},"request":{"protocol":"CREST","operation":"QUERY"},"eventName":"access","userId":"openidm-admin","response":{"status":"SUCCESSFUL","statusCode":null,"elapsedTime":2174,"elapsedTimeUnits":"MILLISECONDS"},"timestamp":"2020-10-09T09:39:40.519Z","_id":"01003720-1d31-4158-984a-4f0b8be122f1-748706"}