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