PostgreSQL is a mature server-based database system. The CPU time in our database is the time the server process of the session used.

To measure the resource usage for the PostgreSQL server systems we took a snapshot of the status information provided by at the start of the client program and when the query was finished. The difference between both snapshots is used to measure CPU time. This approach can not be used to measure memory usage. PostgreSQL starts a new process for every client connected. First we read the process ID of the process associated with the connection and then read the status information.

However, the main comparison is done on real time, which is simpler for server-based systems (it also gives parallel implementations an advantage). The startup time of the server is not included in the time measurements.


We've added an index on the second column of the par relation.

-- tc.sql.tbl
SELECT pg_backend_pid(); -- this is needed to find the proper process
drop table if exists par;
create TEMPORARY table par(x integer not null, y integer not null);
\echo 'LOAD DATA'
\copy par from '{instance}';
create index par_y on par(y);
-- tcff.sql
with recursive tc_tmp as
    (select par.x, par.y
    from   par
    select par.x, tc_tmp.y
    from   par, tc_tmp
    where  par.y = tc_tmp.x)
select count(*) from tc_tmp;

Launch: cat tc.sql.tbl tcff.sql | psql


We're using the same table definitions as for the transitive closure.

-- sgff.sql
    SELECT par.b, par.b from par
    SELECT p1.a, p2.a from sg JOIN par p1 ON p1.b = sg.a JOIN par p2 ON p2.b = sg.b
) SELECT Count(*) FROM sg;