I've just been looking at how alignment of columns in tuples can make the tuple larger than needed.
I created 2 tables... None of which are very real world, but I was hunting for the extremes here...
The first table contained an int primary key and then a total of 10 int columns and 10 boolean columns, I placed one boolean column after an int column so that it was int,bool, int bool, etc
With the 2nd table I had all the ints first then all the booleans at the end of the table. I then inserted 1 million records per table and checked the sizes of each table.
The first table was 112 MB and the 2nd table was 81MB, so naturally there is a difference when it comes to running queries on these tables.
postgres=# select sum(Value1) from test1;
sum
--------------
500000500000
(1 row)
Time: 239.306 ms
postgres=# select sum(Value1) from test2;
sum
--------------
500000500000
(1 row)
Time: 186.926 ms
So in this example a full scan and aggregate of a single column is 28% faster.
I'm sure in the real world there are many cases where a better choice in column ordering would save space and save processing times, but is this something that we want to leave up to our users?
I've not yet looked at the code to see how hard implementing separation of column physical order and logical order would be. I really just want to get an idea of what the thoughts would be on such a change.
I would imagine it should be possible to have a function which optimises column orders which is run when a table is created or rewritten. New columns would still go onto the end of the tuple unless the table had to be rewritten and in this case the column order would be optimised again. All plays where column names were displayed without explicit ordering, e.g select * and in psql the catalog could be queried to see which order these columns should be displayed in.
For reference I've attached the script I used for testing this.
I'd like to implement this as a project, but before I start any work on it I'd just like to find out other people's thoughts on it.
Regards
David Rowley