Re: 'like' refuses to use an index??? - Mailing list pgsql-general

Quoting Dima Tkach <dmitry@openratings.com>:

> Tom Lane wrote:
>
> >Dima Tkach <dmitry@openratings.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@postgresql.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