On Mon, Feb 13, 2012 at 7:16 PM, Puneet Kishor > > > ahhhh... that makes sense. I would like to confirm this, because, if true, > then it is a strike against statements prepared with bind values. >
the postgres reference about when LIKE statements get to use indices states that they are only available for /constants/ ending with %. It seems like this would be simple enough to fix, if you want to be a hero and get a patch accepted into Postgres. It's in the fourth paragraph at http://www.postgresql.org/docs/8.4/static/indexes-types.html Also there is interesting stuff about creating indices for functions, so you can index on lower(a) and then queries using lower(a) will get optimized. After reading that bit of the postgres docs I wonder if doing something like lower(a) like ('^' || ?) would allow the index to be used. Probably not, as the result would not be a constant, even though it would be anchored to the beginning.