pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Montgomery <billm@lulu.com>
To: pgsql-performance@postgresql.org
Subject: Column order performance
Date: Tue, 10 Aug 2004 09:39:20 -0400
Message-ID: <4118D008.1070606@lulu.com> (raw)

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



view thread (3+ messages)  latest in thread

Message-ID: <4118D008.1070606@lulu.com>
Permalink:  ../4118D008.1070606@lulu.com/
Also on:    postgresql.org/message-id/4118D008.1070606@lulu.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: billm@lulu.com
  Subject: Re: Column order performance
  In-Reply-To: <4118D008.1070606@lulu.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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