Re: same query, two different approaches, vastly different performance

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