pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: C. Bensend <benny@bennyvision.com>
To: pgsql-admin@postgresql.org
Subject: Re: VARCHAR -vs- CHAR: huge performance difference?
Date: Tue, 15 Jun 2004 21:12:29 -0500 (CDT)
Message-ID: <50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net> (raw)
In-Reply-To: <15549.1087351669@sss.pgh.pa.us>
References: <51429.63.227.74.41.1087347504.squirrel@webmail.stinkweasel.net>
	<15549.1087351669@sss.pgh.pa.us>


> "C. Bensend" <benny@bennyvision.com> 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.

Hi Tom,

   By transformation, do you mean the varchar() -> char() change?  If
so, I'm not sure I understand - it certainly improved the performance.
Or am I misunderstanding?

> 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...

Actually, all databases on this server are vacuumed nightly, right
before backups.  But here is the data:

prod01=> vacuum verbose emails;
INFO:  --Relation public.emails--
INFO:  Index emails_email_id_idx: Pages 358; Tuples 24198: Deleted 82.
        CPU 0.03s/0.01u sec elapsed 0.41 sec.
INFO:  Index emails_date_received_idx: Pages 325; Tuples 24198: Deleted 82.
        CPU 0.00s/0.00u sec elapsed 0.63 sec.
INFO:  Removed 82 tuples in 23 pages.
        CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  Pages 5793: Changed 0, Empty 0; Tup 24198: Vac 82, Keep 0, UnUsed
71757.
        Total CPU 0.24s/0.06u sec elapsed 4.71 sec.
INFO:  --Relation pg_toast.pg_toast_399420--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
prod01=> vacuum verbose emails2;
INFO:  --Relation public.emails2--
INFO:  Pages 2646: Changed 0, Empty 0; Tup 24162: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.10s/0.00u sec elapsed 1.00 sec.
INFO:  --Relation pg_toast.pg_toast_859969--
INFO:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
        Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Thanks very much,

Benny


-- 
"Oh, the Jedis are going to feel this one!"       -- Professor Farnsworth,
                                                     "Futurama"



view thread (18+ messages)  latest in thread

Message-ID: <50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net>
Permalink:  ../50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net/
Also on:    postgresql.org/message-id/50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net

 · 

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: benny@bennyvision.com
  Subject: Re: VARCHAR -vs- CHAR: huge performance difference?
  In-Reply-To: <50124.63.227.74.41.1087351971.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>

* 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