Re: How Postgresql Compares For Query And Load Operations

Mark kirkwood <markir(at)slingshot(dot)co(dot)nz> writes:
> SELECT sum(val) FROM fact0

> I monitored the cpu consumed by the relevant db processes ( counting
> the time noted against each process from ps -ef, hope that was what
> you had in mind )

> DB Elapsed Cpu
> Postgres 2m25s 2m01s
> Db2 50s 30s
> Oracle 40s 18s

Yes, just what I wanted to see. It looks like my suspicion about CPU,
not I/O, being the main issue was right on target. And I know where
the time is going.

sum(int4) is a good deal slower in PG 7.1 than in prior releases,
because it uses an accumulator of type "numeric" rather than "int4".
We made this change to avoid overflow problems, but I wonder whether
we went too far in the direction of safety rather than performance.

A possible compromise is to make the accumulator be type "int8" for
int2 and int4 sums. You'd have to sum over at least 2^32 rows to
have any risk of overflow, which seems acceptable to me --- comments
anyone?

Another consideration is what about machines without any 64-bit int
support. These machines would end up using a 32-bit int accumulator,
which would mean they'd be back to the pre-7.1 behavior in which sum()
could overflow. Is this okay?

regards, tom lane