Forum OpenACS Development: Re: OpenACS Permissions System

Don,

Thanks for looking that over and posting comments. I will re-run the benchmark tests wrapping the query in exists() and will post the results. I took the sample query from existing code and it neither selected from 'Dual' nor used 'Exists' so it looks as if I chose a bad example.

In between my initial posting and my reading the replies I did some more work which I have posted below. Just to re-iterate, here are my reasons for looking at this again:

1) Although the permissions system works acceptably fast, as it is central to everything, the faster it goes the better.

2) I am going to modify the project manager to have permissions at project and maybe even at task level. That could mean upwards of 20 permissions checks per page or even more. I want to avoid creating performance issues.

3) I understand fully why the heirarchies were not exploded fully into a table, and why a view was used and whilst I fully understand the rationale behind the existing approach, I think that there MAY still be mileage in exploring the alternatives. In particular the method I show below avoids the need for the view altogether - the query refers to the seperate hierarchies without needing a huge de-normalised presentation of the information.

Incidentally, I completely agree that the permissions subtyping is an unnecessary complication. It slows down the permissions queries, adds complexity without adding much of use and confuses users! I would be very keen to eliminate that complexity.

Here is the work I did earlier which I think is still valid since I use 'Limit' in all cases:

#######
STAGE 2
#######

I have done some additional work on this and decided to work on the areas of the query that either still depend on views or show up as relatively expensive in the query plan.

First of all I experimented with basing the query on the table group_element_index instead of the view group_member_index. This in itself did not improve matters which suggests that using a view in this context does not creat any performance issues.

So I then decided to create a table that would hold exactly the information required for a permissions query and nothing else. This table is intended to allow us to ask for a distinct list of group_ids which includes the id of the entity for which permission is sought. Including the party_id of the party itself in the group_id column simplifies the permission query by eliminating the need for an additional 'or' statement.

Creating the table:

select distinct element_id as group_id, element_id into rh_group_struct from group_element_index union select group_id, element_id from group_element_index;

create index rh_group_struct_element_id_idx on rh_group_struct (element_id);

create index rh_group_struct_group_id_idx on rh_group_struct (group_id);

Now the table can be queried using this:

select group_id from rh_group_struct where element_id=1070;

This returns a de-duped list of ancestor ids for the party 1070. It is obviously better to have a de-duped and therefore shorter list for the outer main permissions query to join to.

Now I thought I would try this in the main query:

TEST A-5

explain analyse select 1 from acs_permissions where privilege in (select descendant from acs_privilege_descendant_map where privilege='admin') and object_id in (select ancestor_id from acs_object_context_index where object_id=16631) and grantee_id in (select group_id from rh_group_struct where element_id=448) limit 1;

Total runtime: 1.925 ms

TEST B-5

Total runtime: 1.186 ms

OK, so now we seem to have one query that performs equally quickly in both situations. I think that the main reason for the performance improvement is that the table can now store far more rows on each data page since the only fields are integer key fields. There are no unicode enabled text fields to manage.

I propose that the best way to create this table would be to use the same triggers that initiate the maintenance of the group_element_index table. I plan to experiment with the idea noted above of replacing the text based permissions hierarchy with integer keys to see if further gains in performance can be realised.

For now, whereas before I was concerned that 20 permission controlled projects on a page could have taken 20x500ms (or 20x130ms with Exists or Limit) to check, under this alternative method this would be reduced on this test system to 40ms. I reckon that deserves further exploration.

Further thoughts?

Regards
Richard

Forum OpenACS Development: Re: OpenACS Permissions System - Performance Improvement Work