Forum OpenACS Development: improving permission queries

On large installations, permission checking might become a performance bottleneck. We experienced this some time ago with 1000+ concurrent users in learn@wu. The following index helped us to speed permission queries by more than a factor of 10.

create index acs_permissions_object_id_idx on acs_permissions(object_id);

i have added this to CVS head (sql creation procs, and update for oracle and postgres).

there are couple of indices already on the various permission related tables, but not that one. When the server is getting to its limits, db-queries that take under "sunshine conditions" 2ms will take sometimes 2 or more seconds. I would think that for most rather low traffic installations, this index does not matter. For us it did, so i reported it back.

It is not a bug, the functional behavior is exactly the same like before. Every trained oacs admin can feed the single line into her database. But be aware that the database upgrade script will barf on upgrading upgrade-5.3.0b1-5.3.0b2.sql
I found no simple and proven way to create indices conditionally on postgres and oracle.

-gustaf neumann
PS: the actual upgrade scripts were missing at the time of my original post two days ago.

Here is the oracle version, tested on 10g.

declare v_exists integer;

begin
select count(*) into v_exists from user_indexes where lower(index_name)='acs_permissions_object_id_idx';
if v_exists = 0 then
    execute immediate 'create index acs_permissions_object_id_idx on acs_permissions(object_id)';
end if;

end;
/
show errors

done