Re: JDBC patch, so that bigint indexes get used

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(at)mediaworks(dot)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
----------------------------------------------------------------------------