Re: logical column ordering

On 28/02/15 12:21, Josh Berkus wrote:
> On 02/27/2015 03:06 PM, Tomas Vondra wrote:
>> On 27.2.2015 23:48, Josh Berkus wrote:
>>> Actually, I'm going to go back on what I said.
>>>
>>> We need an API for physical column reordering, even if it's just pg_
>>> functions. The reason is that we want to enable people writing their
>>> own physical column re-ordering tools, so that our users can figure out
>>> for us what the best reordering algorithm is.
>> I doubt that. For example, do you realize you can only do that while the
>> table is completely empty, and in that case you can just do a CREATE
>> TABLE with the proper order?
> Well, you could recreate the table as the de-facto API, although as you
> point out below that still requires new syntax.
>
> But I was thinking of something which would re-write the table, just
> like ADD COLUMN x DEFAULT '' does now.
>
>> I also doubt the users will be able to optimize the order better than
>> users, who usually have on idea of how this actually works internally.
> We have a lot of power users, including a lot of the people on this
> mailing list.
>
> Among the things we don't know about ordering optimization:
>
> * How important is it for index performance to keep key columns adjacent?
>
> * How important is it to pack values < 4 bytes, as opposed to packing
> values which are non-nullable?
>
> * How important is it to pack values of the same size, as opposed to
> packing values which are non-nullable?
>
>> But if we want to allow users to define this, I'd say let's make that
>> part of CREATE TABLE, i.e. the order of columns defines logical order,
>> and you use something like 'AFTER' to specify physical order.
>>
>> CREATE TABLE test (
>> a INT AFTER b, -- attlognum = 1, attphysnum = 2
>> b INT -- attlognum = 2, attphysnum = 1
>> );
>>
>> It might get tricky because of cycles, though.
> It would be a lot easier to allow the user to specific a scalar.
>
> CREATE TABLE test (
> a INT NOT NULL WITH ( lognum 1, physnum 2 )
> b INT WITH ( lognum 2, physnum 1 )
>
> ... and just throw an error if the user creates duplicates or gaps.
>
I thinks gaps should be okay.

Remember BASIC? Lines numbers tended to be in 10's so you could easily
slot new lines in between the existing ones - essential when using the
Teletype input/output device.

Though the difference here is that you would NOT want to remember the
original order numbers (at least I don't think that would be worth the
coding effort & space). However, the key is the actual order, not the
numbering. However, that might require a WARNING message to say that
the columns would be essentially renumbered from 1 onwards when the
table was actually created - if gaps had been left.

Cheers,
Gavin