From: Kurt De Grave <Kurt.DeGrave@student.kuleuven.ac.be>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: parameterized LIKE does not use index
Date: Thu, 23 Jun 2005 10:33:18 +0200
Message-ID: <42BA73CE.4030909@student.kuleuven.ac.be> (raw)
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kurt.DeGrave@student.kuleuven.ac.be, josh@agliodbs.com
Subject: Re: parameterized LIKE does not use index
In-Reply-To: <42BA73CE.4030909@student.kuleuven.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox