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