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 91230D1B550 for ; Wed, 19 Nov 2003 16:35:53 +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 09672-06 for ; Wed, 19 Nov 2003 12:35:22 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id A155DD1D80F for ; Wed, 19 Nov 2003 12:35:21 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 9883A3547C; Wed, 19 Nov 2003 08:35:05 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 96FC935466; Wed, 19 Nov 2003 08:35:05 -0800 (PST) Date: Wed, 19 Nov 2003 08:35:05 -0800 (PST) From: Stephan Szabo To: Ian Barwick Cc: pgsql-performance@postgresql.org Subject: Re: TEXT column and indexing In-Reply-To: <200311191018.18492.barwick@gmx.net> Message-ID: <20031119083025.R85482@megazone.bigpanda.com> References: <200311191018.18492.barwick@gmx.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/161 X-Sequence-Number: 4721 On Wed, 19 Nov 2003, Ian Barwick wrote: > > I have this table: > > db=> \d object_property_value > Table "db.object_property_value" > Column | Type | Modifiers > -----------------------+------------------------+-------------------- > obj_property_value_id | integer | not null default nextval(... > obj_property_id | integer | not null > value | text | > Indexes: > "object_property_value_pkey" primary key, btree (obj_property_value_id) > "opv_obj_property_id_ix" btree (obj_property_id) > "opv_v_ix" btree (substr(value, 1, 128)) > Foreign-key constraints: > "object_property_fkey" FOREIGN KEY (obj_property_id) > REFERENCES object_property(obj_property_id) > ON UPDATE CASCADE ON DELETE CASCADE > I want to query this table to match a specific value along > the lines of: > > SELECT obj_property_id > FROM object_property_value opv > WHERE opv.value = 'foo' > > The question is therefore: can I get an index to work on the TEXT column? It > is currently indexed with: > "opv_v_ix" btree (substr(value, 1, 128)) > > which doesn't appear to have any effect. I am probably missing something > obvious though. I can live with maintaining an extra VARCHAR column but You probably need to be querying like: WHERE substr(value,1,128)='foo'; in order to use that index. While substr(txtcol, 1,128) happens to have the property that it would be probably be useful in a search against a short constant string, that's an internal property of that function.