Re: Column storage positions

On Wed, 21 Feb 2007, Alvaro Herrera wrote:

> Stephan Szabo escribi:
> > On Wed, 21 Feb 2007, Martijn van Oosterhout wrote:
> >
> > > On Wed, Feb 21, 2007 at 12:06:30PM -0500, Phil Currier wrote:
> > > > Well, for two reasons:
> > > >
> > > > 1) If you have a table with one very-frequently-accessed varchar()
> > > > column and several not-frequently-accessed int columns, it might
> > > > actually make sense to put the varchar column first. The system won't
> > > > always be able to make the most intelligent decision about table
> > > > layout.
> > >
> > > Umm, the point of the exercise is that if you know there are int
> > > columns, then you can skip over them, whereas you can never skip over a
> > > varchar column. So there isn't really any situation where it would be
> > > better to put the varchar first.
> >
> > IIRC, in the first message in this thread, or another recent thread of
> > this type, someone tried a reordering example with alternating
> > smallints and varchar() and found that the leftmost varchar was
> > actually slower to access after reordering, so I'm not sure that we can
> > say there isn't a situation where it would affect things.
>
> Offsets are cached in tuple accesses, but the caching is obviously
> disabled for all attributes past any variable-length attribute. So if
> you put a varlena attr in front, caching is completely disabled for all
> attrs (but that first one). The automatic reordering algorithm must put
> all fixed-len attrs at the front, so that their offets (and that of the
> first variable length attr) can be cached.
>
> Did I miss something in what you were trying to say? I assume you must
> already know this.

I think so. What I was mentioning was that I was pretty sure that there
was a message with someone saying that they actually tried something that
did this and that they found left-most varchar access was slightly slower
after the reordering although general access was faster. I believe the
table case was alternating smallint and varchar columns, but I don't know
what was tested for the retrieval. If that turns out to be able to be
supported by other tests, then for some access patterns, the rearranged
version might be slower.