[Hibernate-devel] Postgres - Hibernate How it deals with int2 / int8... ?

[Christian M. <vc...@cl...>]

Hi all,

i was wondering if hibernate knows how to deal with the issue regarding =
int2/int8 in Postgresql ?

Example:

create table message(id int8 not null primary key,name text);

in my example i got 25 000 records in this table, if i issue the =
following query:
explain analyse select * from message where num=3D10;

NOTICE:  QUERY PLAN:
Seq Scan on message  (cost=3D0.00..1493.72 rows=3D1 width=3D117) (actual =
time=3D521.00..521.00 rows=3D0 loops=3D1)
Total runtime: 521.00 msec

As you can see, Postgres did not use the index on id, there are 2 =
possibles workarounds, either cast the parameter as an int8 as follow:
explain analyse select * from message where num=3D10::int8;

Or simpler just quote the paramater, postgres will cast it =
automatically:
explain analyse select * from message where num=3D'10';

And now the results:

NOTICE:  QUERY PLAN:
Index Scan using message_pkey on message  (cost=3D0.00..5.01 rows=3D1 =
width=3D117) (actual time=3D0.00..0.00 rows=3D0 loops=3D1)
Total runtime: 10.00 msec

FIX: the best approach should be to quote any number paramater.

Chris