X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C9BAD9FA5B5 for ; Mon, 5 Jun 2006 18:27:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84899-01 for ; Mon, 5 Jun 2006 18:27:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by postgresql.org (Postfix) with ESMTP id 866809FA30C for ; Mon, 5 Jun 2006 18:27:23 -0300 (ADT) X-EthosMedia-Virus-Scanned: no infections found Received: from [64.81.245.111] (account josh@agliodbs.com HELO [192.168.1.27]) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 9530287; Mon, 05 Jun 2006 14:30:32 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance@postgresql.org Subject: Re: How to force Postgres to use index on ILIKE Date: Mon, 5 Jun 2006 14:26:56 -0700 User-Agent: KMail/1.8 Cc: "Andrus" References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200606051426.56789.josh@agliodbs.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/46 X-Sequence-Number: 19403 Andrus, > SELECT toode, nimetus > FROM toode > WHERE toode ILIKE 'x10%' ESCAPE '!' > ORDER BY UPPER(toode ),nimetus LIMIT 100 > > runs 1 minute in first time for small table size. > > Toode field type is CHAR(20) 1) why are you using CHAR and not VARCHAR or TEXT? CHAR will give you problems using an index, period. 2) You can't use an index on ILIKE. You can, however, use an index on lower(field) if your query is properly phrased and if you've created an expression index on lower(field). -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco