X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3DD6BD1B297 for ; Tue, 15 Jun 2004 22:22:39 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01462-10 for ; Wed, 16 Jun 2004 01:22:32 +0000 (GMT) Received: from mpls-qmqp-04.inet.qwest.net (mpls-qmqp-04.inet.qwest.net [63.231.195.115]) by svr1.postgresql.org (Postfix) with SMTP id 7D733D1B8E0 for ; Tue, 15 Jun 2004 22:22:27 -0300 (ADT) Received: (qmail 18227 invoked by uid 0); 16 Jun 2004 01:22:31 -0000 Received: from mpls-pop-08.inet.qwest.net (63.231.195.8) by mpls-qmqp-04.inet.qwest.net with QMQP; 16 Jun 2004 01:22:31 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-08.inet.qwest.net with SMTP; 16 Jun 2004 01:22:31 -0000 Date: Tue, 15 Jun 2004 19:23:06 -0600 Message-Id: <1087348985.1582.18.camel@localhost.localdomain> From: "Scott Marlowe" To: "C. Bensend" Cc: pgsql-admin@postgresql.org Subject: Re: VARCHAR -vs- CHAR: huge performance difference? In-Reply-To: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net> References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net> Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/188 X-Sequence-Number: 13848 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.