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
