Thread

1. Re: Best way to "add" columns

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-05-15T14:15:43Z

   Marten Feldtmann wrote:
   > 
   >  The varable lengths columns should be at the end of the row, therefore
   >  it does not seem to be good to add an integer column after a varchar
   >  column.
   
   1. Is this true? Should variable length column really be at the end
   of a row?
   
   2. If so, surly postgres can reorder tham internally so that on disk
   they are in the optimal format.
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/
   
   

2. Re: Best way to "add" columns

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-16T04:56:17Z

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
   > Marten Feldtmann wrote:
   >> The varable lengths columns should be at the end of the row, therefore
   >> it does not seem to be good to add an integer column after a varchar
   >> column.
   
   > 1. Is this true? Should variable length column really be at the end
   > of a row?
   
   There is some microscopic performance advantage if you put fixed-width
   columns before variable-width columns: columns that are at a fixed
   offset in the table records don't require scanning through earlier
   columns to access. But I'd be surprised if you could actually measure
   any difference, unless perhaps on tables with hundreds of columns.
   
   > 2. If so, surly postgres can reorder tham internally so that on disk
   > they are in the optimal format.
   
   There are notes in the source code indicating that the original
   Berkeley Postgres crew thought about this and decided it wasn't
   worth the trouble.
   
   			regards, tom lane
   
   

3. Re: Best way to "add" columns

   Bruce Momjian <pgman@candle.pha.pa.us> — 2000-05-16T14:46:13Z

   > Martijn van Oosterhout <kleptog@cupid.suninternet.com> writes:
   > > Marten Feldtmann wrote:
   > >> The varable lengths columns should be at the end of the row, therefore
   > >> it does not seem to be good to add an integer column after a varchar
   > >> column.
   > 
   > > 1. Is this true? Should variable length column really be at the end
   > > of a row?
   > 
   > There is some microscopic performance advantage if you put fixed-width
   > columns before variable-width columns: columns that are at a fixed
   > offset in the table records don't require scanning through earlier
   > columns to access. But I'd be surprised if you could actually measure
   > any difference, unless perhaps on tables with hundreds of columns.
   > 
   > > 2. If so, surly postgres can reorder tham internally so that on disk
   > > they are in the optimal format.
   > 
   > There are notes in the source code indicating that the original
   > Berkeley Postgres crew thought about this and decided it wasn't
   > worth the trouble.
   
   Yes, agreed.  Can anyone measure the difference.  It would show up
   accessing columns past the variable length column.
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026