Thread: parameterized LIKE does not use index

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.

Kurt,

> 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?

There should be an option to tell DBD::Pg not to cache a query plan.
Let's see ....

yes.  pg_server_prepare=0, passed to the prepare() call.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

> > 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?

> There should be an option to tell DBD::Pg not to cache a query plan.
> Let's see ....
>
> yes.  pg_server_prepare=0, passed to the prepare() call.

That does the trick!  Now I can have the cake and eat it! (clean code
and high perf)

Now it's tempting to dream of some mechanism that could make the
database consider
replanning the query automatically once it knows the parameter, or
choose from
a set of plans depending on the parameter. In this case the general plan
was about three orders
of magnitude slower than the specialized plan. But I guess this case is
not all that common
and the developer can work around it.

thanks,
kurt.

--
ir. Kurt De Grave                           http://www.PharmaDM.com
PharmaDM nv.                                   phone: +32-16-298494
Kapeldreef 60, B-3001 Leuven, Belgium            fax: +32-16-298490

On Thu, Jun 23, 2005 at 10:33:18 +0200,
  Kurt De Grave <Kurt.DeGrave@student.kuleuven.ac.be> wrote:
>
> Now it's tempting to dream of some mechanism that could make the
> database consider
> replanning the query automatically once it knows the parameter, or
> choose from
> a set of plans depending on the parameter. In this case the general plan
> was about three orders
> of magnitude slower than the specialized plan. But I guess this case is
> not all that common
> and the developer can work around it.

I remember some discussion about delaying planning until the first
actual query so that planning could use actual parameters to do
the planning. If you really want to have it check the parameters
every time, I think you will need to replan every time. I don't
know if there is a way to save some of the prepare working while
doing this.

Bruno,

> I remember some discussion about delaying planning until the first
> actual query so that planning could use actual parameters to do
> the planning. If you really want to have it check the parameters
> every time, I think you will need to replan every time. I don't
> know if there is a way to save some of the prepare working while
> doing this.

That wouldn't help much in Kurt's case.    Nor in most "real" cases, which is
why I think the idea never went anywhere.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, Jun 23, 2005 at 11:55:35AM -0700, Josh Berkus wrote:
> Bruno,
>
> > I remember some discussion about delaying planning until the first
> > actual query so that planning could use actual parameters to do
> > the planning. If you really want to have it check the parameters
> > every time, I think you will need to replan every time. I don't
> > know if there is a way to save some of the prepare working while
> > doing this.
>
> That wouldn't help much in Kurt's case.    Nor in most "real" cases, which is
> why I think the idea never went anywhere.

I suspect the only way to do this and have it work well would be to
cache plans based on the relevant statistics of the parameters passed
in. Basically, as part of parsing (which could always be cached, btw, so
long as schema changes clear the cache), you store what fields in what
tables/indexes each parameter corresponds to. When you go to execute you
look up the stats relevant to each parameter; you can then cache plans
according to the stats each parameter has. Of course caching all that is
a non-trivial amount of work, so you'd only want to do it for pretty
complex queries.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"