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 [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Marc Mitchell > Sent: Friday, May 31, 2002 11:32 AM > To: [EMAIL PROTECTED] > 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. > [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 > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
