Re: How Postgresql Compares For Query And Load Operations

Mark kirkwood <markir@slingshot.co.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