Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0BD334248 for ; Thu, 22 Mar 2012 17:38:58 -0300 (ADT) Received: from sd-17937.dedibox.fr ([88.191.100.74]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SAome-0000uG-1e for pgsql-sql@postgresql.org; Thu, 22 Mar 2012 20:38:57 +0000 Received: from [192.168.10.3] (AMontsouris-552-1-20-58.w86-212.abo.wanadoo.fr [86.212.51.58]) by sd-17937.dedibox.fr (Postfix) with ESMTPA id DF1333B914; Thu, 22 Mar 2012 21:38:42 +0100 (CET) Message-ID: <1332448722.2339.10.camel@localhost.localdomain> Subject: Re: ignore case in where clause From: Guillaume Lelarge To: "Edward W. Rouse" Cc: pgsql-sql@postgresql.org Date: Thu, 22 Mar 2012 21:38:42 +0100 In-Reply-To: <06fe01cd086a$04681ce0$0d3856a0$@com> References: <06fe01cd086a$04681ce0$0d3856a0$@com> Content-Type: text/plain; charset="UTF-8" X-Mailer: Evolution 3.2.3 (3.2.3-2.fc16) Content-Transfer-Encoding: 7bit Mime-Version: 1.0 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/61 X-Sequence-Number: 36456 On Thu, 2012-03-22 at 16:26 -0400, Edward W. Rouse wrote: > I am currently using lower(column) = '' for matching case insensitive. I > know that there are ways to do this with regular expressions too. I recently > noticed that including even one lower causes severe performance issues (from > 290ms to over 80Kms). > Probably because it cannot use the index anymore. Try creating an index on lower(column), and see if it helps. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com