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:
Show quoted text
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?
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