User XOG Performance issue -- Postgres Only

Products

• Clarity PPM SaaS

Issue/Introduction

Performance Issue with User XOG on Clarity SaaS. Validated further and found that CMN_SEC_USER_GROUPS_INS_SP takes more than 5 mins for each execution Postgres is using a bad execution plan for the same query and different parameters. 

Steps to Reproduce: 

1. Use XOG Client 
2. XOG an user to Clarity SaaS/Postgres DB environment running with Groups tag like below: 

<Groups complete="true"> 
        <Group id="ApplAdminRl"/> 
        <Group id="FINANCIAL_MANAGER"/> 
        <Group id="baseUserGroup"/> 
</Groups> 

Expected Results: The XOG request completes within normal amount of time. 

Actual Results: The XOG request takes more than 15 minutes to complete. 

Cause

DE62304  

Workaround: We did a workaround locally using a modified SP which runs faster. However the most time on the execution plan seems to be caused by the NOT EXISTS clause. 

Environment

Release 15.9.2, 15.9.3 

Resolution

Fixed in Release 16.0.0