Re: LIke and Indicies

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