Re: SERIAL datatype

Mark Roberts írta:
> On Mon, 2008-08-25 at 11:02 -0600, Scott Marlowe wrote:
>
>> Well, of course a 64 bit int is gonna be bigger than a 32 bit, but
>> with alignment issues and on 64 bit hardware, I'm guessing the
>> difference isn't exactly twice as slow / twice as much storage. And
>> it's way faster than a GUID which was what I think started this
>> thread.
>>
> ...
> The integer version is 599752704 bytes, and the bigint version is
> 673120256 bytes (a ~12% size increase). When joining the table to
> itself (keys = 1 date, 5 (big)ints, no indexes), the bigint version
> performs a join to itself with an average of 44.1 sec, and the integer
> version in 29.6 sec (a 48% performance hit).
>
> While granted that it's not twice as big and twice as slow, I think it's
> a fairly valid reason to want to stay within (small)int ranges.
> Sometimes the initial performance hit on insert would really be worth
> the continuing space/performance savings down the road.
>

The development version of PostgreSQL (to-be 8.4)
was modified in a way so on 64-bit hardware 64-bit types
(bigint, date, timestamp, etc.) are compile-time configurable
to be passed as value instead of as reference. This way, most of the
performance hit disappears because there is no malloc() overhead
in passing bigints back and forth. Of course, the on-disk size
difference will be the same.

> Of course, this wasn't very scientific and the benchmarks aren't very
> thorough (for instance I assumed that bigserial is implemented as a
> bigint), but it should remain a valid point.
>
> Of course, it probably has no bearing on the OP's problem. So my advice
> to the OP: have you considered not keying such a volatile table on a
> serial value?
>
> -Mark
>

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/