Re: alter table type from double precision to real

<ssoo(at)siliconfile(dot)com> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>> This could also be due to alignment restrictions on the other columns or the
>> row as a whole. If you're curious exactly what's going on and how to optimize
>> your table layout send your table definition and we can tell you exactly how
>> it's being laid out and where the extra 4 bytes are going.
>
> Here's my table:
>
> create table WaferTestItem (
> WaferID integer NOT NULL REFERENCES Wafer (ID),
> X integer NOT NULL,
> Y integer NOT NULL,
> TestItemID integer NOT NULL REFERENCES TestItem (ID),
> Value double precision NOT NULL,
> PRIMARY KEY (WaferID, X, Y, TestItemID)
> );
>
> What happen if type of Value altered to real?

That table is pretty much optimally packed. It's 4+4+4+4+8 or 24 bytes with no
padding between any of the fields. The next record can start immediately after
the end of the previous one because 24 is a multiple of 8.

If you reduce the last field to 4 bytes then it'll be 4+4+4+4+4 or 20 bytes.
On your 64-bit platform you need 8-byte alignment for the next record so
there'll be 4 bytes of padding before the next record.

I haven't done the arithmetic but there's a small chance (like 1 in 14 think)
that this size will leave just enough space at the end of the page for one
extra record with the reduced size. In which case you would expect about 0.7%
space savings (these are realy small tuples, one more on a page doesn't
represent much saving).

If you had happened to have one more integer then going to real would save you
a lot of space though. Then it would be the difference between 32 and 24 bytes
(plus record headers). That would be more than 12% saving (on 8.2 -- on 8.3 it
would be over 14%).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com