using index with bigint pkey
От | Éric Paré |
---|---|
Тема | using index with bigint pkey |
Дата | |
Msg-id | 1101425976.3726.56.camel@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: using index with bigint pkey
|
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: