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 A157B3A508F for ; Sat, 8 Jan 2005 03:36:58 +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 40578-01 for ; Sat, 8 Jan 2005 03:36:45 +0000 (GMT) Received: from hosea.tallye.com (joel.tallye.com [216.99.199.78]) by svr1.postgresql.org (Postfix) with ESMTP id D77103A5037 for ; Sat, 8 Jan 2005 03:36:49 +0000 (GMT) Received: from hosea.tallye.com (hosea.tallye.com [127.0.0.1]) by hosea.tallye.com (8.12.8/8.12.10) with ESMTP id j083amYs003561 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Fri, 7 Jan 2005 19:36:48 -0800 Received: (from sttng359@localhost) by hosea.tallye.com (8.12.8/8.12.10/Submit) id j083alaH003559 for pgsql-performance@postgresql.org; Fri, 7 Jan 2005 19:36:47 -0800 X-Authentication-Warning: hosea.tallye.com: sttng359 set sender to lorenl@alzatex.com using -f Date: Fri, 7 Jan 2005 19:36:47 -0800 From: "Loren M. Lang" To: PostgreSQL Perfomance Subject: TEXT field and Postgresql Perfomance Message-ID: <20050108033647.GB2867@alzatex.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.1i X-GPG-Key: ftp://ftp.tallye.com/pub/lorenl_pubkey.asc X-GPG-Fingerprint: B3B9 D669 69C9 09EC 1BCD 835A FAF3 7A46 E4A3 280C X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/86 X-Sequence-Number: 9776 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? 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 the rest of the table which is probably all stored in a fixed size rows since all or nearly all of the other types have a specific size including CHAR. Therefore TEXT entries must be in some other hash table that only needs to be looked up when that column is referenced. If this is the case then all other row data will need to be read in for an unindexed query, but the TEXT columns will only be read if their being searched though or queried. And if they're only being queried, then only the rows that matched will need the TEXT columns read in which should have minimal impact on performance even if they contain kilobytes of information. -- I sense much NT in you. NT leads to Bluescreen. Bluescreen leads to downtime. Downtime leads to suffering. NT is the path to the darkside. Powerful Unix is. Public Key: ftp://ftp.tallye.com/pub/lorenl_pubkey.asc Fingerprint: B3B9 D669 69C9 09EC 1BCD 835A FAF3 7A46 E4A3 280C