Upgrading permissions for SO 6.2 to 7.0 causes large performance regression to verify process

• Type: Regression Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.1 CE GA2
• Fix Version/s: 6.2.X EE, 7.0.0 DXP FP2, 7.0.0 DXP SP1, 7.0.3 CE GA4, Master
• Component/s: Collaboration, Collaboration > Upgrade
• Labels:
  • QA-NA
  • liferay-digital-enterprise-70-sp1
  • liferay-fixpack-de-2-7010
  • liferay-fixpack-portal-131-6210
• Branch Version/s:
  7.0.x, 6.2.x
• Backported to Branch:
  Committed
• Fix Priority: 5
• Databases:
  Oracle Database 11.2
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/41665

When attempting to execute the database upgrade for a client sample database going from 6.1 to 7.0, we got "stuck" in the following call:

16:59:39,118 INFO  [main][LoggingTimer:70] Starting com.liferay.portal.verify.VerifyPermission#deleteConflictingUserDefaultRolePermissions

Execution of the related query has been running for over 48 hours (and potentially 80 more to go), and therefor not reasonable for the upgrade.

Query currently running:

SELECT resourcePermission1.resourcePermissionId
FROM ResourcePermission resourcePermission1
INNER JOIN ResourcePermission resourcePermission2
ON resourcePermission1.companyId = resourcePermission2.companyId
AND resourcePermission1.name     = resourcePermission2.name
AND resourcePermission1.scope    = resourcePermission2.scope
AND resourcePermission1.primKey  = resourcePermission2.primKey
INNER JOIN Layout
ON resourcePermission1.companyId = Layout.companyId
AND resourcePermission1.primKey LIKE REPLACE('[$PLID$]_LAYOUT_%', '[$PLID$]', CAST(Layout.plid AS VARCHAR(4000)))
AND Layout.type_ = 'portlet'
INNER JOIN Group_
ON Layout.groupId                   = Group_.groupId
WHERE resourcePermission1.companyId = 10154
AND resourcePermission1.roleId      = 10164
AND resourcePermission2.roleId      = 10165
AND resourcePermission1.scope       = 4
AND (Group_.classNameId             = 10005
OR Group_.classNameId               = 10006)

Attached are screen shots of query + execution plan, and upgrade log file.