Thread: LIke and Indicies

LIke and Indicies

From
Dave Smith
Date:
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


Re: LIke and Indicies

From
Peter Eisentraut
Date:
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/

Re: LIke and Indicies

From
Dave Smith
Date:
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


Re: LIke and Indicies

From
Peter Eisentraut
Date:
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/

Re: LIke and Indicies

From
Thomas F.O'Connell
Date:
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


Re: LIke and Indicies

From
Tom Lane
Date:
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