Re: logical column ordering

Tomas Vondra wrote:

> 1) change the order of columns in "SELECT *"
>
> - display columns so that related ones grouped together
> (irrespectedly whether they were added later, etc.)
>
> - keep columns synced with COPY
>
> - requires user interface (ALTER TABLE _ ALTER COLUMN _ SET ORDER _)

Not sure about the "ORDER #" syntax. In ALTER ENUM we have "AFTER
<value>" and such .. I'd consider that instead.

> 2) optimization of physical order (efficient storage / tuple deforming)
>
> - more efficient order for storage (deforming)
>
> - may be done manually by reordering columns in CREATE TABLE
>
> - should be done automatically (no user interface required)

A large part of it can be done automatically: for instance, not-nullable
fixed length types ought to come first, because that enables the
attcacheoff optimizations in heaptuple.c to fire for more columns. But
what column comes next? The offset of the column immediately after them
can also be cached, and so it would be faster to obtain than other
attributes. Which one to choose here is going to be a coin toss in most
cases, but I suppose there are cases out there which can benefit from
having a particular column there.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services