Column order performance

From: Bill Montgomery <billm(at)lulu(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Column order performance
Date: 2004-08-10 13:39:20
Message-ID: 4118D008.1070606@lulu.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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? For example, is this
different performance-wise:

CREATE TABLE foo
(
foo_id serial,
foo_data varchar(8000),
primary key (foo_id)
);

from this?

CREATE TABLE bar
(
bar_data varchar(8000),
bar_id serial,
primary key (bar_id)
);

My suspicion is it would never make a difference since the index will be
searched when querying "WHERE [foo|bar]_id=?" (as long as the planner
decides to use the index).

What about a case where a sequential scan _must_ be performed? Could the
order of columns make a difference in the number of pages read/written
if there is a mix of small and large columns?

Thanks for your help.

Best Regards,

Bill Montgomery

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Alex Hayward 2004-08-10 14:35:31 Re: Performance Bottleneck
Previous Message Tom Lane 2004-08-10 04:41:33 Re: insert waits for delete with trigger