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.
