Re: Dataypes: performance differences?

On Wed, Jul 31, 2002 at 06:30:23PM +0900, Jean-Christian Imbeault wrote:

> However I could not find any mention of the differences in performance
> amongst the various dataypes available. I am especially interested in
> the differences between the char/varchar/text datatypes and int2/int4.

char() is only a good idea if you know that the data will always and
only be NULL or a fixed length.  That is, if you have US state codes
(which are all 2 letters), char() might be a good choice.  Fir such
purposes, it is reputed to be marginally faster.  Otherwise, avoid
it; you'll regret it later.

As far as I know, varchar() is just window-dressing on text.  So if
you don't need to restrict length, use text.  If you _do_ need to
restrict length, you have to use varchar(nn).

Note that in Postgres 7.1.x and earlier, over-long varchar(nn) fields
were silently truncated; in 7.2 and later the behaviour has been made
more spec-compliant, so that you will get an error on insert.  This
is another reason to use text.

There are some issues with automatic conversion of ints.  For that
reason, unless you are planning to pay a lot of attention to the
matter, you are probably better off using int4 for everything.  The
docs suggest that using smallint is only really a good idea where
disk space is at a premium.  See

<http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/datatype.html#DATATYPE-INT>

(sorry 'bout the long line).

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110