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:
