X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 705B2528CE for ; Wed, 22 Jun 2005 15:50:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86132-06 for ; Wed, 22 Jun 2005 18:50:15 +0000 (GMT) Received: from matlab.pharmadm.com (derma.pharmadm.com [193.190.169.32]) by svr1.postgresql.org (Postfix) with ESMTP id 0A3D65285C for ; Wed, 22 Jun 2005 15:50:12 -0300 (ADT) Received: from matlab.pharmadm.com (matlab.pharmadm.com [192.168.93.22]) by matlab.pharmadm.com (Postfix) with ESMTP id 1CCD1BCE5 for ; Wed, 22 Jun 2005 20:50:13 +0200 (CEST) Date: Wed, 22 Jun 2005 20:50:13 +0200 (CEST) From: Kurt De Grave To: pgsql-performance@postgresql.org Subject: parameterized LIKE does not use index Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/530 X-Sequence-Number: 13167 Hi, Consider the where-clauses: WHERE lower(col) LIKE 'abc'; WHERE lower(col) LIKE 'abc%'; these will both use a b-tree functional index in lower(col) if one exists. The clause WHERE lower(col) LIKE '%abc'; can't use the index as you would expect, because of the wildcard at the front (as mentioned in the manual). Thus, it has to do a seqscan, on what in my case is a very large table. But still that's not too bad, because I expect an overwhelming amount of the simple cases, and only very few that start with a percentage sign. Now, what's problematic is if i replace the literal with a parameter, like this: WHERE lower(col) LIKE ? It seems that the parameterized query gets compiled once, and because the parameter is not yet known, one cannot be sure it doesn't start with a percentage sign. Using the parameterized version causes ALL cases to use a seqscan. Of course, I could modify the application and send different SQL depending on which case we're in or just constructing a query with a literal each time, but is there a way to add a hint to the SQL that would cause the query to be re-planned if it's a case that could use the index? Or can I convince the (Perl) driver to do so? kurt.