Re: Column storage positions

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.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support