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