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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
