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 0DA16D1B7D0 for ; Tue, 15 Jun 2004 23:07:53 -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 63709-07 for ; Wed, 16 Jun 2004 02:07:54 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2BFD3D1B7BF for ; Tue, 15 Jun 2004 23:07:47 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5G27nqx015550; Tue, 15 Jun 2004 22:07:49 -0400 (EDT) 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> Comments: In-reply-to "C. Bensend" message dated "Tue, 15 Jun 2004 19:58:02 -0500" Date: Tue, 15 Jun 2004 22:07:49 -0400 Message-ID: <15549.1087351669@sss.pgh.pa.us> From: Tom Lane 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/191 X-Sequence-Number: 13851 "C. Bensend" writes: > 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... The above transformation is a guaranteed loser in Postgres. > And sure enough, counting the rows on the new table takes around > 148ms. That's a pretty big difference from 3600ms. I'm betting that the original table is physically huge because you've not vacuumed it regularly. The copying produced a table with no wasted space, so physically smaller even though the live data is noticeably bigger (because of all the padding blanks you forced to be added). Check what VACUUM VERBOSE has to say about each of these tables... regards, tom lane