Issue Description

Recently a few Pega Cloud®  clients, who use descendant class queries extensively in their report definitions, intermittently encountered performance issues following their DB update to Postgres version 12 (PG12) or higher. Clients experienced report definition time out and slowness accessing their Pega application.

Client-managed cloud and on-premises clients running with non-Postgres or Postgres versions below 12 have not reported this issue.

Root Cause

From Postgres 12 onwards (PG14 included), query plan behavior has changed, which caused the query to run in a nested loop when ‘Include all descendant classes’ checkbox is enabled in the Data Access tab of report definition.

The following is an example of a typical descendant class query causing the issue:

WITH APP_RULESETS ("PZRULESETNAME") AS (SELECT "PC0AR"."PZRULESETNAME" AS "PZRULESETNAME" FROM PR_SYS_APP_HIERARCHY_FLAT "PC0AH" INNER JOIN PR_SYS_APP_RULESET_INDEX "PC0AR" ON (("PC0AH"."PZAPPHASH" = "PC0AR"."PZAPPHASH")) WHERE ("PC0AH"."PZTOPAPPHASH" = $1)), APP_CLASSES ("PXLEAFCLASSNAME") AS (SELECT "CA"."PXLEAFCLASSNAME" AS "PXLEAFCLASSNAME" FROM PR_SYS_CLASS_ANCESTORS "CA" WHERE ("CA"."PXCLASSNAME" = $2)) SELECT "AC"."PXLEAFCLASSNAME" AS "PXLEAFCLASSNAME" FROM PR4_BASE "R" INNER JOIN APP_CLASSES "AC" ON (("R"."PYCLASSNAME" = "AC"."PXLEAFCLASSNAME")) INNER JOIN APP_RULESETS "AR" ON (("R"."PYRULESET" = "AR"."PZRULESETNAME")) WHERE ("R"."PYCLASSTYPE" = $3)

Note: The query varies depending on the report definition and the descendancy being used. We have observed queries starting with "with app_rulesets.“ causing performance issues for a few clients following their DB update.

Resolution

Pega has provided the  following HFIXs to resolve the issue:

Note: We are providing HFIXs for versions 8.5.6, 8.6.6 and above only.

If you are a Pega Cloud client, your Pega Cloud® environments, running on any of the above mentioned Pega Platform versions, are being proactively remediated by Pega. Cloud Maintenance (CM) tickets are being created for each of your environments to provide the schedule of when the hotfixes will be applied.

If you are a United States Pega Cloud for Government (PCFG) client, SR tickets are being created that will provide the relevant hotfixes for you to apply to your PCFG environments.

A system restart, by the Pega Cloud team, will then be required for the hotfixes to take effect.

If you are a client-managed cloud or on–premises client and running with Postgres 12 or above, please determine the appropriate hotfix ID, and submit a request for an Existing Hotfix using My Support Portal. 

As always, be sure you have appropriate backups in place before applying the hotfixes. Note that a system restart will be required for the hotfixes to take effect. 

It is very important to keep your Pega systems current on the latest patch releases.  Applying the latest patch release provides the latest, important security, supportability and reliability improvements and bug fixes since the last minor release. 

If you have any questions or concerns, submit a Support ticket with Global Client Support in My Support Portal for assistance. 

Frequently Asked Questions