Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7A5D19FB1CB for ; Wed, 18 Oct 2006 07:20:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 48544-08 for ; Wed, 18 Oct 2006 07:20:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from linda-3.paradise.net.nz (linda-3.paradise.net.nz [203.96.152.182]) by postgresql.org (Postfix) with ESMTP id C47BE9FA4F4 for ; Wed, 18 Oct 2006 07:20:02 -0300 (ADT) Received: from smtp-3.paradise.net.nz (tclsnelb1-src-1.paradise.net.nz [203.96.152.172]) by linda-3.paradise.net.nz (Paradise.net.nz) with ESMTP id <0J7B00MN1U1CML@linda-3.paradise.net.nz> for pgsql-performance@postgresql.org; Wed, 18 Oct 2006 23:20:00 +1300 (NZDT) Received: from [192.168.1.11] (218-101-28-242.dsl.clear.net.nz [218.101.28.242]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id 92273146F7F7; Wed, 18 Oct 2006 23:19:59 +1300 (NZDT) Date: Wed, 18 Oct 2006 23:19:44 +1300 From: Mark Kirkwood Subject: Re: Optimization of this SQL sentence In-reply-to: <200610171729.19596.mweilguni@sime.com> To: Mario Weilguni Cc: pgsql-performance@postgresql.org, Alexander Staubo , Ruben Rubio Message-id: <4535FFC0.603@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 8BIT User-Agent: Thunderbird 1.5.0.7 (X11/20061003) References: <45349CDF.3090002@rentalia.com> <4534A35E.9030909@rentalia.com> <181E6D21-8326-4ED6-A9AE-6F9917C1C319@purefiction.net> <200610171729.19596.mweilguni@sime.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.032 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/344 X-Sequence-Number: 21473 Mario Weilguni wrote: >> >> Â Â contacto varchar(255), >> Â Â fuente varchar(512), >> Â Â prefijopais varchar(10) >> >> Instead, use: >> >> Â Â contacto text, >> Â Â fuente text, >> Â Â prefijopais text >> >> See the PostgreSQL manual for an explanation of varchar vs. text. > > Enforcing length constraints with varchar(xyz) is good database design, not a > bad one. Using text everywhere might be tempting because it works, but it's > not a good idea. > I've always used the rationale: If you *know* that the data is length constrained, then it is ok to reflect this in the domain you use - err, thats why they have length limits! e.g. if you know that 'prefijopais' can *never* be > 10 chars in length, then varchar(10) is a good choice. If the data length is unknown or known to be unlimited, then reflect that in the domain you use - e.g if 'fuente' or 'contacto' have no reason to be constrained, then just use text. best wishes Mark