Thread: LIke and Indicies
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
Am Freitag, 25. Februar 2005 17:31 schrieb Dave Smith: > 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) What part of "Index Scan" are you misunderstanding? -- Peter Eisentraut http://developer.postgresql.org/~petere/
From my reading of that statement it says it is going to use the index to match company_id=1000 and then fetch the row and compare product_desc. Where as in the the case without the like it is using product description in the Index Condition. The speed of the queries certainly seems to bare it out .. The query where the product_desc is in the index condition, returns almost immediately where as the other takes about 10 seconds before the first row is returned. On Fri, 2005-02-25 at 11:44, Peter Eisentraut wrote: > Am Freitag, 25. Februar 2005 17:31 schrieb Dave Smith: > > 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) > > What part of "Index Scan" are you misunderstanding? -- Dave Smith CANdata Systems Ltd 416-493-9020
Am Freitag, 25. Februar 2005 17:54 schrieb Dave Smith: > >From my reading of that statement it says it is going to use the index > > to match company_id=1000 and then fetch the row and compare > product_desc. Where as in the the case without the like it is using > product description in the Index Condition. Well, the truth is that it has *the option* to use the index, but it doesn't have to. This really mostly independent of the LIKE index issue. If you think that the plan choice is not optimal, try EXPLAIN ANALYZE and post the results if you need help interpreting them. -- Peter Eisentraut http://developer.postgresql.org/~petere/
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
Dave Smith <dave.smith@candata.com> writes:
> 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.
You *sure* you're using C locale? Try "show lc_collate".
If you're not, and don't want to re-initdb, you can make an index using
the text_pattern_ops operator class to support LIKE. See the manual or
recent discussions in pgsql-performance.
regards, tom lane