Re: Date Parameter To Query Confusing Optimizer

 Can You try "...BETWEEN ?::date and ?::date ..." syntax or send
 statement causing problems?

 Kind regards,
 Radosław Smogura

 On Tue, 04 Jan 2011 07:27:42 -0700, "Kurt Westerfeld"
 <kwesterfeld@novell.com> wrote:
> "By the very definition of a prepared statement the query plan gets
> stored before the parameter values are known"
>
> Is this true for all databases? It would seem to me that this
> approach would always lead to the wrong query plan, especially in the
> case I am testing where the selectivity is very low for the BETWEEN
> clause I am using. The trouble is, the BETWEEN clause can also yield
> a
> highly selective result--which is basically the case as the database
> "ages". See, the date/timestamp column in this case is a good choice
> as it is a database activity table, a temporal database. The trouble
> is at times there is a huge amount of activity, yielding very low
> selectivity.
>
> Maybe it would be best for me to just disable the indices on the
> timestamp fields and just use/expect my join would yield the best
> results.
>
> But, back on topic, to me it seems wrong that choice of prepared vs.
> non-prepared, and protocol 2 vs. 3, would influence the optimizer so
> profoundly. I would think it's got to be something I can tune, that
> prepared statement parameters be considered for execution plan. Is
> there such a setting?
>
>>>> Alban Hertroys  1/4/2011 2:21 AM >>>
> 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 very badly (ie.
> doing
> full table scans). In an effort to try to narrow down the problem, I
> am taking the query and running it in interactive SQL mode, but
> changing the date parameters (which are BETWEEN ? and ? clauses) and
> placing a date literal instead, using the "date '2011-01-01' syntax.
> When I do this, the query runs instantly, obviously using indices on
> the tables involved.
>>
>> Now, I suspect the optimizer is taking the wrong path based on the
> usage of query parameters. I'm pretty surprised by this, because I
> would think the optimizer would do the same thing for any query
> parameter, however it arrived. Unfortunately for 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 statement
> the query plan gets stored before the parameter values are known,
> which forces the database to use a query plan that 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 your table
> 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 particular
> scenario.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
> !DSPAM:1213,4d22ca9211544532215324!