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. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html