Assignment

Hi all, feel free to suggest moving this topic under Performance if not suitable here.

We are having performance issues with group assignments grading, view all submissions - view. With normal assignments
the loading times for the view all submissions is few seconds, with group assignments this pumps up to 20 to seconds. We
looked into this and it seems that the view uses a query that takes 682ms to plan and 0.351ms to execute in PostgreSql, but it
runs the query 30 times to create the view -> 30 X 0.6seconds = 18seconds. This seems little excessive.

We do realize that part of the problem is the amount of groups & group members in our system (we generate them automatically for all
courses that have groupings in SMS) :

but this behavior is making the view all - view unusable.

Our environment is load-balanced 6 nodes (VM) with hardware DB (PostgreSql 9.4), yes it's a large installation... Any ideas how to proceed?
Here's the Explain analyse :

EXPLAIN ANALYSE
SELECT u.id FROM mdl_user u JOIN(
        SELECT DISTINCT 
                eu20_u.id 
        FROM 
                mdl_user eu20_u 
        JOIN mdl_user_enrolments ej20_ue ON 
                ej20_ue.userid = eu20_u.id 
        JOIN mdl_enrol ej20_e ON (
                ej20_e.id = ej20_ue.enrolid 
                AND ej20_e.courseid = 16085
        ) 
        JOIN mdl_role_assignments eu19_ra3 ON (
                eu19_ra3.userid = eu20_u.id 
                AND eu19_ra3.roleid IN (5) 
                AND eu19_ra3.contextid IN (1,3,472391,546512)
        )
        JOIN mdl_groups_members gm18_gm ON (
                gm18_gm.userid = eu20_u.id 
                AND gm18_gm.groupid = 35335
        ) 
        WHERE 1 = 1 AND (eu20_u.id <> 1) AND eu20_u.deleted = 0
) je ON 
        je.id = u.id 
WHERE 
        u.deleted = 0 
ORDER 
        BY u.lastname, u.firstname, u.id;

                                                                                               QUERY PLAN                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=30.62..30.63 rows=1 width=23) (actual time=0.248..0.248 rows=4 loops=1)
   Sort Key: u.lastname, u.firstname, u.id
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop  (cost=26.56..30.61 rows=1 width=23) (actual time=0.225..0.231 rows=4 loops=1)
         ->  HashAggregate  (cost=26.27..26.28 rows=1 width=8) (actual time=0.222..0.223 rows=4 loops=1)
               Group Key: eu20_u.id
               ->  Nested Loop  (cost=1.85..26.27 rows=1 width=8) (actual time=0.056..0.217 rows=4 loops=1)
                     ->  Nested Loop  (cost=1.56..25.94 rows=1 width=16) (actual time=0.043..0.118 rows=32 loops=1)
                           ->  Nested Loop  (cost=1.14..24.75 rows=1 width=24) (actual time=0.036..0.068 rows=4 loops=1)
                                 ->  Nested Loop  (cost=0.84..20.60 rows=1 width=16) (actual time=0.030..0.051 rows=4 loops=1)
                                       ->  Index Scan using mdl_groumemb_gro_ix on mdl_groups_members gm18_gm  (cost=0.42..9.47 rows=4 width=8) (actual time=0.011..0.011 rows=4 loops=1)
                                             Index Cond: (groupid = 35335)
                                       ->  Index Only Scan using mdl_roleassi_useconrol_ix on mdl_role_assignments eu19_ra3  (cost=0.42..2.77 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=4)
                                             Index Cond: ((userid = gm18_gm.userid) AND (roleid = 5))
                                             Filter: (contextid = ANY ('{1,3,472391,546512}'::bigint[]))
                                             Rows Removed by Filter: 7
                                             Heap Fetches: 7
                                 ->  Index Scan using mdl_user_id_pk on mdl_user eu20_u  (cost=0.29..4.13 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4)
                                       Index Cond: (id = eu19_ra3.userid)
                                       Filter: ((id <> 1) AND (deleted = 0))
                           ->  Index Scan using mdl_userenro_use_ix on mdl_user_enrolments ej20_ue  (cost=0.42..1.06 rows=13 width=16) (actual time=0.003..0.011 rows=8 loops=4)
                                 Index Cond: (userid = eu20_u.id)
                     ->  Index Scan using mdl_enro_id_pk on mdl_enrol ej20_e  (cost=0.29..0.32 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=32)
                           Index Cond: (id = ej20_ue.enrolid)
                           Filter: (courseid = 16085)
                           Rows Removed by Filter: 1
         ->  Index Scan using mdl_user_id_pk on mdl_user u  (cost=0.29..4.31 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=4)
               Index Cond: (id = eu20_u.id)
               Filter: (deleted = 0)
 Planning time: 682.850 ms
 Execution time: 0.351 ms
(31 rows)