From: Scott Marlowe <smarlowe@qwest.net>
To: C. Bensend <benny@bennyvision.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: VARCHAR -vs- CHAR: huge performance difference?
Date: Tue, 15 Jun 2004 19:23:06 -0600
Message-ID: <1087348985.1582.18.camel@localhost.localdomain> (raw)
In-Reply-To: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net>
References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net>
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.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: smarlowe@qwest.net, benny@bennyvision.com
Subject: Re: VARCHAR -vs- CHAR: huge performance difference?
In-Reply-To: <1087348985.1582.18.camel@localhost.localdomain>
References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net> <1087348985.1582.18.camel@localhost.localdomain>
* 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