Skip site navigation (1) Skip section navigation (2)

logical column order and physical column order

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: logical column order and physical column order
Date: 2013-11-03 07:37:31
Message-ID: CAApHDvqhnuznxd4xVMFDcGn+nHVYyUtJ-TvbRsOuR=PaVbbGqw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Attachment: column_order_test.txt
Description: text/plain (2.4 KB)

Responses

pgsql-hackers by date

Next:From: Gavin FlowerDate: 2013-11-03 08:40:18
Subject: Re: logical column order and physical column order
Previous:From: Tom LaneDate: 2013-11-03 03:01:07
Subject: Re: Creating Empty Index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group