Forum OpenACS Development: Re: acs_object_

It looks like for things that have context_id null using a sequence to generate the root tree_sortkey gives roughly an order of magnitude speedup (and for context_id -2 I found a similiar result).

You have to add a sequence as well as a "max_leaf" column so that you can generate the root tree_sortkey when context_id is null; that or create a root object that would always exist -- which I guess is what security_context_root is.

The question is is it preferable to force everything to be a child of security_context_root or add a sequence?

Adding the sequence and extra column are straightforward (and if anything substantially improve the locking issues since the resulting queries are so much faster). here was the new acs_objects_insert_tr that I used with a sequence to generate the root context tree_sortkey.

create sequence acs_object_root_sortkey;
select setval('acs_object_root_sortkey',
  (select max(tree_leaf_key_to_int(tree_sortkey)) 
   from acs_objects 
   where context_id is null));

create or replace function acs_objects_insert_tr () returns opaque as '
declare
        v_parent_sk     varbit default null;
        v_max_value     integer;
begin
        if new.context_id is null or new.context_id = -2 then 
	    select nextval(''acs_object_root_sortkey'') into v_max_value from dual;
        else 
            select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value 
              from acs_objects 
             where context_id = new.context_id;
            select tree_sortkey into v_parent_sk 
              from acs_objects 
             where object_id = new.context_id;
        end if;

        new.tree_sortkey := tree_next_key(v_parent_sk, v_max_value);

        return new;
end;' language 'plpgsql';

(the context_id = -2 bit is a hack but is safe and I wanted to see how faster user creation would be with it since that is where I originally started from -- the answer is user creation on a system with 100k users takes 175ms as opposed to 3750ms w/o this change).

I am willing to fix it for 5.0 but I think it needs OCT approval and will write a TIP for it...

Oh, and some information on where the speedups will show up. Malte generated this from aiesec (which has ~5M objects and 850k with null context_id) and you can see the ones with null context_id:

  COUNT(*) OBJECT_TYPE
---------- ---------------------
    152201 acs_activity

    126381 apm_parameter_value
     11079 cal_item
     48268 calendar
      8138 mailbox
      4654 notification
    205921 notification_request
     45549 portal
    146999 portal_page
      5097 rel_segment
     23760 site_node
      3866 static_portal_content
    113891 user

So creation of users, site nodes, packages, notification requests, calendar items and portal pages should all be significantly improved.

Forum OpenACS Development: Re: acs_object__new slowness...