Re: reducing NUMERIC size for 9.1

On 16 July 2010 14:14, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> On 16 July 2010 22:51, Richard Huxton <dev(at)archonet(dot)com> wrote:
>> On 16/07/10 13:44, Brendan Jurd wrote:>
>>> At this scale we should be seeing around 2 million bytes saved, but
>>> instead the tables are identical.  Is there some kind of disconnect in
>>> how the new short numeric is making it to the disk, or perhaps another
>>> effect interfering with my test?
>>
>> You've probably got rows being aligned to a 4-byte boundary. You're probably
>> not going to see any change unless you have a couple of 1-byte columns that
>> get placed after the numeric. If you went from 10 bytes down to 8, that
>> should be visible.
>
> Ah, thanks for the hint Richard.  I didn't see any change with two
> 1-byte columns after the numeric, but with four such columns I did
> finally see a difference.
>
> Test script:
>
> BEGIN;
>
> CREATE TEMP TABLE foo (a numeric, b bool, c bool, d bool, e bool);
>
> INSERT INTO foo (a, b, c, d, e)
> SELECT 0::numeric, false, true, i % 2 = 0, i % 2 = 1
> FROM generate_series(1, 1000000) i;
>
> SELECT pg_total_relation_size('foo'::regclass);
>
> ROLLBACK;
>
> Results:
>
> 8.4: 44326912
> HEAD with patch: 36290560
>
> That settles my concern and I'm happy to pass this along to a commiter.
>
> Cheers,
> BJ
>

Joy! :) Nice patch Robert.

Thom