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

Re: Column order performance

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Bill Montgomery <billm(at)lulu(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Column order performance
Date: 2004-08-10 17:23:28
Message-ID: 200408101023.28084.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Bill,

> Does the order of columns of varying size have any effect on
> SELECT/INSERT/UPDATE/and/or/DELETE performance? Take the example where
> an integer primary key is listed first in the table and alternatively
> listed after some large varchar or text columns?

No, the "order" of the columns in the table makes no difference.  They are not 
physically stored in the metadata order, anyway; on the data pages, 
fixed-length fields (e.g. INT, BOOLEAN, etc.) are stored first and 
variable-length fields (CHAR, TEXT, NUMERIC) after them, AFAIK.

The only thing I have seen elusive reports of is that *display* speed can be 
afffected by column order (e.g. when you call the query to the command line 
with many rows) but I've not seen this proven in a test case.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-performance by date

Next:From: pgsqlDate: 2004-08-10 18:04:52
Subject: Re: [HACKERS] fsync vs open_sync
Previous:From: Josh BerkusDate: 2004-08-10 17:18:32
Subject: Re: [HACKERS] fsync vs open_sync

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