ok. I am completely lost here.
On Feb 13, 2012, at 8:10 PM, David Nicol wrote: > 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 > why should a patch be required in Postgres? Seems to extremely naive pov that this is an issue with DBI. > > 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. So, the index I have created is as follows CREATE INDEX idxa ON t (Lower(a) varchar_pattern_ops); This is a web app (although I have done tests using standalone, non-web, scripts as well). The user types a few characters that are received in $q. I then do $q = lc($q) . '%'; followed by my $sql = qq{SELECT a, b, c FROM t WHERE Lower(a) LIKE '$q'}; my $sth = $dbh->prepare($sql); $sth->execute(); The above takes, say, 200-400 ms depending on the term. Or, in the case of the query that is really slow my $sth = $dbh->prepare(qq{SELECT a, b, c FROM t WHERE Lower(a) LIKE ?}); $sth->execute($q); The above takes 3 to 4 seconds. Pretty much identical queries with identical results by vastly difference performance. Isn't this a DBI issue instead of a Postgres issue? -- Puneet Kishor
