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.
Release : 15.8.x, 15.9.x
Component : CA PPM SAAS DATA WAREHOUSE
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.
Fixed in Release 16.0.0.