Problem (I think) : planner usage of an index that is based on a bigint
field through jdbc ?
1) Planner perspective, in psql for example
I found that running a simple query on a bigint index does not behave as
expected in the planner. This query does not use the index on 'a pkey
id column' if it is of bigint type :
create table test (
id bigint primary key,
field text not null
);
This will never use the index, whatever size of table, and is
independant of calling analyze.
explain select * from test where id = 123;
Solution: cast
explain select * from test where id = 123::bigint;
explain select * from test where id = '123';
This is the way the planner behaves, as far as shown by 'explain'. I
don't really like this example since I wouldn't expect the planner to
use an index on an empty table. It just serves the purpose.
2) Jdbc perspective
The setLong javadoc for PreparedStatement states that it prepares the
query sending the equivalent of the postgresql bigint parameter.
When I look at query activities in pg_stat_activity to feel the behavior
of database usage of my Java program, it shows that a query is ran
without casting ::bigint or 'string'. Since the table is growing big, I
see that the queries aren't using the index on my bigint key (slow
queries).
I'd guess this is a postgresql bug, not a jdbc bug, right ? Or is this
a feature ?
Patch solution with postgersql-jdbc :
I replaced setLong with setString(myLong.toString()) which casts to a
quoted string representation and uses the index. Works for me.
Note on setup :
- postgresql 7.4.1 server
- postgresql-7.4.1-jdbc3.jar
--
Éric Paré for/pour LexUM
Université de Montréal
Centre de recherche en droit public
C.P. 6128, succ. Centre-ville
Montréal (Qc) Canada H3C 3J7
+1 514-343-6111 #0873
paree@lexum.umontreal.ca
