Extremely slow performance while reading the data from DWH_INV_SECURITY_V using ODATA API in PostgresSQL in comparision with Oracle

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Summary: Extremely slow performance while reading the data from DWH_INV_SECURITY_V using ODATA API in PostgresSQL in comparison with Oracle 

Steps to Reproduce: 

1. Prerequisite to reproduce the problem is to have medium size dataset with around 8+ million record in view DWH_INV_SECURITY_V
2. Login to a system and navigate to Administration --> General Settings --> Datawarehouse ODATA SERVICE
3. Use the DATA WAREHOUSE ODATA V4 ENDPOINT URL and connect via Postman 
4. Perform a GET Call https://cppmbixxx.ondemand.ca.com/api/odata4/cppmxxxxtDataSourceV4/DWH_INV_SECURITY_V

Expected Results: The API get call DWH_INV_SECURITY_V should be able to fetch records at the same time it used to take in Oracle. 

Actual Results: The API get call DWH_INV_SECURITY_V takes too long in PostgresSQL. 

Environment

Release : 15.8.x, 15.9.x

Component : CA PPM SAAS DATA WAREHOUSE

Cause

DE63862 - (DE56560 - marked as a duplicate) 

We have exposed below tables.  Use the below tables for your reporting until the view DWH_INV_SECURITY_V is optimized. 

Resolution

Fixed in Release 16.0.0.

• The DWH_INV_SECURITY_V database view is now optimized. 
• The fix also covers the issue DE56560 reported by another customer.