Re: [PERFORM] TEXT column and indexing
On Wednesday 19 November 2003 17:26, you wrote: > On Wed, 19 Nov 2003 10:18:18 +0100, Ian Barwick <[EMAIL PROTECTED]> > > 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] TEXT column and indexing
On Wednesday 19 November 2003 17:35, Stephan Szabo wrote: > On Wed, 19 Nov 2003, Ian Barwick wrote: > > I have this table: (...) > > 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. That's the one :-). Thanks! Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] TEXT column and indexing
On Wed, 19 Nov 2003 10:18:18 +0100, Ian Barwick <[EMAIL PROTECTED]> 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. Servus Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] TEXT column and indexing
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
