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. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend