LIke and Indicies

From: Dave Smith <dave(dot)smith(at)candata(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: LIke and Indicies
Date: 2005-02-25 16:31:57
Message-ID: 1109349117.14398.50.camel@playpen.candata.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am using 7.4.5 and trying to use the like clause (Local C) but
postgres does not seem to want to use the index.

explain
declare t scroll cursor for
select * from product where company_id=1000 and product_desc like 'J%'
order by company_id,product_desc;

QUERY
PLAN
---------------------------------------------------------------------------------
Index Scan using product_4 on product (cost=0.00..12306.67 rows=881
width=181)
Index Cond: (company_id = 1000)
Filter: ((product_desc)::text ~~ 'J%'::text)

explain
declare t scroll cursor for
select * from product where company_id=1000 and product_desc >= 'J' and
product_desc < 'K'
order by company_id,product_desc;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using product_4 on product (cost=0.00..1914.43 rows=881
width=181)
Index Cond: ((company_id = 1000) AND ((product_desc)::text >=
'J'::text) AND ((product_desc)::text < 'K'::text))

I thought that if you used like and the wildcard was at the end it would
use the index?

--
Dave Smith
CANdata Systems Ltd
416-493-9020

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2005-02-25 16:44:11 Re: LIke and Indicies
Previous Message Ben Trewern 2005-02-25 16:19:37 to_char bug?