Re: indexing on char vs varchar - Mailing list pgsql-sql
From | Beth Gatewood |
---|---|
Subject | Re: indexing on char vs varchar |
Date | |
Msg-id | 000001c26a36$c1a48c80$0c00000a@bethvizx Whole thread Raw |
In response to | Re: indexing on char vs varchar (Bruce Momjian <pgman@candle.pha.pa.us>) |
Responses |
Re: indexing on char vs varchar
Re: indexing on char vs varchar |
List | pgsql-sql |
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, Pennsylvania19073