Hi,
I've noticed that on 8.4.0, commits can take a long time when a temp table is repeatedly
filled and truncated within a loop. A very contrived example is
begin;
create or replace function commit_test_with_truncations()returns voidlanguage 'plpgsql'
as $_func_$
declarei integer;
begincreate temp table t1 (x integer) on commit drop ;for i in 1 .. 22000 loop insert into t1 select s from
generate_series(1,1000)s ; truncate t1 ;end loop;
end;
$_func_$;
select commit_test_with_truncations() ;
commit ;
On may laptop (Core2 Duo with 3.5GB and a disk dedicated to PG), the function call takes
about 124 seconds, and the commit takes about 43 seconds. The function execution generates
a lot of I/O activity, but the commit is entirely CPU bound.
By contrast, the same test on an 8.2.13 system (2 older Xeons and 8GB) had times of 495
and 19 seconds. In this case, both the function execution and the commit were entirely
CPU bound.
The overall process in 8.4 is much faster than 8.2.13, but the commit time is somewhat
surprising to me. Is that to be expected?
8.4 version(): PostgreSQL 8.4.0 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat
4.1.2-27),64-bit
8.2.13 version(): PostgreSQL 8.2.13 on x86_64-suse-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 20050117 (prerelease)
(SUSELinux)
-- todd