Thread: "between" is using index but "like" is not

I have a table with a text column and I'd like to find entries matching
"pattern*" in that column. When using a between i get:

sd=> explain analyze select id,name, shortname from tr where shortname between  'Run_' and 'RunZ';
                                                               QUERY PLAN
                 

-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tr_shortname_idx on traces  (cost=0.00..37.86 rows=10 width=42) (actual time=0.025..0.025 rows=0
loops=1)
   Index Cond: ((shortname >= 'Run_'::text) AND (shortname <= 'RunZ'::text))
 Total runtime: 0.052 ms
(3 rows)

But when using like:
sd=> explain analyze select id,name, shortname from tr where shortname like 'Run%';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Seq Scan on tr  (cost=0.00..42379.94 rows=10 width=42) (actual time=988.416..988.416 rows=0 loops=1)
   Filter: (shortname ~~ 'Run%'::text)
 Total runtime: 988.473 ms
(3 rows

Why doesn't it use an index on the "like" operator when it doesn't contain a wildcard in the beginning of the pattern?

Thanks.

--
./Jesper Krogh, jesper@krogh.cc
Jabber ID: jesper@jabbernet.dk

Jesper,

> Why doesn't it use an index on the "like" operator when it doesn't contain
> a wildcard in the beginning of the pattern?

How many rows are there in the table?   What happens if you force an index
scan on the 2nd query?  Is this ASCII text or UNICODE text?

Normally LIKE 'xxx%' does use an index if it's useful, so there is something
else going on here.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Jesper Krogh <jesper@krogh.cc> writes:
> Why doesn't it use an index on the "like" operator when it doesn't
> contain a wildcard in the beginning of the pattern?

Probably because your locale isn't C --- locale-specific sort ordering
usually isn't compatible with the needs of LIKE, so we can only make
that optimization in C locale.

You can either re-initdb in C locale, or (if you're using 7.4) create a
specialized index with non-locale-dependent comparison operators.  See
the manual concerning specialized index operator classes.

            regards, tom lane

Josh Berkus wrote:

> Jesper,
>
>
>>Why doesn't it use an index on the "like" operator when it doesn't contain
>>a wildcard in the beginning of the pattern?
>
>
> How many rows are there in the table?   What happens if you force an index
> scan on the 2nd query?  Is this ASCII text or UNICODE text?
>
> Normally LIKE 'xxx%' does use an index if it's useful, so there is something
> else going on here.

Shall not him define an index with the right operator class in order to be used
with the like operator ?



Regards
Gaetano Mendola

I gmane.comp.db.postgresql.novice, skrev Tom Lane:
>  Jesper Krogh <jesper@krogh.cc> writes:
> > Why doesn't it use an index on the "like" operator when it doesn't
> > contain a wildcard in the beginning of the pattern?
>
>  Probably because your locale isn't C --- locale-specific sort ordering
>  usually isn't compatible with the needs of LIKE, so we can only make
>  that optimization in C locale.
>
>  You can either re-initdb in C locale, or (if you're using 7.4) create a
>  specialized index with non-locale-dependent comparison operators.  See
>  the manual concerning specialized index operator classes.

Locale was set to:
lc_messages = 'en_US.iso885915'         # locale for system error
message string
s
lc_monetary = 'en_US.iso885915'         # locale for monetary formatting
lc_numeric = 'en_US.iso885915'          # locale for number formatting
lc_time = 'en_US.iso885915'                     # locale for time
formatting

I'll try re-initdb to locale C in the weekend.. this requires dump and
restore right?


Jesper


--
./Jesper Krogh, jesper@krogh.cc
Jabber ID: jesper@jabbernet.dk