Re: logical column ordering

On 28/02/15 09:49, 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.)
>>
>>     - 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.
>
>
Possible, if there is no obvious (to the system) way of deciding the 
order of 2 columns, then the logical order should be used?

As either the order does not really matter, or an expert DBA might know 
which is more efficient.


Cheers,
Gavin