Column storage position is the subject of many long threads in recent
times. Solutions proposed for this have been both fairly complex and
long enough that nothing seems likely to happen for 8.3. If I'm wrong,
then of course this proposal would be superceded.

I propose that at CREATE TABLE time, the column ordering is re-ordered
so that the table columns are packed more efficiently. This would be a
physical re-ordering, so that SELECT * and COPY without explicit column
definitions would differ from the original CREATE TABLE statement.

This would be an optional feature, off by default, controlled by a
USERSET GUC
optimize_column_order = off (default) | on

When the full column ordering proposal is implemented,
optimize_column_ordering would be set to default to on. The feature
would be supported for at least one more release after this to allow bug
analysis.

The proposed ordering would be:
1. All fixed length columns, arranged so that alignment is efficient
2. All variable length columns

All column ordering would stay as close as possible to original order

No changes would be made apart from at CREATE TABLE time.

The ordering would be repeatable, so that the order would not change on
repeated dump/restore of a table with no changes.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Simon Riggs wrote:
>
> I propose that at CREATE TABLE time, the column ordering is re-ordered
> so that the table columns are packed more efficiently. This would be a
> physical re-ordering, so that SELECT * and COPY without explicit column
> definitions would differ from the original CREATE TABLE statement.
>
> This would be an optional feature, off by default, controlled by a
> USERSET GUC
> optimize_column_order = off (default) | on
>


Umm, you want a GUC setting to enable standards-breaking behaviour and
that will be obsolete when we do column ordering right, which is not
likely to be more than one release away, and could even still happen in
this coming release?

I hope I haven't misunderstood.

cheers

andrew


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Andrew Dunstan wrote:
> Simon Riggs wrote:
> >
> > I propose that at CREATE TABLE time, the column ordering is re-ordered
> > so that the table columns are packed more efficiently. This would be a
> > physical re-ordering, so that SELECT * and COPY without explicit column
> > definitions would differ from the original CREATE TABLE statement.
> >
> > This would be an optional feature, off by default, controlled by a
> > USERSET GUC
> > optimize_column_order = off (default) | on
>
> Umm, you want a GUC setting to enable standards-breaking behaviour and
> that will be obsolete when we do column ordering right, which is not
> likely to be more than one release away, and could even still happen in
> this coming release?

Given that we already seem to have a patch implementing a complete
solution, or part thereof, this would seem a rather shortsighted
proposal. Why not develop the whole thing and be done with it?

I don't understand the reluctance to implementing all of it. The most
serious objection I've seen, from Andreas IIRC, is that it would make
drivers' lives more difficult; but really, drivers have to cope with
dropped columns today which is a pain, and ISTM this proposal (not this
one here, but the three-column proposal) would make that a bit simpler.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

> Given that we already seem to have a patch implementing a complete
> solution

we do?


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Thu, 2007-02-22 at 23:49 -0300, Alvaro Herrera wrote:
> Andrew Dunstan wrote:
> > Simon Riggs wrote:
> > >
> > > I propose that at CREATE TABLE time, the column ordering is re-ordered
> > > so that the table columns are packed more efficiently. This would be a
> > > physical re-ordering, so that SELECT * and COPY without explicit column
> > > definitions would differ from the original CREATE TABLE statement.
> > >
> > > This would be an optional feature, off by default, controlled by a
> > > USERSET GUC
> > > optimize_column_order = off (default) | on
> >
> > Umm, you want a GUC setting to enable standards-breaking behaviour and
> > that will be obsolete when we do column ordering right, which is not
> > likely to be more than one release away, and could even still happen in
> > this coming release?
>
> Given that we already seem to have a patch implementing a complete
> solution, or part thereof, this would seem a rather shortsighted
> proposal. Why not develop the whole thing and be done with it?

That would be my preference, but if it doesn't happen, I wanted to have
a clear secondary proposal documented.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, 2007-02-22 at 20:07 -0600, Andrew Dunstan wrote:
> Simon Riggs wrote:
> >
> > I propose that at CREATE TABLE time, the column ordering is re-ordered
> > so that the table columns are packed more efficiently. This would be a
> > physical re-ordering, so that SELECT * and COPY without explicit column
> > definitions would differ from the original CREATE TABLE statement.
> >
> > This would be an optional feature, off by default, controlled by a
> > USERSET GUC
> > optimize_column_order = off (default) | on
> >
>
>
> Umm, you want a GUC setting to enable standards-breaking behaviour and
> that will be obsolete when we do column ordering right, which is not
> likely to be more than one release away, and could even still happen in
> this coming release?

If this is standards-breaking as you say, I would withdraw immediately.
I checked the SQL standard and could not see how this would do so. The
standard states SELECT * would return columns in order; it doesn't say
what that order should be, nor does CREATE TABLE enforce the ordering to
be the same as it has specified, AFAICS. Please correct me and I will
withdraw. Practical issues seem far stronger drivers than standards
issues here, which is why the parameter would default=off.

If the full implementation exists and works, I would welcome it. This
proposal is really aimed at what we do if that doesn't occur; we must
wait to see if it will. I see that many users would want to get
something sooner rather than later. That isn't a commercial perspective,
I see that as a PostgreSQL advocacy perspective.

I also see that we are forcing change into the on-disk format of heaps
in this release. If we defer this to another release then we would be
effectively changing the on-disk format again in next release.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Simon Riggs wrote:

> If this is standards-breaking as you say, I would withdraw immediately.
> I checked the SQL standard and could not see how this would do so. The
> standard states SELECT * would return columns in order; it doesn't say
> what that order should be, nor does CREATE TABLE enforce the ordering to
> be the same as it has specified, AFAICS. Please correct me and I will
> withdraw. Practical issues seem far stronger drivers than standards
> issues here, which is why the parameter would default=off.

I did not follow the entire thread. I just wanted to point out that IIRC
MS SQL Server (and maybe also Sybase) do automatically optimize the
internal order of how columns are stored to move fixed length (which
also means non NULLable for these two servers) columns to the left.
Maybe this will serve as a reference point (not necessarily for
standards compliance of course).

regards,
Lukas

> I don't understand the reluctance to implementing all of it.
> The most serious objection I've seen, from Andreas IIRC, is
> that it would make drivers' lives more difficult; but really,
> drivers have to cope with dropped columns today which is a

Yes, I already said, that my objection is probably moot in face of
drop column.

> pain, and ISTM this proposal (not this one here, but the
> three-column proposal) would make that a bit simpler.

Do all of them support dropped columns correctly, yet ?

Andreas

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Hi Simon,

On 2/23/07, Simon Riggs <simon@2ndquadrant.com> wrote:
> If this is standards-breaking as you say, I would withdraw immediately.
> I checked the SQL standard and could not see how this would do so. The
> standard states SELECT * would return columns in order; it doesn't say
> what that order should be, nor does CREATE TABLE enforce the ordering to
> be the same as it has specified, AFAICS. Please correct me and I will
> withdraw.

Phil Currier seems to have a preliminary proposition so I think we
should wait for his patch. AFAICS in what he did, the physical
position is not tied to the column ordering and it's better IMHO. It
doesn't seem very intrusive and it doesn't change the "visible"
behavior of PostgreSQL.
Personnaly, I really need my column ordering. When I create my table,
I put the columns in a logical order and I need they stay as I created
them (for \d for example - on a large table I make semantic groups so
I can find the field names more easily).
And I think a lot of people do it as well so I'm not sure people would
use a GUC which changes that much their habits.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> > If this is standards-breaking as you say, I would withdraw
immediately.
> > I checked the SQL standard and could not see how this would do so.
The
> > standard states SELECT * would return columns in order; it doesn't
say

Imho the create table order is implied. What other order would they mean
with "in order" ?

> > what that order should be, nor does CREATE TABLE enforce the
ordering
> > to be the same as it has specified, AFAICS. Please correct me and I
> > will withdraw. Practical issues seem far stronger drivers than
> > standards issues here, which is why the parameter would default=off.
>
> I did not follow the entire thread. I just wanted to point
> out that IIRC MS SQL Server (and maybe also Sybase) do
> automatically optimize the internal order of how columns are
> stored to move fixed length (which also means non NULLable
> for these two servers) columns to the left.
> Maybe this will serve as a reference point (not necessarily
> for standards compliance of course).

If you state that, it is imho also important to note that this is not
externally visible. select * is not altered.
I think we can have this based on Phil's patch. Simon would probably be
willing to extend it to choose the best physical order during create
table time. And the first step would be done.

The next step could then be to "fix" drop column and add "add column
before"
eighter using a 3rd column or using the existing 2.

Andreas


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings