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" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 16, 2004 11:12 AM Subject: Re: [ADMIN] VARCHAR -vs- CHAR: huge performance difference? > > > "C. Bensend" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html