X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 21186D1B380 for ; Tue, 15 Jun 2004 23:12:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66778-09 for ; Wed, 16 Jun 2004 02:12:52 +0000 (GMT) Received: from fusion.bennyvision.com (fusion.bennyvision.com [216.161.119.28]) by svr1.postgresql.org (Postfix) with SMTP id 5BDD3D1B20E for ; Tue, 15 Jun 2004 23:12:47 -0300 (ADT) Received: (qmail 6056 invoked from network); 16 Jun 2004 02:12:51 -0000 Received: from localhost (HELO webmail.stinkweasel.net) (127.0.0.1) by fusion.bennyvision.com with SMTP; 16 Jun 2004 02:12:51 -0000 Received: from 63.227.74.41 (SquirrelMail authenticated user benny) by webmail.stinkweasel.net with HTTP; Tue, 15 Jun 2004 21:12:29 -0500 (CDT) Message-ID: <50124.63.227.74.41.1087351971.squirrel@webmail.stinkweasel.net> 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> Date: Tue, 15 Jun 2004 21:12:29 -0500 (CDT) Subject: Re: VARCHAR -vs- CHAR: huge performance difference? From: "C. Bensend" To: pgsql-admin@postgresql.org User-Agent: SquirrelMail/1.4.2 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200406/192 X-Sequence-Number: 13852 > "C. Bensend" 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"