<ssoo@xxxxxxxxxxxxxxx> writes: > Gregory Stark <stark@xxxxxxxxxxxxxxxx> 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
