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
Defect DE63862
Release : 15.8.x, 15.9.x, 16.x
Component : CA PPM SAAS DATA WAREHOUSE