Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D60019FA0EF for ; Sun, 10 Dec 2006 21:47:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 62737-09 for ; Sun, 10 Dec 2006 21:46:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0102.google.com (nz-out-0506.google.com [64.233.162.234]) by postgresql.org (Postfix) with ESMTP id 7A76F9FA28E for ; Sun, 10 Dec 2006 21:47:03 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so692590nzh for ; Sun, 10 Dec 2006 17:47:01 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=AqtBPjAonn+JQBfKryHxLcuPttXHATgNMZlvoYBp5DlOaLBXoWcCTgzY4JWyUiI+Hqd87IUO1/Yu0XNYBlqzKEGRSP2G1aYQQuKAhbnLMGee1mQ2WjIZCkIKVslvqO7TDl2pN6/v7aezoqGsVtaaM9zji3BU8U9k9ddr+j/Cv4M= Received: by 10.64.250.3 with SMTP id x3mr9905801qbh.1165801621376; Sun, 10 Dec 2006 17:47:01 -0800 (PST) Received: by 10.65.215.20 with HTTP; Sun, 10 Dec 2006 17:47:01 -0800 (PST) Message-ID: <8a0c7af10612101747j33b09ddft21d3341cc95dfaf5@mail.gmail.com> Date: Sun, 10 Dec 2006 23:47:01 -0200 From: "Daniel van Ham Colchete" To: pgsql-performance@postgresql.org Subject: Re: New to PostgreSQL, performance considerations In-Reply-To: <430d92a20612101718o239dbf9dg990e784c17bf175a@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com> <430d92a20612101336r3de8862coe618277fccb4006a@mail.gmail.com> <8a0c7af10612101702i49b5a533v4a2300d90d9dae6b@mail.gmail.com> <430d92a20612101718o239dbf9dg990e784c17bf175a@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/195 X-Sequence-Number: 22251 Gene, at Postgres's docs they say that the "constraint checks are relatively expensive". From what you're saying, it's really worth studying the matter deply first. I never understood what's the matter between the ASCII/ISO-8859-1/UTF8 charsets to a database. They're all simple C strings that doesn't have the zero-byte in the midlle (like UTF16 would) and that doesn't require any different processing unless you are doing case insensitive search (them you would have a problem). ASCII chars are also correct UTF8 chars as well. The first 127 Unicode chars are the same as the ASCII chars. So you would not have any problems changing your table from ASCII to UTF8. My software uses UTF16 and UTF8 at some of it's internals and I only notice performance problems with UTF16 (because of the zero-byte thing, the processing I make is diferent). So, I imagine that you wouldn't have any performance issues changing from ASCII to UTF8 if necessary. Nowadays everything is turning to Unicode (thank god). I wouldn't start anything with any other charset. I would only be asking for a rewrite in a near future. Best, Daniel On 12/10/06, Gene wrote: > I'm using gentoo as well, I'm having performance issues as the number of > partitions is increasing I imagine do due to overhead managing them and > figuring out where to put each insert/update. I'm switching to weekly > partitions instead of daily. I believe in PG8.2 constraint exclusion works > with updates/deletes also so I'm eager to upgrade. I get about 1 million > records per day in two tables each, each record updated about 4 times within > 30 minutes. > > Do you think using UTF8 vs US-ASCII hurts performance signficantly, some of > my smaller tables require unicode, and I don't think you can have some > tables be unicode and some be ASCII.