Re: FW: performance issue with a 2.5gb joinded table

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Freitag, 4. Januar 2013 21:41
To: Heikki Linnakangas
Cc: Daniel Westermann; 'pgsql-performance(at)postgresql(dot)org'
Subject: Re: [PERFORM] FW: performance issue with a 2.5gb joinded table

Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
> One difference is that numerics are stored more tightly packed on
> Oracle. Which is particularly good for Oracle as they don't have other
> numeric data types than number. On PostgreSQL, you'll want to use int4
> for ID-fields, where possible. An int4 always takes up 4 bytes, while
> a numeric holding an integer value in the same range is typically 5-9 bytes.

>> Replacing those numeric(8) and numeric(16) fields with int4 and int8 would be greatly beneficial to comparison and hashing performance, not just table size. I'm a >> bit surprised that EDB's porting tools evidently don't do this automatically (I infer from the reference to PPAS that the OP is using EDB ...)
>>
>> regards, tom lane

Thanks, tom. Any clue where there remaining around 500mb difference come from ? converted all the numeric(8) to int and this saved around 380mb of storage and around 10 secs exectution time... both databases have their files on standard ext3, same fs options. Given that the table has around 25'000'000 rows this is still approx. 20 bytes more per row on average

Regards
Daniel