Re: 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"