Re: R: Field's position in Table

Alvaro Herrera wrote:
> Michael Gould wrote:
...
>> doesn't need to look at the overflow page. I don't know if this is true or
>> not in PostGres. If it isn't then I'm not sure what difference it would
>> make other than allowing for "pretty" documentation.
>
> Postgres does not overflow pages. Tuples are stored wholly on a single
> page. If they don't fit, large attributes are stored in a separate
> table (the TOAST table) and only a pointer is kept in the main table.
> So reordering won't give you that benefit.
>
> The other difference it would make is that it'd open the door for
> optimizations like storing all fixed-length not nullable attributes
> together at the start of the tuple. That should give slightly better
> performance.
>
And which is quite easily done by:

BEGIN;
CREATE table reorder_footable AS
SELECT b,c,a
FROM footable;
DROP TABLE footable;
ALTER TABLE reorder_footable RENAME TO footable;
COMMIT;

yes of course this does not deal with FK correctly
so a lot more work would need to be done for a general
solution - but in some cases it should be all one needs
for the tuple optimization. I personally don't by the
prettyness argument for reordering columns since for
all practical use I prefer SELECT a,b,c over SELECT *

Regards
Tino