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