From: "Beth Gatewood" <beth@vizxlabs.com>
To: "'Bruce Momjian'" <pgman@candle.pha.pa.us>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: indexing on char vs varchar
Date: Wed, 2 Oct 2002 10:11:31 -0700
Message-ID: <000001c26a36$c1a48c80$0c00000a@bethvizx>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
In-Reply-To: <200210021706.g92H6Re29922@candle.pha.pa.us>
Importance: Normal

Sorry....I don't understand.  The length is at the front of what?

-Beth

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Wednesday, October 02, 2002 10:06 AM
> To: Beth Gatewood
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] indexing on char vs varchar
>
>
>
> We store all the text/char/varchar types with the length at
> the front so
> we don't have such optimizations.  We do have "char", in quotes, which
> is a single character, but that's about it.
>
> --------------------------------------------------------------
> -------------
>
> Beth Gatewood wrote:
> > Hi-
> >
> > This is more just trying to understand what is going on
> under the hood of
> > pgsql.  I have read through the archives that there is no
> difference between
> > index on char, varchar or text.  I am wondering why?  I
> understand all the
> > arguments about saving space but I am specifically asking
> about index
> > performance and wondering about the underworkings of
> indices based on char
> > and varchar.
> >
> > Othe RDBMS have clear leanings that indexing on chars are a
> better way to
> > go.
> >
> > In MySQL this is due to a static table characteristics
> > (http://www.mysql.com/doc/en/Static_format.html) and speed
> for an index
> > look-up (row number X row length). and the ease to read a
> constant number of
> > records with each disk.
> >
> >  In the case of Oracle, the suggestion for char is based on if using
> > varchar2 that takes 5 char and then there is a subsequent
> update to this
> > field to now take 20 char, but now the record can not grow
> physically...so
> > they essentially mark the old one as deleted and create a
> new record at the
> > top (in an entirely new block) but the problem is that the
> index points to
> > the deleted block...so the index has to query the old block
> and then the
> > new....(info from:
> >
> http://groups.google.com/groups?q=oracle+char+vs+varchar+index
&hl=en&lr=&ie=
> UTF-8&oe=UTF-8&selm=3a791aa3%40news.iprimus.com.au&rnum=1)
>
> Thanks for explaining this to me....
> -Beth
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073