Re: Column storage positions

Phil Currier wrote:
> On 2/21/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Keep in mind we have a patch in process to reduce the varlena length and
> > reduce alignment requirements, so once that is in, reordering columns
> > will not be as important.
>
> Well, as I understand it, that patch isn't really addressing the same
> problem. Consider this table:
> create table foo (a varchar(10), b int, c smallint, d int, e smallint, ....);
>
> There are two problems here:
>
> 1) On my machine, each int/smallint column pair takes up 8 bytes. 2
> of those 8 bytes are alignment padding wasted on the smallint field.
> If we grouped all the smallint fields together within the tuple, that
> space would not be lost.

Yes, good point.

> 2) Each time you access any of the int/smallint fields, you have to
> peek inside the varchar field to figure out its length. If we stored
> the varchar field at the end of the tuple instead, the access times
> for all the other fields would be measurably improved, by a factor
> that greatly outweighs the small penalty imposed on the varchar field
> itself.
>
> My understanding is that the varlena headers patch would potentially
> reduce the size of the varchar header (which is definitely worthwhile
> by itself), but it wouldn't help much for either of these problems.
> Or am I misunderstanding what that patch does?
>

Agreed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +