On 3 Jan 2011, at 23:48, Kurt Westerfeld wrote:
> I have a JDBC-based application which passes date/time parameters using JDBC query parameters, which is performing
verybadly (ie. doing full table scans). In an effort to try to narrow down the problem, I am taking the query and
runningit in interactive SQL mode, but changing the date parameters (which are BETWEEN ? and ? clauses) and placing a
dateliteral instead, using the "date '2011-01-01' syntax. When I do this, the query runs instantly, obviously using
indiceson the tables involved.
>
> Now, I suspect the optimizer is taking the wrong path based on the usage of query parameters. I'm pretty surprised
bythis, because I would think the optimizer would do the same thing for any query parameter, however it arrived.
Unfortunatelyfor this situation, the code which forms the query
The problem here is that JDBC uses prepared statements for parameterised queries. By the very definition of a prepared
statementthe query plan gets stored before the parameter values are known, which forces the database to use a query
planthat would work for every possible value of those parameters.
Thus you end up with a generic query plan.
This isn't often a problem, but if a significant number of your possible parameter values exist in a high percentage of
yourtable rows, then chances are you'll end up with a plan with a sequential scan.
You didn't tell what version of Postgres you're using - I recall recent versions (since 8.3?) are smarter about this
particularscenario.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d22ca9a11548321074132!