[Bodington-developers] Performance of ACL Display

[Matthew B. <mat...@co...>]

The new ACL display that is used here at Oxford can be a little slow 
when lots of groups are involved. We only see these issues when running 
on a large dataset. Attached is a rough SQL trace of where the 
bottlenecks are when trying to display an ACL page.

SELECT * FROM objects WHERE id IS NULL

This is the query that is run when a connection is validated from the 
connection pool. On PostgreSQL you cannot have an index containing NULLs 
and so this results in a full table scan:

vle0=> explain SELECT * FROM objects WHERE id IS NULL;
                           QUERY PLAN
--------------------------------------------------------------
  Seq Scan on objects  (cost=0.00..20115.56 rows=6750 width=8)
    Filter: (id IS NULL)

Changing the the validation SQL can result in a much faster check:

vle0=> explain SELECT * FROM objects WHERE id = 0;
                                 QUERY PLAN
--------------------------------------------------------------------------
  Index Scan using pk_objects on objects  (cost=0.00..6.01 rows=2 width=8)
    Index Cond: (id = 0)
(2 rows)

The other big killer is the calculation of the size of the "special" groups:

SELECT count(*) FROM objects, users WHERE type = { } AND id = user_id 
AND ( special_groups_a & ({ }) <> { } )

One option would be to special case the ACL code of that it always 
assumes the special groups are too big (easy and quick).

The other option is to replace the special groups with normal ones.

NB: Changing the connection check and making sure special groups are 
never expanded changes the acl display page load time from around 4 
seconds to less than half a second.

-- 
+--Matthew Buckett-----------------------------------------+
| VLE Developer, Learning Technologies Group               |
| Tel: +44 (0) 1865 283660 http://www.oucs.ox.ac.uk/       |
+------------Computing Services, University of Oxford------+