Received: from tomts9-srv.bellnexxia.net (tomts9.bellnexxia.net [209.226.175.53]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0BLF3B36462 for ; Fri, 11 Jan 2002 16:15:03 -0500 (EST) (envelope-from fbax@sympatico.ca) Received: from ACER ([65.93.1.15]) by tomts9-srv.bellnexxia.net (InterMail vM.4.01.03.16 201-229-121-116-20010115) with SMTP id <20020111211504.ICSL22140.tomts9-srv.bellnexxia.net@ACER>; Fri, 11 Jan 2002 16:15:04 -0500 Message-Id: <3.0.6.32.20020111161313.007a2310@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.6 (32) Date: Fri, 11 Jan 2002 16:13:13 -0500 To: Wei Weng From: Frank Bax Subject: Re: Resources Cc: In-Reply-To: <20020111154258.706feccc.wweng@kencast.com> References: <3.0.6.32.20020111141809.021c4eb0@pop6.sympatico.ca> <3.0.6.32.20020111141809.021c4eb0@pop6.sympatico.ca> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200201/102 X-Sequence-Number: 6191 At 03:42 PM 1/11/02 -0500, Wei Weng wrote: >Can you index on a function? >How exactly does that help performance of a query? If a table "employee" contains a field "last" and you are not sure how the last name might make use of capital letters, then to get all names starting with 'MC', you might code a query like: select * from employee where last ILIKE 'MC%'; which does not use an index on "last" because we used ILIKE instead of LIKE. In this case, we would create an index using "lower" function, like: create index employee_last on employee ( lower(last) ); then write the query as: select * from employee where lower(last) LIKE 'mc%'; which does make use of the index (and a faster executing query). Frank