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