We have an issue with our production Postgres database and IG performance. At the end of 2017 we ran two major enterprise reviews and the key issues we heard from end users had to do with performance. In some cases the time to login could take up to a minute or the time to load an entitlement description within a review could take up to two minutes. We also had a couple instances where during a live production review the IG database needed to be restarted because users could not login.

Currently I believe the issue has to do with the database and not IG / Tomcat. In testing I have Tomcat turned off and simple queries run from pgAdmin either take 40+ minutes or don't resolve at all. For example in pgAdmin if I run the query below it takes about 40 minutes until I get the data back (where as in our dev environment it takes about 2 seconds)

SELECT instance_id, name, description, review_type, next_schedule_date, review_target_spec_json, review_inst_owner_display_name, status, status_comment, start_date, expected_end_date, end_date, valid_to_date, canceled_by, task_count, task_complete_count, canceled_by_name, item_count, reviewer_count, assign_count, keep_count, remove_count
FROM public.access_review_summary_v;

We've tried tuning PostGres with the postgresql.conf file with minor success, maybe 10-15% improvement in performance but nowhere close to where we need it. Here are some additional details:

IG Version: 2.5.1
OS: Windows 2012 R2
Servers: 1 IG / OSP Server and 1 Postgres Server
Hardware: 16GB Ram, 2 Processors, 2.6GHz (on each server), 100GB hardrive with 24GBs used currently

Any suggestions would be greatly appreciated!