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

Yup,

Known issue. 9.1 fixed the problem.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Wed, Feb 12, 2014 at 3:10 AM, Mike O <mcanix(at)gmail(dot)com> wrote:

> 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.
>
>
>
>