Re: Is it possible to search for sub-strings...

On Tue, 19 Sep 2000, Steve Heaven wrote:

> At 09:00 19/09/00 -0700, Mitch Vincent wrote:
> >LIKE can use indexes, ~* can not, that's LIKE's main advantage (at least in
> >my opinion).
> >
>
> ~ does use indexes, ~* doesnt,
>  but then nor does the equivalent upper(columnname) LIKE 'TERM'. Make sure
> you're comparing apples with apples.

It can:

campsites=> explain select * from camps4 where upper(city) like 'MACKINA%';
NOTICE:  QUERY PLAN:

Index Scan using camps4_ucity on camps4  (cost=106.34 size=644 width=132)

In my particular case the like search needed to perform an upper() like
select, an index was created for this purpose (in this case: camps4_ucity)

Vince.

>
>
> explain select * from all_title_fti where string like 'A%';
> NOTICE:  QUERY PLAN:
>
> Index Scan using all_title_idx on all_title_fti  (cost=86633.57 rows=1
> width=16)
>
>  explain select * from all_title_fti where string ~ '^A';
> NOTICE:  QUERY PLAN:
>
> Index Scan using all_title_idx on all_title_fti  (cost=86633.57 rows=1
> width=16
>
>  explain select * from all_title_fti where upper(string) like 'A%';
> NOTICE:  QUERY PLAN:
>
> Seq Scan on all_title_fti  (cost=170921.58 rows=1083414 width=16)
>

--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================