From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Beth Gatewood <beth(at)vizxlabs(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: indexing on char vs varchar |
Date: | 2002-10-02 17:06:27 |
Message-ID: | 200210021706.g92H6Re29922@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Thrasher | 2002-10-02 17:11:19 | Updating from select |
Previous Message | Frederic Logier | 2002-10-02 16:54:58 | Re: split function for pl/pgsql |