Re: logical column ordering

On 2/27/15 2:49 PM, Alvaro Herrera wrote:
> 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.)

FWIW, I find the ordering more important for things like \d than SELECT *.

Hey, after we get this done the next step is allowing different logical 
ordering for different uses! ;P

>>     - 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.

+1. See also Gavin's suggestion of ALTER COLUMN (a, b, c) SET ORDER ...

>> 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

I would think that eliminating wasted space due to alignment would be 
more important than optimizing attcacheoff, at least for a database that 
doesn't fit in memory. Even if it does fit in memory I suspect memory 
bandwidth is more important than clock cycles.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com