From: Beth Gatewood <beth@vizxlabs.com>
To: pgsql-sql@postgresql.org
Subject: indexing on char vs varchar
Date: Wed, 2 Oct 2002 09:39:09 -0700
Message-ID: <004101c26a32$3bce7390$0c00000a@bethvizx> (raw)
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
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-sql@postgresql.org
Cc: beth@vizxlabs.com
Subject: Re: indexing on char vs varchar
In-Reply-To: <004101c26a32$3bce7390$0c00000a@bethvizx>
* 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