pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Column order performance
3+ messages / 2 participants
[nested] [flat]

* Column order performance
@ 2004-08-10 13:39 Bill Montgomery <billm@lulu.com>
  2004-08-10 17:23 ` Re: Column order performance Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Bill Montgomery @ 2004-08-10 13:39 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Column order performance
  2004-08-10 13:39 Column order performance Bill Montgomery <billm@lulu.com>
@ 2004-08-10 17:23 ` Josh Berkus <josh@agliodbs.com>
  2004-08-10 18:49   ` Re: Column order performance Bill Montgomery <billm@lulu.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2004-08-10 17:23 UTC (permalink / raw)
  To: Bill Montgomery <billm@lulu.com>; 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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Column order performance
  2004-08-10 13:39 Column order performance Bill Montgomery <billm@lulu.com>
  2004-08-10 17:23 ` Re: Column order performance Josh Berkus <josh@agliodbs.com>
@ 2004-08-10 18:49   ` Bill Montgomery <billm@lulu.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Bill Montgomery @ 2004-08-10 18:49 UTC (permalink / raw)
  To: pgsql-performance

Josh Berkus wrote:

>>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.
>  
>

Is this true even after a table is altered to "append" say, an integer 
column, after there are already variable-length columns in the table?

-Bill



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-08-10 18:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-10 13:39 Column order performance Bill Montgomery <billm@lulu.com>
2004-08-10 17:23 ` Josh Berkus <josh@agliodbs.com>
2004-08-10 18:49   ` Bill Montgomery <billm@lulu.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox