SA-23043

Summary

Performance issues related to volume of data occurred in pre-production server configuration. Some actions in Pega Marketing portal freeze after adding approximately 112 million users. These actions include opening Audience landing page, creating Segment, and opening and refreshing Campaign landing page. The portal freezes from two to five minutes.

Error Messages

Not Applicable

Steps to Reproduce

1. Insert approximately 100 million rows in custom table.
2. Create index on primary key.
3. Explain (Analyze) select count (CustomerId) from table.
4. Check estimated time and navigate over Pega Marketing portal.

Root Cause

A defect in Pegasystems’ code or rules. This is a known issue with PostgreSQL. Refer the page on Slow Counting from PostgreSQL wiki page. Pega Marketing uses count(*) in Audience tab loading, which scans the table completely and takes time to complete.
A poor performance of Connect-Sql rule "RULE-CONNECT-SQL RULE-OBJ-REPORT-DEFINITION-SEGMENT ORACLE!COUNTROWS #20150910T222420.514 GMT" was identified. It executes the query "select count(1) from {class:CustomerData}" that takes a lot of time to execute in PostgreSQL DB.

Resolution

Perform the following local-change:
1. Update the query to use Estimated count feature of PostgreSQL.
2. Save as the Connect SQL rule “Rule-Obj-Report-Definition-Segment • oracle • CountRows” to local ruleset, and update the Open SQL query as follows:
SELECT reltuples::BIGINT as ".pyValue" FROM pg_class WHERE oid = '{class: {.pyName}}'::regclass;
3. Run Analyze and Vacuum commands whenever updating Pega Marketing custom table to keep estimated count and count in synchronization.