Re: JDBC patch, so that bigint indexes get used - Mailing list pgsql-jdbc

This is now 'fixed' in current sources.

--Barry

João Paulo Caldas Ribeiro wrote:

> Hi!
>
> First, as you said correctly this NOT a JDBC driver problem but a
> PostgresSQL PROBLEM.
> Second, this is not new. I made a patch my self months ago. It was
> exactely the sam thing :) but i used to substitue the
> "set(parameterIndex, Long.toString(x));" by
> " setString(parameterIndex, Long.toString(x));". It's a matter fact
> this work perfectly but as Barry Lind ( Hi, Barry! :) ) said to me
> when a told about my patch, this not the correct way to do this is
> definetely an "Hack" not  the true solution for the problem because
> the problem have to be fixed where it belong:  in the PostgreSql code.
> I will forward you the mail Barry Lind sent to me and  I suggest you
> the same he suggested try to force Postgresql team to correct this
> problem defenitely.
>
> Regards.
> JP
>
> PS: Keep the good work. :)
>
>
>
>
> Teofilis Martisius wrote:
>
>> Hello,
>>
>> Are you aware of this old problem that postgresql doesn't use bigint
>> indexes
>> when comparing with integers? Well, adding quotes or explicit casts
>> fixes this.
>>
>> => create table aaa (aaa bigint);                          => create
>> index aaa_aaa on aaa(aaa);
>> => explain select * from aaa where aaa=2;
>> Seq Scan on aaa  (cost=0.00..22.50 rows=5 width=8)
>> => explain select * from aaa where aaa='2';
>> Index Scan using aaa_aaa on aaa  (cost=0.00..17.07 rows=5 width=8)
>> => explain select * from aaa where aaa=2::bigint;
>> Index Scan using aaa_aaa on aaa  (cost=0.00..17.07 rows=5 width=8)
>>
>> I just finished fixing JDBC driver (one that comes with postgres 7.2.2)
>> to add quotes to PreparedStatement.setLong(), and
>> PreparedStatement.setObject()
>> methods, when setting numeric values. It gave me a big performance
>> improvement.
>>
>> I appended the patch. Apply it to $postgresql/src/interfaces/jdbc tree.
>>
>> Teofilis Martisius
>> teo@mediaworks.lt
>>
>> diff -u -r
>> /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java
>> ./org/postgresql/jdbc1/PreparedStatement.java
>> ---
>> /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java
>> 2002-01-15 09:37:33.000000000 +0200
>> +++ ./org/postgresql/jdbc1/PreparedStatement.java    2002-08-29
>> 15:47:23.000000000 +0200
>> @@ -203,7 +203,7 @@
>>      */
>>     public void setLong(int parameterIndex, long x) throws SQLException
>>     {
>> -        set(parameterIndex, Long.toString(x));
>> +        set(parameterIndex, "'"+Long.toString(x)+"'");
>>     }
>>
>>     /*
>> diff -u -r
>> /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java
>> ./org/postgresql/jdbc2/PreparedStatement.java
>> ---
>> /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java
>> 2002-01-15 09:37:33.000000000 +0200
>> +++ ./org/postgresql/jdbc2/PreparedStatement.java    2002-08-29
>> 16:17:32.000000000 +0200
>> @@ -211,7 +211,7 @@
>>      */
>>     public void setLong(int parameterIndex, long x) throws SQLException
>>     {
>> -        set(parameterIndex, Long.toString(x));
>> +        set(parameterIndex, "'"+Long.toString(x)+"'");
>>     }
>>
>>     /*
>> @@ -674,7 +674,7 @@
>>                 if (x instanceof Boolean)
>>                     set(parameterIndex, ((Boolean)x).booleanValue() ?
>> "1" : "0");
>>                 else
>> -                    set(parameterIndex, x.toString());
>> +                    set(parameterIndex, "'"+x.toString()+"'");
>>                 break;
>>             case Types.CHAR:
>>             case Types.VARCHAR:
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>>
>>
>
>