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 void
language 'plpgsql'
as $_func_$
declare
i integer;
begin
create 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) (SUSE Linux)
-- todd
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
