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. [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
