Re: Postgres 9.0.4 with Serverside PreparedStatements never returns with large tables

All,

I've experienced a problem with the following conditions:

1. PostgreSQL Server 9.0.4 (fine on 9.1.10)
2. JDBC (Any version)
3. Serverside prepared statements (prepareThreshold=1)
4. A very large table (30,000,000+ rows) with indexes on int columns
5. Java (any version)

The problem is that the serverside prepared statement doesn't return in good time when querying a large table.

<massive_assumption> It seems that the serverside prepared statement tries to do something fancy around the parameterised arguments type inference and casts the arguments as text or varchar which results in the query not returning on time because it's using a seqscan rather than an index scan. </massive_assumption>

If I set the protocolVersion = 2 then it works fine (assuming I pass ints to the setObject or setInt functions)

I've tried the following connection parameters (in different configurations) which didn't work:

1. prepareThreshold=1
2. stringType=unspecified
3. protocolVersion=3

The following connection parameters worked (either/or):

1. prepareThreshold=0
2. protocolVersion=2

Can anyone confirm or deny this bug/issue/feature?

I've attached a test class - adjust the query and comment out the properties as you see fit (Obviously the large data set is going to be a problem).

Kind regards,

Mike O'Connell

--

If you don't know where you are going, any road will get you there.