X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CD9523A50B7 for ; Sat, 8 Jan 2005 05:03:07 +0000 (GMT) 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 46130-09 for ; Sat, 8 Jan 2005 05:02:56 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 49DF33A508E for ; Sat, 8 Jan 2005 05:02:55 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 71so38071wra for ; Fri, 07 Jan 2005 21:02:55 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=Onyl45yHSk69BRCyHxaEqZOCqtQ5BYczrwcOdmjopx2pSNf0/Q7nm2QMgxF2bKO63snJydHScBLwAoOw3STwi2WXA66jlppbnGmIgSwNO3QeUvwXoDXtYgcoKa2k0ZQx6HtT0i4XWnnWO0IPQqLJoMaOeCqdmjU0C+sB40dvUjY= Received: by 10.54.18.23 with SMTP id 23mr81946wrr; Fri, 07 Jan 2005 21:02:54 -0800 (PST) Received: by 10.54.18.72 with HTTP; Fri, 7 Jan 2005 21:02:54 -0800 (PST) Message-ID: <33c6269f0501072102144e82dc@mail.gmail.com> Date: Sat, 8 Jan 2005 00:02:54 -0500 From: Alex Turner Reply-To: alex@neteconomist.com To: "Loren M. Lang" , PostgreSQL Perfomance Subject: Re: TEXT field and Postgresql Perfomance In-Reply-To: <20050108040323.GA13889@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <20050108033647.GB2867@alzatex.com> <20050108040323.GA13889@wolff.to> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/88 X-Sequence-Number: 9778 I guess my question that would follow is, when does it work best to start using BLOBs/CLOBs (I forget if pg has CLOBs) instead of textfields because your table is going to balloon in disk blocks if you have large amounts of data, and all fields you want to search on would have to be indexed, increasing insert time substantialy. Does it ever pay to use text and not CLOB unless your text is going to be short, in which case why not just varchar, leading to the thought that the text datatype is just bad? Alex Turner NetEconomist On Fri, 7 Jan 2005 22:03:23 -0600, Bruno Wolff III wrote: > On Fri, Jan 07, 2005 at 19:36:47 -0800, > "Loren M. Lang" wrote: > > Do large TEXT or VARCHAR entries in postgresql cause any performance > > degradation when a query is being executed to search for data in a table > > where the TEXT/VARCHAR fields aren't being searched themselves? > > Yes in that the data is more spread out because of the wider rows and that > results in more disk blocks being looked at to get the desired data. > > > Since, according to the postgresql docs, theirs no performance > > difference between VARCHAR and TEXT, I'm assuming VARCHAR is identical > > to TEXT entries with a restriction set on the length. And since TEXT > > can be of any possible size, then they must be stored independently of > > No. > > > the rest of the table which is probably all stored in a fixed size rows > > No, Postgres uses variable length records. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >