X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 89641D1B58E for ; Mon, 29 Sep 2003 17:19:15 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 30787-06 for ; Mon, 29 Sep 2003 14:18:47 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id B7A7FD1CA6B for ; Mon, 29 Sep 2003 14:14:44 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3694864; Mon, 29 Sep 2003 10:10:46 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Alex Kretschmer Subject: Re: postgres index on ILIKE Date: Mon, 29 Sep 2003 10:09:31 -0700 User-Agent: KMail/1.4.3 References: <3F7824A8.2010704@gmx.de> In-Reply-To: <3F7824A8.2010704@gmx.de> Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200309291009.31470.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/400 X-Sequence-Number: 15378 Alex, > mydb=# explain select filename from neighbors where filename ilike > '%test%'; QUERY PLAN > ------------------------------------------------------------- > Seq Scan on neighbors (cost=0.00..17449.64 rows=774 width=37) > Filter: ((filename)::text ~~* '%test%'::text) I just looked at your query. What you have above is an "unanchored text search", where you are looking for a snippet of text anywhere in the field. Such a search *cannot* be indexed using a regular index. Instead, you need to employ the TSearch full text searching module for PostgreSQL. hmmm ... the Tsearch home page appears to be down. Check out the readme in your postgreSQL source code: PG_SOURCE/contrib/tsearch/readme -- Josh Berkus Aglio Database Solutions San Francisco