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 89D4FD1B19F for ; Wed, 16 Jun 2004 02:30:36 -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 66455-03 for ; Wed, 16 Jun 2004 05:30:28 +0000 (GMT) Received: from mpls-qmqp-02.inet.qwest.net (mpls-qmqp-02.inet.qwest.net [63.231.195.113]) by svr1.postgresql.org (Postfix) with SMTP id 19A4BD1B21B for ; Wed, 16 Jun 2004 02:30:26 -0300 (ADT) Received: (qmail 10320 invoked by uid 0); 16 Jun 2004 04:22:39 -0000 Received: from mpls-pop-06.inet.qwest.net (63.231.195.6) by mpls-qmqp-02.inet.qwest.net with QMQP; 16 Jun 2004 04:22:39 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-06.inet.qwest.net with SMTP; 16 Jun 2004 05:30:26 -0000 Date: Tue, 15 Jun 2004 23:31:11 -0600 Message-Id: <1087363871.1582.20.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: <58063.63.227.74.41.1087355391.squirrel@webmail.stinkweasel.net> References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net> <15549.1087351669@sss.pgh.pa.us> <50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net> <15996.1087354312@sss.pgh.pa.us> <58063.63.227.74.41.1087355391.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/197 X-Sequence-Number: 13857 On Tue, 2004-06-15 at 21:09, C. Bensend wrote: > > Right. The only thing that will do for you is waste space with padding > > blanks. The performance improvement you saw was due to something else, > > which I theorize is not having to waste time reading dead space. > > > > Since you obviously doubt this assertion, try the same experiment except > > load the data into a new table with the *same* structure as the original > > (ie, all varchar). You'll see the same or probably better performance. > > Hi Tom, > > I don't doubt your assertion, I just don't understand it all yet. :) > Let me assure you, you're the expert here, not I. > > I did as you suggested - I created a third copy of the table, using > the exact same structure. And you're absolutely right - it was lightning > fast (around 100ms). > > >> Actually, all databases on this server are vacuumed nightly, right > >> before backups. > > > > Not often enough evidently... > > This statement worries me a bit. The data in the original table was > bulk-loaded the other night (less than three days I'm sure), and new > entries have been added at the approximate rate of 300 per day. Is this > going to continue to happen? > > Or do I just need to vacuum more often? I _did_ try a vacuum before > asking the list for help, but it didn't give any improvement (just a > vacuum analyze). > > > Note the difference in "pages". emails is nearly double the physical > > size, even though the live data in it is doubtless smaller. (You could > > use the contrib/pgstattuple functions to see exactly how much live data > > there is.) > > OK. I see (and understand) the pages value now. > > > I was actually expecting to see more than a 2:1 difference in file size, > > seeing that you reported more than a 2:1 difference in read time. It > > could be that there is also some question of physical layout of the file > > on disk. The original table probably accreted over time and was given > > space that's not very consecutive on disk. The copied table was > > written in one swoop, more or less, and is very likely stored in a more > > nearly consecutive set of physical disk blocks. I'm not sure how you'd > > investigate this theory though --- AFAIK there isn't any real easy way > > to find out how badly fragmented a file is in most Unix filesystems. > > Ugh. So, what would you recommend as a fix? I see the problem, and I > see the fix that just worked, but I certainly can't be the only person > around that is using a "wide" table with a lot of character data being > added at a rather slow rate... You might want to look into the autovacuum daemon, and / or increasing fsm settings to be large enough to hold all the spare tuples released by vacuuming.