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

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

Post by Jesper Kro » Thu, 26 Aug 2004 22:30:50



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.

--


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

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

Post by Josh Berk » Fri, 27 Aug 2004 02:38:37


Jesper,

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

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

Post by Tom La » Fri, 27 Aug 2004 03:59:59



> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

      message can get through to the mailing list cleanly

 
 
 

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

Post by Gaetano Mendol » Fri, 27 Aug 2004 10:51:01



> 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

 
 
 

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

Post by Jesper Kro » Fri, 27 Aug 2004 16:27:00


I gmane.comp.db.postgresql.novice, skrev Tom Lane:


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

--


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster