Performance Optimization needed for view cmn_lic_users_v in PostgreSQL Database

Products

• Clarity PPM SaaS

Issue/Introduction

Performance of view cmn_lic_users_v is bad in PostgreSQL Database 

Steps to Reproduce 

Pre Requisite: The performance degradation of the view  cmn_lic_users_v is observed when you have large data in table cmn_sec_assgnd_obj_perm . This defect was found when testing for a dataset where table cmn_sec_assgnd_obj_perm  contained 383304465 records 

1. Run the below reports with large customer database 
 a. User License and Activity
 b. User License and Activity Detail

2. You can also navigate to Administration --> License Information

 

Expected Results: The output of reports should be quick or within reasonable time 

Actual Results: The reports takes long time and huge TEMP space utilization at the database is observed 

 

Environment

Release : 15.9 and above with PostgreSQL database only 

Component : CA PPM SAAS INSTALLATION AND UPGRADE

Cause

This is logged as defect (DE59583) and currently in investigation with out engineering team. 

Resolution

No workaround found so far.