PostgreSQL - MUX Reporting - large report performance with Instance rights

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

In MUX Reporting, a very large report was created on a custom data provider, with security on Investment. When admin user is accessing it, it opens fast, however for a user with instance permissions, it's spinning and never returns the results.

Environment

Clarity with PostgreSQL

Cause

Adjustment needed to database settings 

Resolution

1. Check the below settings on the PostgreSQL database:
   • work_mem
   • join_collapse_limit
   • set default_statistics_target
2. Ensure they are set to at least the following
   1. work_mem = 32
   2. join_collapse_limit = 10
   3. set default_statistics_target = 1000;
3. Flush caches

Alternate workaround:

1. If you are not needing the Investment Security on this report, you may consider disabling it in the data provider
2. Open Reporting - Data Providers - select the provider - Security - Do you want to secure this Data Provider - uncheck this
3. Publish