Looks to me like it is using an index scan in both example queries.
I'm not an expert plan reader, but are you wondering why the index condition in the second query includes everything from your WHERE clause? Are you using a multi-column index that is not applicable in the first query?
It's possible that the planner thinks using the index on company_id filtered by product_desc is faster than any multicolumn index that might exist.
-tfo
-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
On Feb 25, 2005, at 10:31 AM, Dave Smith wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
