pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 23:31:11 -0600
Message-ID: <1087363871.1582.20.camel@localhost.localdomain> (raw)
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>

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.




view thread (18+ messages)  latest in thread

Message-ID: <1087363871.1582.20.camel@localhost.localdomain>
Permalink:  ../1087363871.1582.20.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1087363871.1582.20.camel@localhost.localdomain

 · 

reply

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: <1087363871.1582.20.camel@localhost.localdomain>
  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> <1087363871.1582.20.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