Quoting Dima Tkach <[EMAIL PROTECTED]>: > Nah... > This is a different story - for teh index to be useful, the *beginning* > of your search string must be known. > So "like '00423%" and "~ '^00423'" should both work, but "like '%423'" > and "~ '00423'" both won't - it's like searching a telephone book for > somebody, whose last name ends with "erry" (as opposed to begins with > "Perr"). > > Dima > > > Keith C. Perry wrote: > > >I wanted to know this too because I notice that using like with wildcards > >appears to be similar to a regular expression in that the index is not used. > > >This is what I have... > > > >ethernet=# select version(); > > version > >---------------------------------------------------------------------- > > PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 > >(1 row) > > > >ethernet=# \d vendors > > Table "public.vendors" > > Column | Type | Modifiers > >---------+-----------------------+----------- > > header | character(6) | > > company | character varying(80) | > >Indexes: > > "vender_id_idx" btree (header) > > > > > >ethernet=# explain select * from vendors where header like '000423'; > > QUERY PLAN > > >--------------------------------------------------------------------------------- > > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 > width=68) > > Index Cond: (header = '000423'::bpchar) > > Filter: (header ~~ '000423'::text) > >(3 rows) > > > > > >Ok, that made sense- > > > >ethernet=# explain select * from vendors where header like '%000423%'; > > QUERY PLAN > >---------------------------------------------------------- > > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > > Filter: (header ~~ '%000423%'::text) > >(2 rows) > > > >This didn't make sense until I did... > > > >ethernet=# explain select * from vendors where header like '0004%'; > > QUERY PLAN > > >--------------------------------------------------------------------------------- > > Index Scan using vender_id_idx on vendors (cost=0.00..113.26 rows=36 > width=68) > > Index Cond: ((header >= '0004'::bpchar) AND (header < '0005'::bpchar)) > > Filter: (header ~~ '0004%'::text) > >(3 rows) > > > >which again made sense because of the header's size but both- > > > >ethernet=# explain select * from vendors where header ~* '0004'; > > QUERY PLAN > >----------------------------------------------------------- > > Seq Scan on vendors (cost=0.00..151.15 rows=58 width=68) > > Filter: (header ~* '0004'::text) > >(2 rows) > > > >ethernet=# explain select * from vendors where header ~* '000423'; > > QUERY PLAN > >---------------------------------------------------------- > > Seq Scan on vendors (cost=0.00..151.15 rows=3 width=68) > > Filter: (header ~* '000423'::text) > >(2 rows) > > > >are sequentially scanned which means that regex's do not use indexes. Is > that > >right also? > > > > > > > >
Ahhh, so it is!! So let me ask you this. In order to build an index that would be able to handle something like "lastname like '%erry'", would you need that full text search patch in contrib (tsearch?) or could you do it with an index on a function? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com ---------------------------(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
