Re: Date Parameter To Query Confusing Optimizer

On Mon, Jan 3, 2011 at 2:48 PM, 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.

Try using PREPARE to simulate your issue. We ran into a similar issue
using PHP and prepared statements, where the plan choices were
occasionally abysmal depending what the filters were and their
relative distributions within the table.

http://www.postgresql.org/docs/current/interactive/sql-prepare.html