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@xxxxxxxxxx> 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!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
