Re: VARCHAR -vs- CHAR: huge performance difference? - Mailing list pgsql-admin

Hi Benny,

What happens if you recreate the table using varchar? I mean use the same
procedure yoused to create the table with char, but leave the definitions as
varchar.

Personally, I can't see any logical reason for char being faster. The
problem is the size of the row, each row spans multiple database pages and
all have to be read to do a count. It's a lot of IO, and varchar should
actually be more compact in your case.

I would think that the speedup you see is a result of the table having a
fresh new organization. Anyway, I'm interested to hear what happens if you
do the test above.

I liked Scot's suggestion of using a small table containing only the IDs to
use for counting. A serial scan of this small table could be expected to be
much faster. Postgres doesnt use an index in the case you specified, as I
understand it.

Regards
Iain
----- Original Message -----
From: "C. Bensend" <benny@bennyvision.com>
To: <pgsql-admin@postgresql.org>
Sent: Wednesday, June 16, 2004 11:12 AM
Subject: Re: [ADMIN] VARCHAR -vs- CHAR: huge performance difference?


>
> > "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"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match