<ssoo@siliconfile.com> writes:
> Gregory Stark <stark@enterprisedb.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
