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 actually is
used for several database back-ends, and I don't want to change it to use
postgres-specific query syntax if I can help it. I'm trying to avoid this at
all costs.
What's really weird, and makes me suspect an optimizer or protocol bug, is that
if I place "protocolVersion=2" as a JDBC parameter, the problem goes away.
That is, I'm seeing the query take <1sec as opposed to >3min when using the
legacy protocol. I stumbled on this based on reading that the older protocol
sent everything as string, and inferred the type on the server side.
Now, that's a reasonable workaround, but it does seem like I've hit either a
Postgres server bug, optimizer or other, or a JDBC bug of some kind.
Any help in narrowing down the problem is appreciated!
