Re: LIKE operator and indexes

Marc-

I've just gone through some similar query optimizing work, and I can confirm
that LIKE can definitely use an index if the initial characters are supplied
as in the example you sent.

-Nick

--------------------------------------------------------------------------
Nick Fankhauser nickf(at)ontko(dot)com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Marc Mitchell
> Sent: Friday, May 31, 2002 11:32 AM
> To: pgsql-admin(at)postgresql(dot)org
> Subject: [ADMIN] LIKE operator and indexes
>
>
> Can anyone provide definitive information and/or points of
> reference within
> the documentation about the ability of the query optimizer to use indexes
> when processing a "LIKE" operator on a String column?
>
> My assumption is that
>
> "SELECT * FROM customer WHERE name LIKE 'GENERAL%';"
>
> should benefit from the existence of a BTREE index on the "customer.name"
> column. I know this is the case is most other RDBMS's I've used including
> Postgres's ancestor Ingres.
>
> However, experiments with EXPLAIN seem to always result in Seq Scans.
> Furthermore, per the documentation:
>
> "7.2. Index Types... In particular, the PostgreSQL query optimizer will
> consider using a B-tree index whenever an indexed column is involved in a
> comparison using one of these operators: <, <=, =, >=, > ",
>
> the LIKE operator is conspicuously missing. However, I've yet to find a
> reference specifically talking about LIKE and query plans. There is no
> reference in "4.6.1. Pattern Matching with LIKE" .
>
> I'd like to know if indexes can be used and I've go something else setup
> wrong or if indexes and LIKEs don't mix.
>
> Marc Mitchell - Senior Application Architect
> Enterprise Information Solutions, Inc.
> marcm(at)eisolution(dot)com
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>