Forum OpenACS Development: Response to PG Object Extensions

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.

Forum OpenACS Development: Response to PG Object Extensions - pros and cons (benchmarking)