Skip site navigation (1) Skip section navigation (2)

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

From: "Keith C(dot) Perry" <netadmin(at)vcsn(dot)com>
To: Dima Tkach <dmitry(at)openratings(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: 'like' refuses to use an index???
Date: 2003-12-30 21:21:26
Message-ID: 1072819286.3ff1ec5634757@webmail.vcsn.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Dima TkachDate: 2003-12-30 21:36:40
Subject: Re: 'like' refuses to use an index???
Previous:From: ezra epsteinDate: 2003-12-30 20:22:32
Subject: Re: SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group