VARCHAR -vs- CHAR: huge performance difference? - Mailing list pgsql-admin

From C. Bensend
Subject VARCHAR -vs- CHAR: huge performance difference?
Date
Msg-id 51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net
Whole thread Raw
Responses Re: VARCHAR -vs- CHAR: huge performance difference?
Re: VARCHAR -vs- CHAR: huge performance difference?
Re: VARCHAR -vs- CHAR: huge performance difference?
List pgsql-admin
Hey folks,

   I am working on a rather small, simple database.  I'm running 7.3.5 on
an OpenBSD 3.5-STABLE machine (1.3GHz, 512M RAM, IDE drive using UDMA-5).

   I am parsing and storing emails, so I have a lot of character string
data to worry about.  In one particular table, I have 26 columns of type
varchar, with widths of 24-8192 characters.  This is the table that is
giving me some pretty severe performance problems.

   For example, many of the statistics I run against these emails is a
simple count(), whether it be on a column or *.  Right now, counting the
rows takes in excess of 3.5 seconds.  OK, that's pretty slow.  However,
there are only --> 24,000 <-- rows in this table!  I could see it taking
a long time if I had 500,000 rows, or a million, but 24,000?

   Now, I am not an expert with database design whatsoever, so I fully
admit that having a table with 26 varchar columns (along with a handful
of other fixed-width columns) probably isn't the brightest design.  :)
So, I went ahead and created an exact copy of this table, with the
exception of creating all character columns as type char(), not varchar().
I was pondering if making PostgreSQL worry about the varying lengths
by using varchar was the problem...

   And sure enough, counting the rows on the new table takes around
148ms.  That's a pretty big difference from 3600ms.  And no, 3.6 seconds
doesn't sound like much, until you have several operations on the same
table to draw a single web page, and suddenly the page takes 20s to
load.  :(

   So, my question for the gurus:  is using varchars THAT detrimental?
Or am I just going way way overboard by having so _many_ in a single
table?  Or am I an idiot?  (honest question - I'm armpit-deep in learning
mode here)  I'm also curious to know if I've crossed some invisible
line with the number of columns/width of rows that makes performance
degrade rapidly.

   If further info is needed, please ask - I just didn't want to spam
the list with further table definitions and explain analyze output if
it wasn't needed.

Benny


--
"Oh, the Jedis are going to feel this one!"       -- Professor Farnsworth,
                                                     "Futurama"

pgsql-admin by date:

Previous
From: "Lee Wu"
Date:
Subject: Re: table not shown
Next
From: "Scott Marlowe"
Date:
Subject: Re: VARCHAR -vs- CHAR: huge performance difference?