Thread: JDBC patch, so that bigint indexes get used
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:
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
>
>
>
>
--
----------------------------------------------------------------------------
MobiComp - Mobile Computing & Wireless Solutions
phone: +351 253 305 250 fax: +351 253 305 251
web: http://www.mobicomp.com
----------------------------------------------------------------------------
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
>>
>>
>>
>>
>
>