From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | ssoo(at)siliconfile(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: alter table type from double precision to real |
Date: | 2007-06-25 14:16:42 |
Message-ID: | 28051.1182781002@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Michael Fuhr <mike(at)fuhr(dot)org> writes:
> On Mon, Jun 25, 2007 at 12:35:11AM -0400, Tom Lane wrote:
>> Even with no other columns involved, if you're on a machine with
>> MAXALIGN = 8 (which includes all 64-bit platforms as well as some
>> that aren't), the row width won't shrink.
> I see table sizes shrink on 64-bit sparc and x86 architectures, as
> in the following example that results in adjacent 4-byte columns.
> Or am I misinterpreting what's happening?
Sorry, I should've clarified that this depends on whether the total row
length is a multiple of 8. In your example, you have an 8-byte column
followed by a 4-byte column. MAXALIGN-8 machines will pad the row
length to 16 bytes. You then altered it to be two 4-byte columns,
requiring no padding to have a row length of 8 bytes. (Plus overhead
of course, but the overhead is MAXALIGN'd anyway.)
The case I was thinking of was more like this:
regression=# create table test (col1 double precision);
CREATE TABLE
regression=# insert into test select 1.0 from generate_series(1, 10000);
INSERT 0 10000
regression=# select pg_relation_size('test');
pg_relation_size
------------------
368640
(1 row)
regression=# alter table test alter col1 type real;
ALTER TABLE
regression=# select pg_relation_size('test');
pg_relation_size
------------------
368640
(1 row)
The space savings disappears into alignment padding.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua | 2007-06-25 14:30:55 | simple SQL question |
Previous Message | Chris Browne | 2007-06-25 13:48:28 | Re: Experiences of PostgreSQL on-disk bitmap index patch |