Simon Riggs wrote:
On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
I'd expect the system being able to reoder the columns to the most
efficient order possible (performance-wise and padding-saving-wise),
automatically. When you create a table, sort the columns to the most
efficient order; ALTER TABLE ADD COLUMN just puts the new columns at the
end of the tuple; and anything that requires a rewrite of the table
(ALTER TABLE ... ALTER TYPE for example; would be cool to have CLUSTER
do it as well; and do it on TRUNCATE also) again recomputes the most
efficient order.
That's exactly what I'm proposing. On table creation, the system
chooses an efficient column order for you.
That's fairly straightforward and beneficial. I much prefer Alvaro's
approach rather than the storage position details originally described.
Moreover, you'd need to significantly re-write lots of ALTER TABLE and I
really don't think you want to go there.
There is a problem: If people do a CREATE TABLE and then issue SELECT *
they will find the columns in a different order. That could actually
break some programs, so it isn't acceptable in all cases. e.g. COPY
without a column-list assumes that the incoming data should be assigned
to the table columns in the same order as the incoming data file.
You seem to have missed that we will be separating logical from physical
ordering. Each attribute will have a permanent id, a physical ordering
and a logical ordering. You can change either ordering without affecting
the other.
COPY, SELECT and all user-visible commands should follow the logical
ordering, not the physical ordering, which should be completely
invisible to SQL.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
