Received: from tomts12-srv.bellnexxia.net (tomts12.bellnexxia.net [209.226.175.56]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0BGSiB45409 for ; Fri, 11 Jan 2002 11:28:44 -0500 (EST) (envelope-from fbax@sympatico.ca) Received: from ACER ([65.93.1.15]) by tomts12-srv.bellnexxia.net (InterMail vM.4.01.03.16 201-229-121-116-20010115) with SMTP id <20020111162848.QCJT5888.tomts12-srv.bellnexxia.net@ACER>; Fri, 11 Jan 2002 11:28:48 -0500 Message-Id: <3.0.6.32.20020111112758.01d7feb0@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 11:27:58 -0500 To: From: Frank Bax Subject: Re: Resources Cc: "Guru\(External\)" , In-Reply-To: References: <3.0.6.32.20020111110937.02289430@pop6.sympatico.ca> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200201/88 X-Sequence-Number: 6177 Sorry, I made too general a statement. Index will help when the expession is anchored to beginning of field. So 'A%' gets help from index, but '%A%' does not. Frank At 11:13 AM 1/11/02 -0500, Nick Fankhauser wrote: > >> I don't beleive indexes will improve SELECT using LIKE. > >I wondered about that too, so I did a test using a database I'm working >with. The results indicate that it helps substantially- here is my terminal >log file: > >staging=# >staging=# >staging=# explain select count(*) from actor where actor_full_name like >'A%'; >NOTICE: QUERY PLAN: > >Aggregate (cost=1067.72..1067.72 rows=1 width=0) > -> Seq Scan on actor (cost=0.00..1066.29 rows=572 width=0) > >EXPLAIN >staging=# create index test on actor(actor_full_name); >CREATE >staging=# explain select count(*) from actor where actor_full_name like >'A%'; >NOTICE: QUERY PLAN: > >Aggregate (cost=577.46..577.46 rows=1 width=0) > -> Index Scan using test on actor (cost=0.00..576.03 rows=572 width=0) > >EXPLAIN >staging=# > > >-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/ > >