Обсуждение: 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@eisolution.com

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@ontko.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@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Marc Mitchell
> Sent: Friday, May 31, 2002 11:32 AM
> To: pgsql-admin@postgresql.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@eisolution.com
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

On Fri, 31 May 2002, Marc Mitchell wrote:

> 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.

If you are in "C" locale, and have an anchored like or regex, indexes
should be considered.  If you're in a different locale, the optimization
is turned off.