|
First, sorry I didn't mention that I am using PostgreSQL 9, but the problem existed also on 8.4.
As for the BETWEEN clause, I'm using hibernate and don't want to put a database-specific SQL query in place.
I also decided to drop a few indexes, which were probably causing the optimizer to choose the wrong plan. This "fixes" the problem also, but I think in general the "bug" here is that the query plan is bound too early in prepared statement execution.
>>> RadosÅaw Smogura<rsmogura@xxxxxxxxxxxxxxx> 1/4/2011 9:48 AM >>> 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! |
