Poor performance of site load with many users

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.X, 7.1.X, Master
• Fix Version/s: 7.0.0 DXP FP85, 7.0.10.12 DXP SP12, 7.0.X, 7.1.10 DXP FP13, 7.1.X, 7.2.X, 7.2.1 CE GA2, 7.3.10 DXP GA1, Master
• Component/s: Performance, Sites Administration > Sites
• Labels:
  • 7.2-should
  • ee-ts
  • liferay-dxp-70-sp12
  • liferay-fixpack-de-85-7010
  • liferay-fixpack-dxp-13-7110
• Branch Version/s:
  7.2.x, 7.1.x, 7.0.x
• Backported to Branch:
  Committed
• Fix Priority: 4
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/73853

Steps to reproduce the issue:
1) create 1 Million+ users and make them site-member of site guest
2) With an admin user (from control panel) go to "sites/ sites"

Expected result: the system shows the list of sites
Actual result: the page takes a very long time to be load

Even with applying the properties:

table.mapper.cacheless.mapping.table.names=\ 
Users_Orgs,\ 
Users_Roles,\ 
Users_Teams,\ 
Users_UserGroups

If the above properties are applied, the first load is slow 'only', and the next ones are occurring fast.

The issue seems to be due to

locked <0x00000000a3086968> (a oracle.jdbc.driver.T4CConnection)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
	at com.liferay.portal.dao.jdbc.spring.MappingSqlQueryImpl.execute(MappingSqlQueryImpl.java:72)
	at com.liferay.portal.kernel.service.persistence.impl.TableMapperImpl.getPrimaryKeys(TableMapperImpl.java:473)
	at com.liferay.portal.kernel.service.persistence.impl.TableMapperImpl.getRightPrimaryKeys(TableMapperImpl.java:325)
	at com.liferay.portal.service.persistence.impl.GroupPersistenceImpl.getUsersSize(GroupPersistenceImpl.java:14164)
	at com.liferay.portal.service.base.UserLocalServiceBaseImpl.getGroupUsersCount(UserLocalServiceBaseImpl.java:584)

Additional information:
This issue seems to be due to the API call com.liferay.portal.service.base.UserLocalServiceBaseImpl.getGroupUsersCount
and com.liferay.portal.service.persistence.impl.GroupPersistenceImpl.getUsersSize

the code seems to do this:
long[] pks = groupToUserTableMapper.getRightPrimaryKeys(pk);
return pks.length;

this means that it gets all (1000.000+) userIds and than get the size while a "SELECT count(userId)" would be much faster.