X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E595DD1CCDE for ; Wed, 19 Nov 2003 19:17:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38718-08 for ; Wed, 19 Nov 2003 15:16:52 -0400 (AST) Received: from p15140235.pureserver.info (sql-info.de [217.160.166.164]) by svr1.postgresql.org (Postfix) with ESMTP id 16D3DD1B53A for ; Wed, 19 Nov 2003 15:16:51 -0400 (AST) Received: by p15140235.pureserver.info (Postfix, from userid 639) id DA3BF203A; Wed, 19 Nov 2003 20:16:51 +0100 (CET) Received: from iwate.sql-info.de (pD9517DFE.dip.t-dialin.net [217.81.125.254]) (using SSLv3 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by p15140235.pureserver.info (Postfix) with ESMTP id AD4EEEA1; Wed, 19 Nov 2003 20:16:50 +0100 (CET) From: Ian Barwick To: Manfred Koizar Subject: Re: TEXT column and indexing Date: Wed, 19 Nov 2003 20:16:48 +0100 User-Agent: KMail/1.5.1 References: <200311191018.18492.barwick@gmx.net> In-Reply-To: Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200311192016.48447.barwick@gmx.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/166 X-Sequence-Number: 4726 On Wednesday 19 November 2003 17:26, you wrote: > On Wed, 19 Nov 2003 10:18:18 +0100, Ian Barwick > > wrote: > >Indexes: > > [...] > > "opv_v_ix" btree (substr(value, 1, 128)) > > > >SELECT obj_property_id > > FROM object_property_value opv > > WHERE opv.value = 'foo' > > Try > ... WHERE substr(opv.value, 1, 128) = 'foo' > > HTH. Yup: db=> explain db-> SELECT obj_property_id db-> FROM object_property_value opv db-> WHERE substr(opv.value,1,128) = 'foo'; QUERY PLAN ------------------------------------------------------------------------------------------------ Index Scan using opv_v_ix on object_property_value opv (cost=0.00..4185.78 rows=1101 width=4) Index Cond: (substr(value, 1, 128) = 'foo'::text) (2 rows) Many thanks Ian Barwick barwick@gmx.net