Re: VARCHAR -vs- CHAR: huge performance difference?

On Tue, 2004-06-15 at 18:58, C. Bensend wrote:
> 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. :(

Were those fields populated just like the varchar fields? If not, then
the test proves little. If so, I find it hard to believe that char(x)
would be any faster than varchar. They're all handled about the same.

If you want to do count(*) on the table, do it by having a table with
nothing but IDs in it that is referenced by the table with all the
text. PostgreSQL can't really optimized aggregate functions with
indexes, so it always winds up doing seq scans.