I've ported the users, persons, parties, and acs_objects table and set up a simple benchmark. For example to benchmark the users, persons, parties, acs_objects join, I've created the following function:
create function bm1(integer) returns text as '
declare
cnt alias for $1;
v_i integer;
startt text;
endt text;
trec record;
begin
startt := timeofday();
for v_i in 1..cnt LOOP
select u.*,p.*,pt.*,o.* into trec
from users1 u, persons p, parties pt, acs_objects o
where o.object_id = v_i
and pt.party_id = o.object_id
and p.person_id = pt.party_id
and u.user_id = p.person_id;
end loop;
endt := timeofday();
return delta_time_msecs(startt,endt);
end;' language 'plpgsql';
the timeofday function is like now(), except it returns up to usec precision:
acspg=# select timeofday();
timeofday
-------------------------------------
Tue Feb 20 19:46:15.262591 2001 EST (1 row)
It returns a text string but it works to cast the result to a timestamp, and if used for time interval calculation, they work out correctly. I use this as a basis for estimating the time for executing a query.
In addition to the above function, I have created similar functions to test the following: 1) simple select from the acs4 users table, 2) simple join of acs_objects and the parties table, 3) select from users table which is inherited from persons -> parties -> acs_objects. To perform the tests, I have created two similar data-models, one based on acs4, and one based on porting acs4 to use pg inheritance. I then populated the acs_objects table with 11,000 rows where 1000 of the objects correspond to users in the users table. I'm not sure what a reasonable ratio of objects to users should be, but it seems that the number of objects would be much higher than the number of users.
I then ran a test where I averaged the number of selects over 1000 iterations. I get the following results:
acspg=# i trial.sql
method | time | units
-------------------------------------+----------+--------------
user inherits person, party, object | 0.292497 | milliseconds
(1 row)
method | time | units
-----------------------+----------+--------------
simple select on user | 0.187112 | milliseconds
(1 row)
method | time | units
---------------------------------+----------+--------------
simple join of object and party | 0.275417 | milliseconds
(1 row)
method | time | units
-------------------------------------+----------+--------------
join of object, party, person, user | 0.667479 | milliseconds
(1 row)
This test was performed on redhat 6.2, pg 7.1 beta3 using a 667MHz PIII with 512 Mb of ram. If anybody has requests for what they feel might a more realistic test, let me know, and I'll see if I can set it up.