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!