Re: 'like' refuses to use an index???

Quoting Dima Tkach <dmitry(at)openratings(dot)com>:

> 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