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

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

> Tom Lane wrote:
>
> >Dima Tkach <dmitry(at)openratings(dot)com> writes:
> >
> >
> >>Does anyone have any idea what could be wrong here?
> >>
> >>
> >
> >You didn't initdb in C locale ...
> >
> > regards, tom lane
> >
> >
> Ouch!
> Is there any way to fix that without recreating the database?
> Also, are you sure about this? Because the text comparison operators do
> seem to work fine...
>
> name like 'blah%' does not work, but name >= 'blah' and name < 'blai'
> *does*... aren't these locale-dependent too?
>
> Thanks a lot for your help!
>
> Dima
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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?

--
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