Skip site navigation (1) Skip section navigation (2)

parameterized LIKE does not use index

From: Kurt De Grave <Kurt(dot)DeGrave(at)student(dot)kuleuven(dot)ac(dot)be>
To: pgsql-performance(at)postgresql(dot)org
Subject: parameterized LIKE does not use index
Date: 2005-06-22 19:25:20
Message-ID: 42B9BB20.7050109@student.kuleuven.ac.be (view raw or flat)
Thread:
Lists: pgsql-performance
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.  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.


Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2005-06-22 21:08:55
Subject: Re: parameterized LIKE does not use index
Previous:From: Kurt De GraveDate: 2005-06-22 18:50:13
Subject: parameterized LIKE does not use index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group